SQL Column Encryption- Migrating a single encryption key to another column master key

Problem

I have a SQL database which has column encryption enabled with master keys and encryption keys. Single master key has 4 encryption keys and I would like to migrate 1 encryption key to another new master key without any service disruption.

Issue

Tried using sql normal scripts/gui but we dont have an option to migrate a single encryption key rather we would need to migrate all encryption keys together by rotating and cleanup.

When we rotate, there is no option to select which key to rotate, rather it will rotate all keys to new master key.

Also once rotated we will have both master keys running and during cleanup, it will move all encryption keys to new one

Resolution

Since the sql out of box approach doesn't provide a solution to rotate a single encryption key out of many to another master key,  we have a solution for you.

•  Start with normal approach to rotate keys to new master key . Advantage with this approach is to have an encryption value created by sql automatically when you rotate. Also this will avoid any encryption/decryption issue if you use script and update encryption key with new master key and custom encrypted value.
• Once rotated, use below script to cleanup instead of cleaning up from sql gui.

• -- Drop old column master key from encryptionkey

   

  ALTER COLUMN ENCRYPTION KEY CEK_config

  DROP VALUE

  ( 

    COLUMN_MASTER_KEY = CMK_Configtest

  );

  GO 

The above drop sql script to be ran on all the encryption keys. But makes sure you drop the right master keys from encryptionkeys.

For ex: the ones doesn;t need to be migrated, you can drop the new masterkey and the one to be migrated, you can drop the old masterkey. This way the cleanup process is completed and the single encryptionkey is migrated to new masterkey without any downtime.

-- drop old masterkey to move to new one

ALTER COLUMN ENCRYPTION KEY [CEK_configtest]

DROP VALUE

( 

  COLUMN_MASTER_KEY = [CMK_Configtest]

);

GO 

-- drop new masterkey to retain in same one

ALTER COLUMN ENCRYPTION KEY [CEK_confignew]

DROP VALUE

( 

  COLUMN_MASTER_KEY = [CMK_test]

);

GO