Error SQL71621: Azure Key Vault URI '' must be an well-formed, absolute URL

Question

I have an Azure SQL Database using Always Encrypted with the Column Master Key being stored in Azure Key Vault. The project is in Azure DevOps and I am using the Azure SQL DacpacTask to deploy my releases to my dev and prod environments. I am using a SQLCMD variable called CMK1_KeyPath in my project to pass the environment appropriate AKV URI to the SqlPackage.exe. It works fine deploying to dev but when I try to deploy to prod I get the following error 'Error SQL71621: Azure Key Vault URI '$(CMK1_KeyPath)' must be an well-formed, absolute URL'

I have upgraded my dev and prod Azure remote agents to the latest version, 3.245.0, as well as the latest version of the DacFramework, 162.4.92.3 with no luck.

My Sqlpackage.exe looks like this

"C:\Program Files\Microsoft SQL Server\160\DAC\bin\SqlPackage.exe" /Action:Publish /SourceFile:"C:\AzureDevOps\orgname\SQLAgentPool_work\r104\a_Project-CI\Project\Project-DB.dacpac" /TargetConnectionString:"Server=tcp:my-azure-db.privatelink.database.windows.net,1433;Initial Catalog=myDatabase;Persist Security Info=False;User ID=myUser;Password=myPassword;MultipleActiveResultSets=False;Encrypt=False;TrustServerCertificate=True;Connection Timeout=30;" /v:CMK1_KeyPath=https://my-kv.vault.azure.net/keys/Always-Encrypted-Auto1/97a8c01ab2fc43e0bd3ef2be69a64309

Any advice would be much appreciated

Answer 1

Hi ,

Thanks for reaching out to Microsoft Q&A.

The error you’re encountering (SQL71621: Azure Key Vault URI '$(CMK1_KeyPath)' must be a well-formed, absolute URL) usually occurs when the Azure SQL DacpacTask or SqlPackage.exe isn’t able to interpret the CMK1_KeyPath variable properly in your production deployment.

Here are a few steps to resolve this issue:

1. Verify the SQLCMD Variable Substitution in DevOps Pipeline: Ensure that the CMK1_KeyPath SQLCMD variable is set correctly within the DevOps pipeline, particularly for the production environment. Sometimes, variable scopes or names in the pipeline setup might differ between environments, so double-check the DevOps pipeline variable section.
2. Set the Variable in the Pipeline Task: Explicitly set the CMK1_KeyPath variable in the DacpacTask definition. You can pass the CMK1_KeyPath directly in the pipeline’s Azure SQL DacpacTask configuration:

   • task: SqlAzureDacpacDeployment@1 inputs: azureSubscription: 'Your-Azure-Service-Connection' serverName: 'your-server.database.windows.net' databaseName: 'your-database' sqlUsername: '$(sqlUsername)' sqlPassword: '$(sqlPassword)' deployType: 'DacpacTask' deploymentAction: 'Publish' sqlFile: '$(System.DefaultWorkingDirectory)/your-path/your-file.dacpac' additionalArguments: '/v:CMK1_KeyPath="https://my-kv.vault.azure.net/keys/Always-Encrypted-Auto1/97a8c01ab2fc43e0bd3ef2be69a64309"'

   3. Check the Variable Syntax: Sometimes, $() syntax is missed when passing variables between different tools or configurations. Ensure $(CMK1_KeyPath) resolves correctly. You can add a debugging task in your pipeline to log the resolved CMK1_KeyPath value to verify it’s correct. Local Agent Variable Substitution: If you’re deploying through a self-hosted agent, check that the agent’s environment has permissions to read Azure Key Vault URIs if the variable is accessed through a secured mechanism or credential store. Hardcode for Testing: As a temporary troubleshooting step, try hardcoding the CMK1_KeyPath URI in the SQL project and see if the error persists during the production deployment. If this resolves the error, the issue is likely within the variable resolution process in the DevOps pipeline. Use /p: Instead of /v:: For some versions of SqlPackage.exe, the /p:ColumnMasterKeySettings argument is necessary for specifying Azure Key Vault URIs directly. If SqlPackage.exe supports it, try:

   /p:ColumnMasterKeySettings="CMK1_KeyPath=https://my-kv.vault.azure.net/keys/Always-Encrypted-Auto1/97a8c01ab2fc43e0bd3ef2be69a64309"

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.