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.
2
Answers
TL;DR Service connection names cannot be stored in variable groups. Declare them as variables in your YAML pipeline or template instead.
Also, remember to queue at least one build manually in order to authorize any service connection(s).
More details
Variables in variable groups are available at runtime so they are only available after a pipeline starts.
Given that a service connection is a protected resource that needs to be authorized before the pipeline starts (i.e. at compile time), we cannot use variables from variable groups. Only values that are available at compile time can be used.
Example
/pipelines/variables/dev-variables.yaml
/pipelines/variables/qa-variables.yaml
/pipelines/variables/prod-variables.yaml
my-pipeline.yaml
From the error message, it shows the service connection name:
DEVOPS_mdwh_connection_prod
. This means that the parameters have passed the correct value to theazureSubscription
field. The parameters in the YAML file should be correct.The cause of the issue could be that the service connection itself is not valid.
In this case, it will show the same error.
For example:
When you use the Workload identity federation type service connection, it has not verify option when editing the service connection.
You can use the following steps to check if the Workload identity federation type service connection is valid.
Step1: Manually search the sql task in YAML UI editor.
Step2: You can select the sql task and check if you can see the
DEVOPS_mdwh_connection_prod
service connection in the service connections dropdown list.If you cannot see the service connection in the dropdown list, this means that the service connection itself is not valid.
To solve this issue, you can navigate to Project Settings -> Service Connections and create new automatically Workload identity federation type ARM Service connection in Azure DevOps UI and check if it can work.
Or you can try to create a manually Workload identity federation type ARM Service connection. For more detailed info, you can refer to this doc: Manually set an Azure Resource Manager workload identity service connection
Note:
AuthenticationType
field inSqlAzureDacpacDeployment@1
is used to control Azure SQL server connections. It is not related toazureSubscription
field and not supportWorkloadIdentityFederation
value. Refer to this doc: SqlAzureDacpacDeployment@1 – Azure SQL Database deployment v1 task