Azure DevOps deployment error to Azure SQL Database using Always Encrypted

Question

I am getting an error trying to deploy to an Azure SQL Database with Always Encrypted using Azure DevOps Azure SQL DacpacTask. The Column Master Key encryption keys is in Azure Key Vault. I have created a SQLCMD variable called $(CMK1_KeyPath) in my project to pass the AKV URI as a variable to sqlpackage.exe. It works fine deploying to my development Azure SQL Database, but when I try to deploy to production I receive the following error 'Error SQL71621: Azure Key Vault URI '$(CMK1_KeyPath)' must be an well-formed, absolute URL'.

I have updated my remote Azure Devops Agent to version 3.245.0 for both DEV and PROD and updated SqlPackage.exe to version 162.4.92 on both agent servers.

Any advice would be helpful

Thanks,

JB

Answer 1

Hi J B,

Thanks for reaching out to Microsoft Q&A.

The error 'Error SQL71621: Azure Key Vault URI '$(CMK1_KeyPath)' must be a well-formed, absolute URL' suggests that the variable $(CMK1_KeyPath) is not being correctly interpreted as a fully qualified URI in production. Here are some steps you could try to resolve this:

Verify Variable Scope: In Azure DevOps, ensure that $(CMK1_KeyPath) is defined in the pipeline’s variable group or directly within the pipeline for the production environment. Sometimes, variables defined only at the pipeline level in one environment (e.g., development) may not carry over to another (e.g., production).

Confirm Variable Format: Double-check the value assigned to $(CMK1_KeyPath) in the production environment. Ensure it is a well-formed URI (e.g., https://yourvaultname.vault.azure.net/keys/yourkeyname/version). Also, test it directly in production by echoing it in the pipeline log or deploying a simple change that outputs it to confirm the format.

Agent-Specific Configuration: Although you've updated SqlPackage.exe and the Azure DevOps Agent version, the error may stem from a configuration or permissions difference between the DEV and PROD agents. Verify that the production agent has the correct permissions to access Azure Key Vault and that the Key Vault's access policies include permissions for the agent's identity.

Debug the SQLCMD Variable Handling: Run a simplified deployment script or debug step that solely outputs the SQLCMD variable in production. This can help confirm if the pipeline correctly substitutes the variable value for $(CMK1_KeyPath).

Testing these can help pinpoint whether it's an environment-specific issue or an Azure DevOps variable handling problem in the pipeline.

Please feel free to click the 'Upvote' (Thumbs-up) button and 'Accept as Answer'. This helps the community by allowing others with similar queries to easily find the solution.