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.