LLI Wiki — My Digital Knowledge Base

Import and export an Azure SQL BACPAC with SqlPackage (authentication methods)

SqlPackage commands to export and import a BACPAC on Azure SQL or Managed Instance: interactive sign-in, access token, managed identity.

SqlPackage exports an Azure SQL database or Azure SQL Managed Instance to a .bacpac file and imports a .bacpac into a target server. This article lists the authentication methods that work from the command line, from the simplest to the most suited to unattended use.

Prerequisites

Import a BACPAC

Interactive sign-in (universal authentication)

sqlpackage /Action:Import `
  /SourceFile:"C:\backup\AppDb.bacpac" `
  /TargetServerName:"sqlserver01.database.windows.net" `
  /TargetDatabaseName:"AppDb_check" `
  /UniversalAuthentication:True `
  /DiagnosticsFile:"C:\backup\import-check.log" `
  /DiagnosticsLevel:Verbose

With a connection string, and optionally an explicit tenant:

sqlpackage /Action:Import /SourceFile:"C:\backup\AppDb.bacpac" /UniversalAuthentication:True /TenantId:"<TENANT-ID>" /TargetConnectionString:"Server=tcp:sqlserver01.database.windows.net,1433;Initial Catalog=AppDb_test;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30"

Without /TenantId, the command is identical, just leave the parameter out.

Access token from Az PowerShell

$tok = (Get-AzAccessToken -ResourceUrl 'https://database.windows.net/').Token
$token = if ($tok -is [securestring]) { ConvertFrom-SecureString $tok -AsPlainText } else { $tok }

& "$env:USERPROFILE\.dotnet\tools\sqlpackage.exe" `
    /Action:Import `
    /SourceFile:'C:\backup\AppDb.bacpac' `
    /AccessToken:$token `
    /TargetConnectionString:'Server=tcp:sqlserver01.database.windows.net,1433;Initial Catalog=AppDb_test;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30'

Depending on the Az module version, .Token is a plain string or a SecureString, hence the test. The same call works with the DacFx executable, using the short forms /a:, /sf: and /tcs:.

Access token from Azure CLI

az account get-access-token --resource https://database.windows.net/ --query accessToken -o tsv

Pass the result to /AccessToken:. For an export:

sqlpackage /Action:Export /SourceServerName:"sqlserver01.database.windows.net" /SourceDatabaseName:"AppDb" /AccessToken:"<TOKEN>" /TargetFile:"C:\temp\AppDb.bacpac"

Authentication keywords in the connection string

After az login, the default credential chain is used:

& 'C:\Program Files\Microsoft SQL Server\160\DAC\bin\SqlPackage.exe' `
    /Action:Import `
    /SourceFile:'C:\backup\AppDb.bacpac' `
    /TargetConnectionString:'Server=tcp:sqlserver01.database.windows.net,1433;Initial Catalog=AppDb_test;Authentication=Active Directory Default;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30'

Interactive sign-in with a given user:

Authentication=Active Directory Interactive;User Id=user@example.com;

User-assigned managed identity (best for unattended runs)

On an Azure VM or Azure Virtual Desktop host that has a user-assigned managed identity:

$uami = '00000000-0000-0000-0000-000000000000'   # client ID of the managed identity

& 'C:\Program Files\Microsoft SQL Server\160\DAC\bin\SqlPackage.exe' `
    /Action:Import `
    /SourceFile:'C:\backup\AppDb.bacpac' `
    /TargetConnectionString:"Server=tcp:sqlserver01.database.windows.net,1433;Initial Catalog=AppDb_test;Authentication=Active Directory Managed Identity;User ID=$uami;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30"

Alternatively, request the token yourself from the Instance Metadata Service and pass it with /at::

$AppID = "https://database.windows.net"
$UamiID = "00000000-0000-0000-0000-000000000000"
$metadataUri = "http://169.254.169.254/metadata/identity/oauth2/token?api-version=2018-02-01&resource=$AppID&object_id=$UamiID"
$request = Invoke-RestMethod -Uri $metadataUri -Headers @{Metadata = "true"}
$AccessToken = $request.access_token

& "C:\Program Files\Microsoft SQL Server\160\DAC\bin\sqlpackage.exe" /at:"$AccessToken" /Action:Import /sf:"C:\backup\AppDb.bacpac" /TargetConnectionString:"Server=sqlserver01.database.windows.net;Initial Catalog=AppDb_test;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"

Never print or log the token.

Export a BACPAC

sqlpackage /Action:Export `
  /SourceConnectionString:"Server=tcp:sqlserver01.database.windows.net,1433;Initial Catalog=AppDb;Authentication=Active Directory Interactive;Encrypt=True;" `
  /TargetFile:"C:\temp\AppDb.bacpac"

With a token obtained from the Instance Metadata Service (see above):

& "C:\Program Files\Microsoft SQL Server\160\DAC\bin\sqlpackage.exe" /at:"$AccessToken" /Action:Export /SourceConnectionString:"Server=sqlserver01.database.windows.net;Initial Catalog=AppDb;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;" /tf:"C:\temp\AppDb.bacpac"

Azure SQL Managed Instance

The syntax is the same, with the managed instance name as the server:

"C:\Program Files\Microsoft SQL Server\160\DAC\bin\sqlpackage.exe" /Action:Export /TargetFile:"C:\temp\AppDb.bacpac" /SourceConnectionString:"Server=tcp:mymanagedinstance.database.windows.net,1433;Initial Catalog=AppDb;Authentication=Active Directory Managed Identity;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"

"C:\Program Files\Microsoft SQL Server\160\DAC\bin\sqlpackage.exe" /Action:Export /TargetFile:"C:\temp\AppDb.bacpac" /UniversalAuthentication:True /SourceConnectionString:"Server=mymanagedinstance.database.windows.net;Initial Catalog=AppDb;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"

Debugging an import with an external model.xml

To diagnose an import failure, extract model.xml from the BACPAC (a BACPAC is a ZIP archive) and point SqlPackage at it, with a diagnostics file:

& 'C:\Program Files\Microsoft SQL Server\160\DAC\bin\SqlPackage.exe' `
    /Action:Import `
    /SourceFile:'C:\backup\AppDb.bacpac' `
    /TargetConnectionString:"Server=tcp:sqlserver01.database.windows.net,1433;Initial Catalog=AppDb_test;Authentication=Active Directory Managed Identity;User ID=$uami;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30" `
    /DiagnosticsFile:'C:\backup\log\sqlpackagediag.log' `
    /ModelFilePath:'C:\backup\AppDb\model.xml'

Notes

Mastodon LLIMastodon SocialFosstodon