Copying a Database in Azure with Always Encrypted Data

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 ENCRYPTED_VALUE:

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

Clean Up

To clean up, delete the newly created, CEK and the old CMK.

Leave a Reply

Your email address will not be published. Required fields are marked *