Azure DevOps SQL DacPac deployment not working with Service Connection

Question

For one of our clients I'm configuring a build and deployment pipeline in Azure DevOps for an Azure SQL database. We have done this many times before without issue, but this is the first time I'm using a service connection of the type "Workload identity federation". I've modified the YAML code as required and connected the variable groups to the pipeline but I keep getting the following error:

There was a resource authorization issue: "The pipeline is not valid. Job deployment_job_prod: Step input azureSubscription references service connection DEVOPS_mdwh_connection_prod which could not be found. The service connection does not exist, has been disabled or has not been authorized for use.

Since the name of the service connection is correct and configured based on variables in the variable groups, I know that those can be accessed. Pressing the "Authorize resources" button next to the error message doesn't seem to do anything. I've tried changing the AuthorizationType in the YAML script to servicePrincipal, WorkloadIdentityFederation or just disabled it, but that doesn't seem to make a difference either.

I'm at a bit of a loss here. Microsoft recommends using Workload Identity Federation over service principals now so I'm reluctant to change back to that. Can anyone here help me figure out what I'm missing? Below is the YAML code that's being used as well as the service connection settings:

- name: env
  displayName: Environment
  type: string
  values:
    - tst
    - acc
    - prod
- name: ServiceConnectionPrefix
  displayName: Service Connection Prefix
  type: string
- name: SQLDatabaseName
  displayName: SQL Database Name
  type: string
- name: SQLProjectName
  displayName: SQL Project Name
  type: string

jobs:
  - deployment: deployment_job_${{ parameters.env }}
    displayName: Deployment Job ${{ parameters.env }}
    environment: Deploy to ${{ parameters.env }}
    variables:
      - group: 'VG-MDWH-${{ upper(parameters.env) }}'

    strategy:
      runOnce:
        deploy:
          steps:
            - checkout: self
              displayName: 1. Retrieve repository

            - task: SqlAzureDacpacDeployment@1
              displayName: 2. Deploy DACPAC
              inputs:
                azureSubscription: '${{ parameters.ServiceConnectionPrefix}}${{ parameters.env }}'
                ## AuthenticationType: 'WorkloadIdentityFederation'
                ServerName: '$(ServerName)'
                DatabaseName: '${{ parameters.SQLDatabaseName }}'
                deploymentType: 'DacpacTask'
                DeploymentAction: 'Publish'
                DacpacFile: '$(Pipeline.Workspace)/${{ parameters.SQLProjectName }}/${{ parameters.SQLProjectName }}/bin/debug/${{ parameters.SQLProjectName }}.dacpac'
                PublishProfile: '$(Pipeline.Workspace)/${{ parameters.SQLProjectName }}/${{ parameters.SQLProjectName }}/${{ parameters.SQLProjectName }}_${{ parameters.env }}.publish.xml'

Service connection settings:

As you can see here there are no restrictions on the service connection either:

Any help would be appreciated.

Answer 1

The solution was about as stupid as you can get. I left a space in front of the ServiceConnectionPrefix variable. Removing the space also solved the problem... I will now hide in shame for a decade...