LLI Wiki — My Digital Knowledge Base

SqlPackage with Azure SQL Database: Entra ID authentication, export and deployment

Using SqlPackage with Azure SQL Database: access tokens, service principal, universal authentication, BACPAC export, DACPAC comparison and publish.

SqlPackage is the command-line utility of the SQL Server data tools (DacFx). It extracts, exports, imports, compares and publishes SQL Server and Azure SQL Database databases as DAC packages (.dacpac, schema) or BACPAC files (.bacpac, schema and data). This article summarises the authentication methods for Azure SQL Database and a few common commands.

Authentication methods

Known limitations

-- Copy the database on the same logical server, then export the copy
CREATE DATABASE [AppDb_copy] AS COPY OF [AppDb];
GO

Export with an access token

The token is requested for the resource https://database.windows.net/, here with the Az module for an already signed-in account (Connect-AzAccount, including with a managed identity or a service principal):

$AccessToken = (Get-AzAccessToken -ResourceUrl "https://database.windows.net/").Token
$serverName    = "myserver.database.windows.net,1433"
$CatalogueName = "AppDb"
$TargetFile    = "E:\temp\AppDb.bacpac"

& "C:\Program Files\Microsoft SQL Server\160\DAC\bin\sqlpackage.exe" /at:$AccessToken /Action:Export /TargetFile:$TargetFile `
    /SourceConnectionString:"Server=$serverName;Initial Catalog=$CatalogueName;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"

With recent versions of the Az module, .Token may be a SecureString: convert it to plain text before passing it to /at.

Service principal with a secret (MSAL, untested)

Import-Module MSAL.PS

$TenantID     = '00000000-0000-0000-0000-000000000000'
$ClientID     = '00000000-0000-0000-0000-000000000000'
$ClientSecret = Read-Host -AsSecureString "Client secret"   # never hard-code the secret in the script

$App = New-MsalClientApplication -ClientId $ClientID -ClientSecret $ClientSecret `
        -Authority "https://login.microsoftonline.com/$TenantID"
$AccessToken = (Get-MsalToken -ClientApplication $App -Scopes "https://database.windows.net/.default").AccessToken

The token is then passed to sqlpackage.exe with /at:$AccessToken, as above. The older ADAL method (ADAL.PS, Get-AdalToken) is obsolete in favour of MSAL.

Universal authentication (interactive)

# Export a BACPAC
SqlPackage /Action:Export /UniversalAuthentication:True `
  /SourceConnectionString:"Server=myserver.database.windows.net;Initial Catalog=AppDb;Encrypt=True;" `
  /TargetFile:"E:\tmp\AppDb.bacpac"

# Extract the schema to a DACPAC
SqlPackage /Action:Extract /UniversalAuthentication:True `
  /SourceConnectionString:"Server=myserver.database.windows.net;Initial Catalog=AppDb;Encrypt=True;" `
  /TargetFile:"E:\tmp\AppDb.dacpac"

Compare and deploy a DACPAC

  1. Extract the current schema of the target database:
& "C:\Program Files\Microsoft SQL Server\170\DAC\bin\SqlPackage.exe" /Action:Extract /UniversalAuthentication:True /SourceConnectionString:"Server=myserver.database.windows.net;Initial Catalog=AppDb;Encrypt=True;" /TargetFile:"E:\tmp\live.dacpac"
  1. Compare the build DACPAC with it, offline with no connection, and generate the difference script:
& "C:\Program Files\Microsoft SQL Server\170\DAC\bin\SqlPackage.exe" /Action:Script /SourceFile:"E:\tmp\AppDb_build.dacpac" /TargetFile:"E:\tmp\live.dacpac" /TargetDatabaseName:"AppDb" /OutputPath:"E:\tmp\diff.sql"
  1. Generate the real deployment script (nothing is applied):
& "C:\Program Files\Microsoft SQL Server\170\DAC\bin\SqlPackage.exe" /Action:Script /UniversalAuthentication:True /SourceFile:"E:\tmp\AppDb_build.dacpac" /TargetServerName:"myserver.database.windows.net" /TargetDatabaseName:"AppDb" /OutputPath:"E:\tmp\deploy.sql"
  1. Apply: same line with Publish and without /OutputPath:
& "C:\Program Files\Microsoft SQL Server\170\DAC\bin\SqlPackage.exe" /Action:Publish /UniversalAuthentication:True /SourceFile:"E:\tmp\AppDb_build.dacpac" /TargetServerName:"myserver.database.windows.net" /TargetDatabaseName:"AppDb" /p:BlockOnPossibleDataLoss=False

Debugging tips:

$env:Path += ";C:\Program Files\Microsoft SQL Server\170\DAC\bin"
(Select-String -Path "E:\tmp\deploy.sql" -Pattern "DROP COLUMN").Count

Import a BACPAC with an external model file

SqlPackage /Action:Import /SourceFile:"E:\bacpacs\AppDb.bacpac" `
  /TargetConnectionString:"Server=tcp:myserver.database.windows.net,1433;Initial Catalog=AppDb;Authentication=Active Directory Managed Identity;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;User ID=<client-id-of-the-managed-identity>;" `
  /DiagnosticsFile:"E:\logs\sqlpackage.log" `
  /ModelFilePath:"E:\bacpacs\AppDb\model.xml"

Notes

Mastodon LLIMastodon SocialFosstodon