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 and then cleanup the initial CMK.
After this is done, you can delete the old CMK.