[BUG]: SqlAzureDacpacDeployment@1 - error when using a Workload Identity federation

[zoemac11]

New issue checklist

• [X] I searched for existing GitHub issues
• [X] I read pipeline troubleshooting guide
• [X] I checked how to collect logs

Task name

SqlAzureDacpacDeployment@1

Task version

1.237.4

Issue Description

Getting the following error when using a Workload Identity federation service connection

##[error]Microsoft.PowerShell.Commands.WriteErrorException: ExceptionMessage: Cannot bind argument to parameter 'vstsAccessToken' because it is an empty string. (in function: Build-MSALInstance)Check out how to troubleshoot failures at https://aka.ms/sqlazuredeployreadme#troubleshooting-

Environment type (Please select at least one enviroment where you face this issue)

• [ ] Self-Hosted
• [X] Microsoft Hosted
• [ ] VMSS Pool
• [ ] Container

Azure DevOps Server type

dev.azure.com (formerly visualstudio.com)

Azure DevOps Server Version (if applicable)

No response

Operation system

Microsoft Windows Server 2022

Relevant log output

##[error]Microsoft.PowerShell.Commands.WriteErrorException: ExceptionMessage: Cannot bind argument to parameter 'vstsAccessToken' because it is an empty string. (in function: Build-MSALInstance)Check out how to troubleshoot failures at https://aka.ms/sqlazuredeployreadme#troubleshooting-

Full task logs with system.debug enabled

 Starting: Run migrations
==============================================================================
Task         : Azure SQL Database deployment
Description  : Deploy an Azure SQL Database using DACPAC or run scripts using SQLCMD
Version      : 1.237.4
Author       : Microsoft Corporation
Help         : https://docs.microsoft.com/azure/devops/pipelines/tasks/deploy/sql-azure-dacpac-deployment
==============================================================================
Added TLS 1.2 in session.
Temporary inline SQL file: C:\Users\VssAdministrator\AppData\Local\Temp\tmp56C8.tmp
Invoke-Sqlcmd -connectionString "**********"  -Inputfile "C:\Users\VssAdministrator\AppData\Local\Temp\tmp56C8.tmp" 
##[error]Microsoft.PowerShell.Commands.WriteErrorException: ExceptionMessage: Cannot bind argument to parameter 'vstsAccessToken' because it is an empty string. (in function: Build-MSALInstance)Check out how to troubleshoot failures at https://aka.ms/sqlazuredeployreadme#troubleshooting-
Finishing: Run migrations
 

Repro steps

pool: 
    vmImage: 'windows-latest'

  steps:           
    - task: SqlAzureDacpacDeployment@1
      displayName: 'Run migrations'
      inputs:
        azureSubscription: '<workload-identity-service-connection>'
        AuthenticationType: connectionString
        ConnectionString: 'Server=database.database.windows.net;Database=database;User ID=user;Password=password;'
        deployType: 'SqlTask'
        SqlFile: '$(System.DefaultWorkingDirectory)/migrations/migrations.sql'
        IpDetectionMethod: 'AutoDetect'

[verhaje]

Same configuration, same error. Any update on the cause or solution?

[D0nnyg]

For me the fix was to enable the following Azure SQL Database Networking option:

It looks like the underlying Azure DevOps server cannot reach the Azure Database.

The required steps:

• Navigate to https://portal.azure.com/
• Go to the (Subscription and then) Resource Group containing the Azure SQL Database
• Go to the Azure SQL Database component
• Go to "Security/Networking" in the pane on the left side
• If you have Public Access enabled for "Selected Networks" the above shown option is at the bottom of the screen

Hope this helps

[tomqiaozc]

You can try set system.debug to true in Pipeline Variables, and it will print detail logs. For me, I have same error because the ip address of my pipeline job VM is not allowed due to the settings of the SQL DB firewall.