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:
You’ll need to have the following software installed:
- 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.
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.