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:
And then check to see if the key exists in the appropriate vault:
If it exists, you’ll be able to copy the database over without issue.
Procedure
Create a new key in the Key Vault:
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.