[BUG]: SqlAzureDacpacDeployment Fails When Using Workload Identity Federation

[MarcelMichau]

Task name

SqlAzureDacpacDeployment

Task version

1.225.1

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

• Self-Hosted
• 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

Windows Server 2022

Task log

##[debug]Entering Add-AzureSqlDatabaseServerFirewallRule.
##[debug] endpoint: '@{Url=https://management.azure.com/; Data=; Auth=}'
##[debug] startIPAddress: '13.79.44.0'
##[debug] endIPAddress: '13.79.44.255'
##[debug] serverName: 'sql-marcel-michau'
##[debug] firewallRuleName: '76bad11c-2caf-4ba5-b08d-891b1183b31e'
##[debug] connectedServiceNameARM: '3b37c47e-bf1a-4a31-88bf-d3c55d24d2d2'
##[debug] vstsAccessToken: '***'
##[debug]Creating firewall rule 76bad11c-2caf-4ba5-b08d-891b1183b31e
##[debug]Connection type used is WorkloadIdentityFederation
##[debug]Connection type used is WorkloadIdentityFederation
##[debug]Connection type used is WorkloadIdentityFederation
##[debug]Exception message - System.Management.Automation.RuntimeException: Unsupported authentication scheme 'WorkloadIdentityFederation' for Azure endpoint.
##[debug]No Firewall Rule was added
##[debug]Caught exception from task script.
##[debug]Error record:
##[debug]System.Management.Automation.RuntimeException: Unsupported authentication scheme 'WorkloadIdentityFederation' for Azure endpoint.Check out how to troubleshoot failures at https://aka.ms/sqlazuredeployreadme#troubleshooting-
##[debug]At D:\a\_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.225.1\DeploySqlAzure.ps1:226 char:5
##[debug]+     throw $errorMessage
##[debug]+     ~~~~~~~~~~~~~~~~~~~
##[debug]    + CategoryInfo          : OperationStopped: (System.Manageme...roubleshooting-:String) [], RuntimeException
##[debug]    + FullyQualifiedErrorId : System.Management.Automation.RuntimeException: Unsupported authentication scheme 'Worklo    adIdentityFederation' for Azure endpoint.Check out how to troubleshoot failures at https://aka.ms/sqlazuredeployre    adme#troubleshooting-
##[debug] 
##[debug]Script stack trace:
##[debug]at <ScriptBlock>, D:\a\_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.225.1\DeploySqlAzure.ps1: line 226
##[debug]at <ScriptBlock>, <No file>: line 1
##[debug]at <ScriptBlock>, <No file>: line 22
##[debug]at <ScriptBlock>, <No file>: line 18
##[debug]at <ScriptBlock>, <No file>: line 1
##[debug]Exception:
##[debug]System.Management.Automation.RuntimeException: System.Management.Automation.RuntimeException: Unsupported authentication scheme 'WorkloadIdentityFederation' for Azure endpoint.Check out how to troubleshoot failures at https://aka.ms/sqlazuredeployreadme#troubleshooting-
##[error]System.Management.Automation.RuntimeException: Unsupported authentication scheme 'WorkloadIdentityFederation' for Azure endpoint.Check out how to troubleshoot failures at https://aka.ms/sqlazuredeployreadme#troubleshooting-
##[debug]Processed: ##vso[task.logissue type=error]System.Management.Automation.RuntimeException: Unsupported authentication scheme 'WorkloadIdentityFederation' for Azure endpoint.Check out how to troubleshoot failures at https://aka.ms/sqlazuredeployreadme#troubleshooting-
##[debug]Processed: ##vso[task.complete result=Failed]

Relevant log output

##[error]System.Management.Automation.RuntimeException: Unsupported authentication scheme 'WorkloadIdentityFederation' for Azure endpoint.Check out how to troubleshoot failures at https://aka.ms/sqlazuredeployreadme#troubleshooting-

Aditional info

Hi There,

I've recently converted an Azure Service Connection to use workload identity federation & when using the SqlAzureDacpacDeployment task with this Service Connection, it fails with the attached error.

After some investigation, this might be caused by the IsAzureRmConnection function in VstsAzureRestHelpers_.psm1 which does not check if $connectionType is $wifConnection:

azure-pipelines-tasks/Tasks/Common/VstsAzureRestHelpers_/VstsAzureRestHelpers_.psm1

Line 103 in cfc97f2

if (($connectionType -eq $spnConnection) -or ($connectionType -eq $MsiConnection)) {

Happy to provide any additional information if required.

Pipeline logs: https://dev.azure.com/marcelmichau/Personal/_build/results?buildId=5015&view=logs&j=6c434506-33ce-52e3-30f1-def0355013e5&t=a238fb55-122e-5c75-0386-86a8958c7523

Task in repo: https://github.com/MarcelMichau/fake-survey-generator/blob/78564da5bc8eae8a0645a43b1a6bba750c2797d3/.azuredevops/azure-pipelines.yml#L271

[geekzter]

@MarcelMichau what OS are you using?

[MarcelMichau]

Hi @geekzter , this is using Windows Server 2022

[andrewfabrizi]

We're seeing the same issue running on windows-latest.

[dpickeringjudge]

any updates here?

[LeftTwixWand]

I'm taking this issue

[LeftTwixWand]

Hey everyone. There is some update regarding this issue.
@MarcelMichau was right about the root cause. We've added the missing type to ARM types.
The updated version of the task will be released tomorrow.

Thanks everyone for your patience.

[MarcelMichau]

This is fantastic news!

Thank you @LeftTwixWand for your effort in fixing this, much appreciated!

[anish714]

@LeftTwixWand, has this been released? I'm still seeing this issue for in Azure DevOps AzureFileCopy@5 task

[LeftTwixWand]

Hey @anish714 looks like we have a delay for some customers, because we're not releasing a new version for all the customers at once, but rather enabling it by chanks. I'm trying to clarify the current release state.

[LeftTwixWand]

Hey @anish714 @MarcelMichau can you please confirm, that this task works for you now?

[MarcelMichau]

Hi @LeftTwixWand , I just ran a pipeline again now & the task is still on version 1.225.1 which doesn't have the fix yet.

[LeftTwixWand]

@MarcelMichau Thank you for your response. From what I see, we're still enabling this feature for the last rings.
Sorry for such a delay. I'll let you know when the updated version will be delivered to all the customers.
I think it might take few more days.

[LeftTwixWand]

Hey @MarcelMichau can you please run the pipeline one more time to verify that the new version is available for you?

[MarcelMichau]

Hi @LeftTwixWand , I can confirm the task is now updated to version 1.231.0 & is working as expected.

I'm happy that this issue can be closed.

Thank you again for getting this sorted for us!

[LeftTwixWand]

Hey @MarcelMichau, glad to hear that!
Thak you and everyone who contributed and helped us to solve this issue.

[arsh3186]

@anish714 Did your issue resolved with updated version?
I am also getting same issue with workloadIdentityFederation however SQL Dacpactask is working

[LeftTwixWand]

Hey @arsh3186 can you please provide, which task version do you have?