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 the initial CMK (using credentials from the source key vault, and then the destination key vault).

Next, clean up the previous 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 *