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:

Pre-Reqs

You’ll need to have the following software installed:

  • SSMS
  • Azure CLI

You’ll also need to make sure the database you’re copying from has a key that already exists. Run the following query on your newly copied database:

select * from sys.column_master_keys

And then check to see if the key exists in the appropriate vault:

az keyvault key show --id KEY_PATH

If it exists, you’ll be able to copy the database over without issue.

Procedure

Create a new key in the Key Vault:

az keyvault key create --name Always-Encrypted-Auto1 --vault-name VAULT_NAME

Next, create a new Column Master Key, using the created key above.

With two CMKs, rotate and then cleanup the initial CMK.

After this is done, you can delete the old CMK.

Leave a Reply

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