Column Encryption: "Cannot drop column master key because column encryption key is encrypted by it."

[asrichesson]

• SqlPackage or DacFx Version: 162.0.52.1
• .NET Framework (Windows-only) or .NET Core: net core
• Environment (local platform and source/target platforms): Microsoft SQL Server 2019 (RTM-CU12) (KB5004524) - 15.0.4153.1 (X64) Jul 19 2021 15:37:34 Copyright (C) 2019 Microsoft Corporation Express Edition (64-bit) on Windows 10 Home 10.0 (Build 19045: )

Steps to Reproduce:

1. Create a table with a column using a COLUMN ENCRYPTION KEY referencing a COLUMN MASTER KEY
2. Build and deploy the database
3. Change the column encryption key and the column master key to use different values
4. Rebuild and redeploy the database
5. Expected: The data is decrypted according to the old keys and re-encrypted using the new keys
6. Actual: sqlpackage tries to drop the column master key before it drops the column encryption key resulting in an error

Example SQLProject: Demo.zip

Did this occur in prior versions? If not - which version(s) did it work in?

(DacFx/SqlPackage/SSMS/Azure Data Studio)

[asrichesson]

Additionally, only changing the column encryption key and redeploying yields the following error: There is already a column encryption key value associated with the column master key 'CMK_Auto1'

[DBarmanMS]

I'd like to get some more info about what you are trying to achieve. If it is CMK/CEK rotation then we've a guide for that using other tools which are much easier to use for this specific purpose - https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/rotate-always-encrypted-keys-using-ssms?view=sql-server-ver16.

[asrichesson]

@DBarmanMS I'm trying to get automated pipeline deployments working for column encryption. We have situations where devs do not have direct permissions to modify a database so they can't use ssms or powershell. Additionally, we'd like to use automated deployments to minimize human error. We use sqlpackage for our database deployments including initial column encryption.

Reading this makes me think that I should expect sqlpackage to decrypt and re-encrypt columns.	Condition	Action
The column is encrypted both in the DACPAC and the database, but the column in the DACPAC uses a different encryption type or/and a different column encryption key than the corresponding column in the database.	The data in the column will be decrypted and then re-encrypted to match the encryption configuration in the DACPAC.

[DBarmanMS]

The errors that you are facing is due to an existing CEK which uses the CMK with the same name that you are trying to create using the dacpac. Please try key rotation in a fresh DB and change the CEK/CMK name while generating the new dacpac.