Column Level Encryption

Create Provider and Credentials for Migration

Create the provider and credentials for the user, using the below 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;

CLE Live Migration

The live migration steps are run on an up and running database.

1. Fetch the restored Asymmetric and Symmetric Keys from the CipherTrust Manager, using the following commands:

   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_on_cm>',
     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_on_cm>',
     CREATION_DISPOSITION=OPEN_EXISTING
     

2. To encrypt and decrypt the data using the keys fetched above, run the below commands.
   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>
     

   With that you are done encrypting and decrypting the column.

CLE Passive Migration

Passive migration steps are run 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. To restore the database for Column level encryption and decryption, run the below command.

   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, using the following commands:

   Note

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

   • Fetching 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 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
     

3. To encrypt data using the keys fetched above, run the below commands. It is assumed 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>
     

   With that you are done encrypting the column.