Authentication Error in Azure Devops when Deploying DACPAC

I was building a CI/CD pipeline in Azure Devops, where I was building my SSDT project into a DACPAC file and then deploying it to the development server. For the deployment step, I use the built-in Azure SQL Database deployment task. I was planning on using SQL Server authentication, so I created a SQL user, gave it appropriate permissions and I added the username and password to the variables of the pipeline.

I used the resulting dacpac from the build step and I also included a publish profile that I created in Visual Studio. This profile dictates what should be deployed, what should be ignored etc. However, when I ran my pipeline, I got the following error message:

##[error]*** Cannot use ‘Authentication=Active Directory Interactive’ with ‘Password’ or ‘PWD’ connection string keywords.

Whoah, I was not trying to use Azure Active Directy Interactive authentication at all. I tried adding the flag /UniversalAuthentication:False, but this didn’t help. When I executed sqlpackage.exe (the tool responsible for the dacpac deployment) locally on the command line, I got the same error message, so it’s not exactly an Azure Devops issue, but rather a sqlpackage.exe issue.

After some searching, I found the following issue on github: Sql Package doesn’t override all connection string properties when username and password are set. Wait, there are connection string properties in the publish profile? Whoops. I used the publish profile in Visual Studio to manually deploy the database solution to the development database. This profile had saved my connection information and also had set the authentication method to Interactive in the connection string.

Once I had cleaned out this publish profile, the build pipeline succeeded without errors.

------------------------------------------------
Do you like this blog post? You can thank me by buying me a beer 🙂