Azure Devops build pipeline to create .bacpac from Azure SQL DB

Goal: setup an Azure DevOps build pipeline with Service Connection to create a .bacpac from an Azure SQL database using sqlpackage.exe

As I understand there are 3 requirements:

1. DevOps Service connection e.g. myServiceConnection. Created web application with managed identity called myWebAppManagedIdentity In project settings, service connection has 'Grant access to all pipelines'.

2. Permission on the Azure Sql DB to the DB.
   I've gone overboard with db_owner, trying to get it to work:

   CREATE USER [myWebAppManagedIdentity] FROM EXTERNAL PROVIDER;

   ALTER ROLE db_owner ADD MEMBER [myWebAppManagedIdentity];

3. YAML file with instruction:

    trigger:
    - main
    
    pool:
      vmImage: 'windows-latest'
    
    variables:
      sourceServerName: 'some-srv.database.windows.net'
      sourceDatabaseName: 'somedb'
     
    steps:
   
    - script: |
        "C:\Program Files\Microsoft SQL Server\160\DAC\bin\SqlPackage.exe" /Action:Export /TargetFile:"$(Pipeline.Workspace)/s/$(sourceDatabaseName).bacpac" /SourceConnectionString:"Server=tcp:$(sourceServerName),1433;Initial Catalog=$(sourceDatabaseName);Authentication=Active Directory Managed Identity;MultipleActiveResultSets=False;

Running the pipeline returns error:

"C:\Program Files\Microsoft SQL Server\160\DAC\bin\SqlPackage.exe" /Action:Export /TargetFile:"D:\a\1/s/somedb.bacpac" /SourceConnectionString:"Server=tcp:some-srv.database.windows.net,1433;Initial Catalog=somedb;Authentication=Active Directory Managed Identity;MultipleActiveResultSets=False; ========================== Starting Command Output =========================== "C:\Windows\system32\cmd.exe" /D /E:ON /V:OFF /S /C "CALL "D:\a_temp\5670cb81-285e-4e1d-8570-a65f39dff472.cmd"" Connecting to database 'somedb' on server 'tcp:some-srv.database.windows.net,1433'. *** ManagedIdentityCredential authentication unavailable. The requested identity has not been assigned to this resource.

I created the service connection and use the identity to extract dacpac, can reproduce the same with you.

To resolve this, you can use task Azure SQL Database deployment which contains the connection setting, script task doesn't contain the connection info.

1. choose service principal option(it uses the Authenticaiton data from connection)

2. Extract action

steps:
- task: SqlAzureDacpacDeployment@1
  displayName: 'Azure SQL DacpacTask'
  inputs:
    azureSubscription: ARMConn2
    AuthenticationType: servicePrincipal
    ServerName: $(sourceServerName).database.windows.net
    DatabaseName: $(sourceDatabaseName)
    DeploymentAction: Extract

The task will generate AccessToken based on the identity:

Make sure dbowner permission has been got for the identity, which has been done in your step2.

CREATE USER [myWebAppManagedIdentity] FROM EXTERNAL PROVIDER;

ALTER ROLE db_owner ADD MEMBER [myWebAppManagedIdentity];

The method you are using is typically for system assigned managed identity, you can check the link for your reference, it generates accesstoken for connection as well.

Use Azure powershell task(as it contains connection data) with script below, it's also working.

- task: AzurePowerShell@5
  displayName: 'Azure PowerShell script: InlineScript'
  inputs:
    azureSubscription: ARMConn2
    ScriptType: InlineScript
    Inline: |
     $AccessToken_Object = (Get-AzAccessToken -Resource "https://database.windows.net/")
     $AccessToken = $AccessToken_Object.Token
     
     & "C:\Program Files\Microsoft SQL Server\160\DAC\bin\SqlPackage.exe" /at:$AccessToken /Action:Extract /TargetFile:"test.dacpac" /SourceConnectionString:"Server=tcp:$(sourceServerName),1433;Initial Catalog=$(sourceDatabaseName);MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
    azurePowerShellVersion: LatestVersion