When trying to copy a database with Always Encrypted data (say, to a different environment), you’ll generally want to recycle the Column Master Key used to match the vault stored in the same Azure resource group. This takes a little bit of work to do.
Create a new key in the Azure Key Vault:
az keyvault key create --name Always-Encrypted-Auto1 --vault-name VAULT_NAME
Next, create a new Column Master Key, using the newly created key above.
Next, create a Column Encryption Key using the newly created Column Master Key above:
Script the CREATE script for the new Column Encryption Key and copy
Run the following query with the copied
ENCRYPTED_VALUE to alter the current Column Encryption Key:
ALTER COLUMN ENCRYPTION KEY CEK_Auto1 ADD VALUE ( COLUMN_MASTER_KEY = [CMK_Auto2], ALGORITHM = 'RSA_OAEP', ENCRYPTED_VALUE = VALUE_FROM_ABOVE ); GO ALTER COLUMN ENCRYPTION KEY CEK_Auto1 DROP VALUE ( COLUMN_MASTER_KEY = CMK_Auto1 ); GO
To clean up, delete the newly created, CEK and the old CMK.