Release Devops pipeline failing for Azure Synapse Dedicated SQL Pool DB

Question

Hello All,

Use case: I have build .dacpac file using VS build and .dacpac is ready as an artifact using below CI pipeline
jobs:
- job: 'Builddacpac'
displayName: 'Build SQL Pool dacpac'

  steps:  
  - task: VSBuild@1  
    displayName: 'Builds the dacpac'  
    inputs:  
      solution: Create dacpac from ADS test.sqlproj  
      configuration: $(BuildConfiguration)  

  - task: PublishBuildArtifacts@1  
    displayName: 'Publishes dacpac as an artifact'  
    # Publishes the dacpac as part of an artifact within Azure DevOps  
    inputs:  
      PathtoPublish: 'bin\$(BuildConfiguration)'  
      ArtifactName: SQLPool  
      publishLocation: 'Container'  

Issue: I am trying to deploy the dacpac on the Synapse Dedicated SQL Pool database.
Database is already created and its accessible via SSMS without any issue.
But while doing a release with the same credentials its not working.
Note: I am using admin creds which has access to everything.
Below is release pipeline

jobs:
- job: 'DeployDedicated'
displayName: 'Deploy to dedicated SQL Pool'

  steps:  
  - task: DownloadBuildArtifacts@0  
    inputs:  
      buildType: 'current'  
      downloadType: 'single'  
      artifactName: 'SQLPool'  
      downloadPath: '$(System.ArtifactsDirectory)'  

  - task: SqlAzureDacpacDeployment@1  
    displayName: 'Deploy DACPAC to dedicated SQL Pool'  
    inputs:  
      azureSubscription: '$(EntSub)'  
      AuthenticationType: 'server'  
      ServerName: '{YOUR SYNAPSE DEDICATED SQL ENDPOINT}'  
      DatabaseName: 'DedicatedPool'  
      SqlUsername: '$(SynapseUser)'  
      SqlPassword: '$(Synapsepw)'  
      deployType: 'DacpacTask'  
      DeploymentAction: 'Publish'  
      DacpacFile: '$(System.ArtifactsDirectory)\SQLPool\Create dacpac from ADS test.dacpac'  
      AdditionalArguments: '/p:TreatVerificationErrorsAsWarnings=True'  

==============================

Error which I am getting:
2022-11-22T10:34:42.6693441Z ##[error]*** Could not deploy package.
2022-11-22T10:34:42.6799643Z ##[error]Unable to connect to target server '********-workspace.sql.azuresynapse.net'. Please verify the connection information such as the server name, login credentials, and firewall rules for the target server.
2022-11-22T10:34:42.6936044Z ##[error]Login failed for user '$(SynapseUser)'.
2022-11-22T10:34:42.7607142Z ##[error]The Azure SQL DACPAC task failed. SqlPackage.exe exited with code 1.Check out how to troubleshoot failures at https://aka.ms/sqlazuredeployreadme#troubleshooting-
2022-11-22T10:34:42.8535623Z ##[section]Finishing: Azure SQL DacpacTask

Answer 1

I had created env variabled and linked with Azure key vault but didnt linked that key vault with the release pipeline. Linking the key vault variable with release pipeline resolved the issue