SqlPackage.exe deploy DACPAC with always encrypted and Key vault

Question

I am trying to deploy a DACPAC using Azure Release pipeline.

Following are the methods which I used

1. Using Azure SQL DACPAC task - While using this approach, as part of additional properties following set of params are passed for deployment AzureKeyVaultAuthMethod:ClientIdSecret /ClientId:'$(SERVICEPRINCIPALID)' /Secret:'$(SERVICEPRINCIPALKEY)'. On enabling diagnostics log, getting following error: SqlPackage build version - 16.0.6161.0

2. Using a power-shell script - While using the power-shell script in order to do the DACPAC deployment, I am getting the following error.

Failed to decrypt a column encryption key. Invalid key store provider name: 'AZURE_KEY_VAULT'. A key store provider name must denote either a system key store provider or a registered custom key store provider. Valid system key store provider names are: 'MSSQL_CERTIFICATE_STORE', 'MSSQL_CNG_STORE', 'MSSQL_CSP_PROVIDER'. Valid (currently registered) custom key store provider names are: . Please verify key store provider information in column master key definitions in the database, and verify all custom key store providers used in your application are registered properly.

Logs attached here SqlPackage build version - 15.0.5472.2 As a pre-requisite before deploying the DACPAC, using a power-shell script I am creating the keys and then inserting them to DB. Contents of the script below.

All the deployments are happening through a service principal and it has admin level access on all the resources in the Azure AD.

Am I missing any steps here before the deployment of DACPAC through release pipeline.

Thanks,

Nandan

Answer 1

1. The Failed to decrypt a column encryption key. Invalid key store provider name: 'AZURE_KEY_VAULT' is related to the version of code trying to decrypt the column. Update your Nuget packages to the latest version (that supports Azure Key Vault integration) and double check that your target within the .sqlproj is set to Azure SQL and not Sql Server.

2. Another hurdle you're going to face is the currently active key vs. what the DACPAC generated. If a CMK/CEK is referenced anywhere, and you have a hardcoded version of the CMK/CEK then the deploy will fail with an encryption/decryption based issue. I'd remove the references and make sure the service account has permissions to rotate / use Azure Key vault keys via policy AND within the database itself.