Categories: Azure

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 🙂

Koen Verbeeck

Koen Verbeeck is a Microsoft Business Intelligence consultant at AE, helping clients to get insight in their data. Koen has a comprehensive knowledge of the SQL Server BI stack, with a particular love for Integration Services. He's also a speaker at various conferences.

Next T-SQL Tuesday #149 - T-SQL Advice you’d give to your younger self »

Previous « Book Review - Pro Serverless Data Handling with Microsoft Azure

View Comments

Nana says:

August 17, 2022 at 3:43 pm

This has saved me a lot of time hunting around for a reason for the error. Thank you very much. I had to simply remove the connection details from the "TargetConnectionString" tag and leave it blank in the Publish.xml file:

Thanks, Nana