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
- Login and password.
- User-assigned managed identity (UAMI), recommended: the access token is passed with
/at. - Service principal with a secret.
- Entra ID universal authentication (
/UniversalAuthentication:True), interactive.
Known limitations
- An export can fail with
Error exporting database: Unable to determine the database compatibility level. This is a Microsoft bug; workaround: export a copy of the database. - An access token has a limited lifetime (about 1 hour): a long export can fail if the token expires midway.
-- 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
- 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"
- 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"
- 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"
- Apply: same line with
Publishand 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:
- Add the SqlPackage folder to the session
PATHto avoid typing the full path:
$env:Path += ";C:\Program Files\Microsoft SQL Server\170\DAC\bin"
/DiagnosticsFile:"E:\tmp\sqlpackage.log"writes the full trace./p:ScriptDatabaseOptions=Falseon theScriptaction removes the preamble and keeps only the real differences.- Counting the
DROP COLUMNstatements in the script tells you whether the build DACPAC is consistent with the database:
(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
- Adapt the
DACfolder version number (160,170) to the installed version. - Identifiers, server and database names above are generic examples.