Azure DevOps SQL DacPac deployment not working with Service Connection

KoenvanWielink
July 8, 2024
108 views
0 votes
2 Answers

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.

Answers

- RuiJarimba
- July 5, 2024 at 4:23 pm
- 0 votes
0
Since the name of the service connection is correct and configured based on variables in the variable groups

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
```
variables:
  - name: azureSubscription
    value: dev-service-connection

  # other dev variables here
```
/pipelines/variables/qa-variables.yaml
```
variables:
  - name: azureSubscription
    value: qa-service-connection

  # other qa variables here
```
/pipelines/variables/prod-variables.yaml
```
variables:
  - name: azureSubscription
    value: prod-service-connection

  # other prod variables here
```
my-pipeline.yaml
```
jobs:
  - deployment: deployment_job_${{ parameters.env }}
    displayName: Deployment Job ${{ parameters.env }}
    environment: Deploy to ${{ parameters.env }}
    variables:
      - template: /pipelines/variables/${{ parameters.env }}-variables.yaml # <------- reference variables template here
    strategy:
      runOnce:
        deploy:
          steps:
            - checkout: self
              displayName: 1. Retrieve repository

            - task: SqlAzureDacpacDeployment@1
              displayName: 2. Deploy DACPAC
              inputs:
                azureSubscription: ${{ variables.azureSubscription }} # <------- from variables template referenced above
                # other inputs...
```
Login or Signup to reply.

- KevinLuMSFT
- July 8, 2024 at 9:28 am
- 0 votes
0
here 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.

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 the azureSubscription 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 in SqlAzureDacpacDeployment@1 is used to control Azure SQL server connections. It is not related to azureSubscription field and not support WorkloadIdentityFederation value. Refer to this doc: SqlAzureDacpacDeployment@1 – Azure SQL Database deployment v1 task

Login or Signup to reply.