Azure DevOps - Changes are not deploying on PROD for Azure SQL Database even though release pipeline is getting succeeded

Question

I'm setting up an Azure DevOps for Azure SQL Database by deploying the SQL DacPac file through Release pipeline. I'm promoting changes from DEV to PROD database.

I'm new to the Azure DevOps did all the setup through Azure documentation and google, got stuck at this point.

Build pipeline and release pipeline are getting succeeded but the changes are not reflecting in PROD database. Please find below the YAML of release pipeline:

steps:
- task: SqlAzureDacpacDeployment@1
  displayName: 'Azure SQL DacpacTask'
  inputs:
    azureSubscription: 'azure_subscription'
    ServerName: server_name
    DatabaseName: 'database_name'
    SqlUsername: user_name
    SqlPassword: password
    DeploymentAction: DeployReport
    DacpacFile: '$(System.DefaultWorkingDirectory)/AzureDB_Test/Test/bin/Debug/Test.dacpac'

Please find below the logs of Azure SQL DacPacTask :

2024-01-06T21:27:27.9410537Z ##[section]Starting: Azure SQL DacpacTask
2024-01-06T21:27:27.9722384Z ==============================================================================
2024-01-06T21:27:27.9722835Z Task         : Azure SQL Database deployment
2024-01-06T21:27:27.9723374Z Description  : Deploy an Azure SQL Database using DACPAC or run scripts using SQLCMD
2024-01-06T21:27:27.9723284Z Version      : 1.232.0
2024-01-06T21:27:27.9723394Z Author       : Microsoft Corporation
2024-01-06T21:27:27.9723957Z Help         : https://docs.microsoft.com/azure/devops/pipelines/tasks/deploy/sql-azure-dacpac-deployment
2024-01-06T21:27:27.9723395Z ==============================================================================
2024-01-06T21:27:29.7550294Z Added TLS 1.2 in session.
2024-01-06T21:27:44.6571057Z Temporary inline SQL file: C:\Users\VssAdministrator\AppData\Local\Temp\tmpC89E.tmp
2024-01-06T21:27:44.9123856Z Invoke-Sqlcmd -ServerInstance "server_name" -Database "database_name" -Username "user_name"  -Password "password"  -Inputfile "C:\Users\VssAdministrator\AppData\Local\Temp\tmpC89E.tmp"  **-ConnectionTimeout 120**
2024-01-06T21:27:58.3224482Z DACPAC file path: D:\a\r1\a\_nEDS_AzureDB_Test\nEDS_Test\bin\Debug\nEDS_Test.dacpac
2024-01-06T21:27:58.6926284Z ##[command]"D:\a\_tasks\SqlAzureDacpacDeployment_ch87a08b-a478-4e2b-8369-1d37u9ab560f\1.232.0\vswhere.exe" -version [15.0,18.0) -latest -format json
2024-01-06T21:27:59.5484384Z "C:\Program Files\Microsoft SQL Server\160\DAC\bin\SqlPackage.exe" /Action:DeployReport /SourceFile:"D:\a\r1\a\AzureDB_Test\Test\bin\Debug\Test.dacpac" /TargetServerName:"server_name" /TargetDatabaseName:"database_name" /TargetUser:"user_name" /TargetPassword:"password" /OutputPath:"D:\a\r1\a\GeneratedOutputFiles\ACAEDS_Prod_DevOps_DeployReport.xml"  /**TargetTimeout:120**
2024-01-06T21:28:04.0578492Z Generating report for database 'database_name' on server 'server_name'.
2024-01-06T21:28:23.7792056Z Successfully generated report to file D:\a\r1\a\GeneratedOutputFiles\Report.xml.
2024-01-06T21:28:23.7803000Z Changes to connection setting default values were incorporated in a recent release.  More information is available at https://aka.ms/dacfx-connection
2024-01-06T21:28:23.7806733Z Time elapsed 0:00:20.21
2024-01-06T21:28:23.8142166Z Generated file D:\a\r1\a\GeneratedOutputFiles\Report.xml. Uploading file to the logs.
2024-01-06T21:28:23.8160223Z Setting output variable 'SqlDeploymentOutputFile' to 'D:\a\r1\a\GeneratedOutputFiles\.xml'
2024-01-06T21:28:23.8509958Z ##[section]Finishing: Azure SQL DacpacTask

Could the issue happens to the production database be related to assigning only the Reader role to the service principal on the resource group i am working on? Is it necessary to have the Contributor role for these operations to be successful? I'm restricted here I can not get the Contributor role on the Resource Group.

Answer 1

You can use Publish option instead of DeployReport to create/update the target database with dacpac file. Please check the argument here for your reference.

*** Could not deploy package. Error SQL72014: Framework Microsoft SqlClient Data Provider: Msg 15247, Level 16, State 13, Line 5 User does not have permission to perform this action. Error SQL72045: Script execution error. ..

You are using SQL server authentication(username, password) to deploy the dacpac, and the error indicates the user doesn't have permission to update the DB. Please grant higher permission for the account, you can find the permission on the answer here.

In addition, the Public network access on azure database is disabled by default. You can use select network and add the DevOps agent ip to the whitelhist, so that it can access the DB.

My yaml for your reference:

pool:
  vmImage: Windows-latest


steps:
- task: AzureCLI@2     # get agent ip and add it to DB white list.
  inputs:
    azureSubscription: 'ARMConn3'
    scriptType: 'ps'
    scriptLocation: 'inlineScript'
    inlineScript: |
      $ip =Invoke-RestMethod -uri "http://ifconfig.me/ip"
      az sql server firewall-rule create -g "testRG" -s testserver1 -n myrule --start-ip-address $ip --end-ip-address $ip

- task: SqlAzureDacpacDeployment@1
  displayName: 'Azure SQL DacpacTask'
  inputs:
    azureSubscription: 'ARMConn3'
    AuthenticationType: 'server'
    ServerName: 'testserver1.database.windows.net'
    DatabaseName: 'wadedb1'
    SqlUsername: 'wade'
    SqlPassword: '$(password)'
    deployType: 'DacpacTask'
    DeploymentAction: 'Publish'
    DacpacFile: '**/wadedb1.dacpac'

- task: AzureCLI@2       # delete the agent ip on sql network
  inputs:
    azureSubscription: 'ARMConn3'
    scriptType: 'ps'
    scriptLocation: 'inlineScript'
    inlineScript: |
      $ip =Invoke-RestMethod -uri "http://ifconfig.me/ip"
      az sql server firewall-rule delete -g "testRG" -s testserver1 -n myrule

Edit:

We can store the sql logins and passwords in keyvault and get the value in pipeline for Azure SQL DacpacTask.

Step1: On the azure key valut, create the username and password secrets.

Step2: Grant Key Vault Secrets User for the service principal(from ARM service connection)

Step3: add AzureKeyVault@2 task before your azure SQL Dacpac task to get the secrets value, and use $(username) and $(password) in the azure SQL Dacpac task for deployment.

- task: AzureKeyVault@2
  inputs:
    azureSubscription: 'ARMConn3'
    KeyVaultName: 'wadekeyvalut7'
    SecretsFilter: '*'
    RunAsPreJob: true

- task: SqlAzureDacpacDeployment@1
  displayName: 'Azure SQL DacpacTask'
  inputs:
    azureSubscription: 'ARMConn3'
    AuthenticationType: 'server'
    ServerName: 'testserver1.database.windows.net'
    DatabaseName: 'wadedb1'
    SqlUsername: '$(username)'
    SqlPassword: '$(password)'
    deployType: 'DacpacTask'
    DeploymentAction: 'Publish'
    DacpacFile: '**/wadedb1.dacpac'

Another option is to use 'servicePrincipalas AuthenticationType in the Dacpac task, it will use the service principal to deploy instead of the sql user. But it needs to grantSQL server admin` on azure portal for the service principal.

- task: SqlAzureDacpacDeployment@1
  inputs:
    azureSubscription: 'ARMConn3'
    AuthenticationType: 'servicePrincipal'
    ServerName: 'testserver1.database.windows.net'
    DatabaseName: 'wadedb1'
    deployType: 'DacpacTask'
    DeploymentAction: 'Publish'
    DacpacFile: '**/wadedb1.dacpac'