Automated deployment of Azure SQL Database with Azure DevOps

Question

I am using ServicePrincipal to execute database queries.

I have exactly the same problem as described on this page: https://github.com/ErikEJ/erikej.github.io/issues/29

Generating a migration script and executing it like following works:

- script: 'dotnet ef migrations script   --idempotent  --output migrations.sql --project DBPROJECT --startup-project ./APIPROJECT/ -v'
  displayName: Create EF Scripts

- task: AzurePowerShell@5
  displayName: Capture DB access token
  inputs:
    azureSubscription: $(ServiceConnection)
    ScriptType: 'InlineScript'
    azurePowerShellVersion: LatestVersion
    Inline: |
        Import-Module Az.Accounts -MinimumVersion 2.2.0

        $tenantID = '$(tenant)'
        $clientID = '$(clientId)'
        $clientSct = '$(clientSecret)'

        $request = Invoke-RestMethod -Method POST `
           -Uri "https://login.microsoftonline.com/$tenantID/oauth2/token"`
           -Body @{ resource="https://database.windows.net/"; grant_type="client_credentials"; client_id=$clientid; client_secret=$clientSct }`
           -ContentType "application/x-www-form-urlencoded"
        $access_token = $request.access_token
        Write-Host("##vso[task.setvariable variable=access_token;issecret=true]$($access_token)")
        Write-Output "Access token type is $($request.token_type), expires $($request.expires_on)"

- task: AzurePowerShell@5
  displayName: 'Execute SQL Migration Script'
  inputs:
    azureSubscription: $(serviceConnection)
    ScriptType: inlineScript
    Inline: Invoke-Sqlcmd -AccessToken "$(access_token)" -ServerInstance "$(serverName).database.windows.net" -Database "$(dbName)" -InputFile "$(System.DefaultWorkingDirectory)\migrations.sql"
    azurePowerShellVersion: 'LatestVersion'

However below task (provided by Microsoft) seems not to acquire access token correctly and as a result I got "Login failed for user 'token-identified principal'"

Below task is not working, AccessToken is not being generated using 'servicePrincipal' authenticationType and due to that migration script cannot be executed.

- task: SqlAzureDacpacDeployment@1
  displayName: 'Execute SQL Migration Script'
  inputs:
    azureSubscription: $(ServiceConnection)
    AuthenticationType: 'servicePrincipal'
    ServerName: '$(serverName).database.windows.net'
    DatabaseName: $(dbName)
    TaskNameSelector: 'SqlTask'
    SqlFile: '$(System.DefaultWorkingDirectory)\migrations.sql'
    IpDetectionMethod: 'AutoDetect'

Answer 1

AuthenticationType: 'servicePrincipal' requires add servicePrincipal service connection