Error when deploying DACPAC via Azure DevOps Pipelines

Question

I am flushing out a CI/CD process with Azure SQL DB deployed via Azure DevOps Pipelines. I am using the Adventure works database and set up a visual studio project importing the schema.

I have a pipeline configured to publish the dacpac and run a subsequent deployment using the SqlAzureDacpacDeployment@1 and am getting the below error:

2020-10-10T02:36:34.1421137Z ##[error]Unable to connect to target server 'server.database.windows.net'. Please verify the connection information such as the server name, login credentials, and firewall rules for the target server.
2020-10-10T02:36:34.1605855Z ##[error]Windows logins are not supported in this version of SQL Server.
2020-10-10T02:36:34.2143924Z ##[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-
2020-10-10T02:36:34.2522414Z ##[section]Finishing: SqlAzureDacpacDeployment

I am using windows latest and here is my YAML pipeline:

trigger:
- master

pool:
  vmImage: 'windows-latest'


jobs: 
- job: BuildDeploySQL
  variables:
  - group: SQLServerLogin
  steps:
  - task: VSBuild@1
    inputs:
      solution: '**\*.sln'
  - task: PublishPipelineArtifact@1
    inputs:
      targetPath: '$(Pipeline.Workspace)'
      publishLocation: 'pipeline'
  - task: SqlAzureDacpacDeployment@1
    inputs:
      azureSubscription: 'Subscription Name here'
      AuthenticationType: 'server'
      ServerName: 'server.database.windows.net'
      DatabaseName: 'AdventureWorks'
      SqlUsername: 'sqladmin'
      SqlPassword: ${{ variables.Password }}
      deployType: 'DacpacTask'
      DeploymentAction: 'Publish'
      DacpacFile: '$(Pipeline.Workspace)\s\AdventureWorks\bin\Debug\*.dacpac'
      IpDetectionMethod: 'AutoDetect'

I have tried to deploy from my local machine and it is successful using the same sql credentials. Additionally I have confirmed that the SQL Database has allow Azure Services enabled. I have also tried to deploy the dacpac to a new empty database and get this same error.

I do believe this could be just a generic error message as my deployment logs do show a successful connection to the server:

2020-10-10T02:36:18.7912964Z Invoke-Sqlcmd -ServerInstance "server.database.windows.net" -Database "AdventureWorks" -Username "sqladmin"  -Password ******  -Inputfile 
....
2020-10-10T02:36:33.0554895Z Initializing deployment (Start)

** Update Just to rule out I did create a new SQL Login with DBO_owner permissions and ran the deployment using that and got the same error message.

Answer 1

The root issue was the password secret contained characters which escaped Powershell. Wrapping the secret in "" resolved it.

Answer 2

Above error is probably because the build agent ip is not allow-listed in the firewall rules of your Azure SQL Database. See the this link about IP ranges for Microsoft-hosted agents.

You can check the firewall rules setting of your azure database, and try allowing all IP ranges.

You can aslo add Azure CLi task to get the agent ip and set a firewall rule for your azure database to allow the agent ip dynamically in your pipeline. See this thread.

steps:
- task: AzureCLI@2
  displayName: 'Azure CLI '
  inputs:
    azureSubscription: 'azureSubscription'
    scriptType: ps
    scriptLocation: inlineScript
    inlineScript: |
     
     $agentIp = (New-Object net.webclient).downloadstring("http://checkip.dyndns.com") -replace "[^\d\.]"
     
     az sql server firewall-rule create -g $(rg) -s $(server) -n test --start-ip-address $agentIp --end-ip-address $agentIp

You can also create a self-hosted agent on your local machine/Azure VM. and run your pipeline on this self-hosted agent. Note to allow-list your local machine ip for the azure database.