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