Column Level Encryption (CLE) Migration

Create provider and credentials

Create the provider and credentials for the user, using the following commands. In this document, we are using the user "sa".

USE master;

CREATE CRYPTOGRAPHIC PROVIDER <cakm_ekm_provider_name>
FROM FILE = '<Installation_Dir>\CipherTrust\CAKM For SQLServerEKM\cakm_mssql_ekm.dll';
CREATE CREDENTIAL sa_cakm_ekm_credential
WITH IDENTITY ='<domain-name||cm_username>',
SECRET = '<cm_user_password>'
FOR CRYPTOGRAPHIC PROVIDER <cakm_ekm_provider_name>
ALTER LOGIN sa ADD CREDENTIAL sa_cakm_ekm_credential;

Live migration

Run the live migration steps on an up and running database.

1. Fetch the restored Asymmetric and Symmetric Keys from the CipherTrust Manager.

   Note

   Use a key name other than that was used with VKM.

   • Fetching Asymmetric Key

     CREATE ASYMMETRIC KEY <new_asym_key_name> 
     FROM PROVIDER <cakm_ekm_provider_name>
     WITH
     PROVIDER_KEY_NAME = '<key_name_in_cipherTrust_manager>',
     CREATION_DISPOSITION=OPEN_EXISTING
     

   • Fetching Symmetric Key

     CREATE SYMMETRIC KEY <new_sym_key_name>
     FROM Provider <cakm_ekm_provider_name>
     With
     PROVIDER_KEY_NAME = '<key_name_in_cipherTrust_manager>',
     CREATION_DISPOSITION=OPEN_EXISTING
     

   Note

   Ensure that you perform the above steps on all the replicas present in the Always On High Availability Group before rotating the DEK on the primary replica.

2. Encrypt and decrypt the data using the keys fetched above. It is assummed that a table <table_name> already exists in the database.

   • Encryption and decryption using Asymmetric key <new_asym_key_name>

     INSERT INTO <table_name> VALUES (1,'fName1',ENCRYPTBYASYMKEY(AsymKey_ID('<new_asym_key_name>'), 'lName1'));
     
     select ID, Fname, convert(varchar(max),DECRYPTBYASYMKEY(AsymKey_ID('<new_asym_key_name>'),Lname)) Lname from <table_name>
     

   • Encryption and decryption using Symmetric key <new_sym_key_name>

     INSERT INTO <table_name> values(1,'fName1',ENCRYPTBYKEY(KEY_GUID('<new_sym_key_name>'),'lName1'))
     
     SELECT ID, FNAME, CONVERT(VARCHAR(MAX), DECRYPTBYKEY(LName)) FROM  <table_name>
     

   This completes the live migration for Column Level Encryption (CLE).

Passive migration

Run the passive migration steps on a restored MS SQL database backup.

Before proceeding with passive migration, ensure that the database backup is copied to the desired Microsoft SQL Server node.

1. Restore the database for column level encryption and decryption.

   RESTORE DATABASE <db_name> FROM DISK = '<backup_file_path>\<backup_file_name>.bak' WITH REPLACE;
   

2. Fetch the restored asymmetric and symmetric keys from the CipherTrust Manager.

   Note

   Use a key name other than that was used with VKM.

   • Fetching an asymmetric key

     USE <db_name>
     
     CREATE ASYMMETRIC KEY <new_asym_key_name> 
     FROM PROVIDER <cakm_ekm_provider_name>
     WITH
     PROVIDER_KEY_NAME = '<key_name_on_cm>',
     CREATION_DISPOSITION=OPEN_EXISTING
     

   • Fetching a symmetric key

     USE <db_name>
     
     CREATE SYMMETRIC KEY <new_sym_key_name>
     FROM Provider <cakm_ekm_provider_name>
     With
     PROVIDER_KEY_NAME = '<key_name_on_cm>',
     CREATION_DISPOSITION=OPEN_EXISTING
     

   Note

   Ensure that you perform the above steps on all the replicas present in the Always On High Availability Group before rotating the DEK on the primary replica.

3. Encrypt and decrypt data using the keys fetched above. It is assumed that a table <table_name> already exists in the database.

   • Encryption and decryption using the asymmetric key <new_asym_key_name>

     INSERT INTO <table_name> VALUES (1,'fName1',ENCRYPTBYASYMKEY(AsymKey_ID('<new_asym_key_name>'), 'lName1'));
     
     select ID, Fname, convert(varchar(max),DECRYPTBYASYMKEY(AsymKey_ID('<new_asym_key_name>'),Lname)) Lname from <table_name>
     

   • Encryption and decryption using the symmetric key <new_sym_key_name>

     INSERT INTO <table_name> values(1,'fName1',ENCRYPTBYKEY(KEY_GUID('<new_sym_key_name>'),'lName1'))
     
     SELECT ID, FNAME, CONVERT(VARCHAR(MAX), DECRYPTBYKEY(LName)) FROM  <table_name>
     

   This completes the passive migration for Column Level Encryption (CLE).