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.
I recently read the book Agile Data Warehouse Design - Collaborative Dimensional Modeling, from Whiteboard…
You can find the slides for the session Building the €100 data warehouse with the…
I was asked to do a review of the book Microsoft Power BI Performance Best…
This is a quick blog post, mainly so I have the code available if I…
Praise whatever deity you believe in, because it's finally here, a tenant switcher for Microsoft…
This book was making its rounds on social media, and the concept seems interesting enough…
View Comments
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