LLI Wiki — My Digital Knowledge Base

SqlPackage avec Azure SQL Database : authentification Entra ID, export et déploiement

Utiliser SqlPackage avec Azure SQL Database : jeton d'accès, service principal, authentification universelle, export BACPAC, comparaison et publication de DACPAC.

SqlPackage est l'utilitaire en ligne de commande des outils de données SQL Server (DacFx). Il permet d'extraire, exporter, importer, comparer et publier des bases SQL Server et Azure SQL Database sous forme de packages DAC (.dacpac, schéma) ou BACPAC (.bacpac, schéma et données). Cet article résume les modes d'authentification vers Azure SQL Database et quelques commandes courantes.

Modes d'authentification

Limites connues

-- Copie de la base dans le même serveur logique, à exporter ensuite
CREATE DATABASE [AppDb_copy] AS COPY OF [AppDb];
GO

Export avec un jeton d'accès

Le jeton est obtenu pour la ressource https://database.windows.net/, ici avec le module Az pour un compte déjà connecté (Connect-AzAccount, y compris avec une identité managée ou un 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;"

Sur les versions récentes du module Az, .Token peut être de type SecureString : dans ce cas, le convertir en texte avant de le passer à /at.

Service principal avec secret (MSAL, non testé)

Import-Module MSAL.PS

$TenantID     = '00000000-0000-0000-0000-000000000000'
$ClientID     = '00000000-0000-0000-0000-000000000000'
$ClientSecret = Read-Host -AsSecureString "Client secret"   # ne jamais écrire le secret dans le 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

Le jeton est ensuite passé à sqlpackage.exe avec /at:$AccessToken, comme ci-dessus. L'ancienne méthode ADAL (ADAL.PS, Get-AdalToken) est obsolète au profit de MSAL.

Authentification universelle (interactive)

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

# Extraction du schéma en DACPAC
SqlPackage /Action:Extract /UniversalAuthentication:True `
  /SourceConnectionString:"Server=myserver.database.windows.net;Initial Catalog=AppDb;Encrypt=True;" `
  /TargetFile:"E:\tmp\AppDb.dacpac"

Comparer et déployer un DACPAC

  1. Extraire le schéma actuel de la base cible :
& "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. Comparer le DACPAC issu du build à celui-ci, hors ligne, sans connexion, et générer le script de différences :
& "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. Générer le script du déploiement réel (rien n'est appliqué) :
& "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. Appliquer : même ligne avec Publish et sans /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

Astuces de diagnostic :

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

Importer un BACPAC avec un fichier de modèle externe

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"

Remarques

Mastodon LLIMastodon SocialFosstodon