Transparent Database Encryption (TDE) Migration

Live migration

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

Before performing the following steps for live migration, ensure that DSM backup is successfully restored into the CipherTrust Manager. Add the VKM_mode parameter and set the corresponding value to no in cakm_mssql_ekm.properties file.

1. Fetch the keys from the CipherTrust Manager.

   USE Master;
   
   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
   

2. Create a TDE login and add credentials.

   CREATE LOGIN <tde_login_name>
   FROM ASYMMETRIC KEY <new_asym_key_name>;
   
   CREATE CREDENTIAL <tde_cred_name>
   WITH IDENTITY = '<domain_name||cm_username>',
   SECRET = '<cm_password>'
   FOR CRYPTOGRAPHIC PROVIDER <cakm_ekm_provider_name>;
   
   ALTER LOGIN <tde_login_name>
   ADD CREDENTIAL <tde_cred_name>;
   

   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. Rotate the Database Encryption Key (DEK).

   Note

   <algorithm_name> used for DEK should be same as that was used with VKM.

   • For Microsoft SQL Server versions 2016 and higher, use the below command to rotate the DEK.

     USE <db_name>
     
     ALTER DATABASE ENCRYPTION KEY
     REGENERATE WITH ALGORITHM=<algorithm_name>
     
     ALTER DATABASE ENCRYPTION KEY
     ENCRYPTION BY SERVER ASYMMETRIC KEY <new_asym_key_name>
     

     Note

     Here, ALTER DATABASE ENCRYPTION KEY REGENERATE WITH ALGORITHM=<algorithm_name> is optional as it rekeys the database that may take time depending on the resource of the system and the size of the database.

   • For Microsoft SQL Server versions lower than 2016, use the below command to rotate the DEK.

     USE <db_name>
     
     ALTER DATABASE ENCRYPTION KEY
     REGENERATE WITH ALGORITHM = <algorithm_name>
     ENCRYPTION BY SERVER ASYMMETRIC KEY <new_asym_key_name>;
     

   This completes the live migration for Transparent Database Encryption (TDE). TDE is enabled on <db_name> with CAKM for Microsoft SQL EKM provider.

Passive migration

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

Before performing the following steps for Passive Migration, ensure that DSM backup is successfully restored into the CipherTrust Manager.

1. Update the VKM_mode parameter, set the corresponding value to yes in cakm_mssql_ekm.properties file, and restart the Microsoft SQL Server Service.

2. Fetch the restored Asymmetric Key from the CipherTrust Manager.

   USE Master;
   
   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
   

   Note

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

3. Create a TDE login and add credentials.

   CREATE LOGIN <tde_login_name>
   FROM ASYMMETRIC KEY <new_asym_key_name>;
   
   CREATE CREDENTIAL <tde_cred_name>
   WITH IDENTITY = '<domain_name||cm_username>',
   SECRET = '<cm_password>'
   FOR CRYPTOGRAPHIC PROVIDER <cakm_ekm_provider_name>;
   
   ALTER LOGIN <tde_login_name>
   ADD CREDENTIAL <tde_cred_name>;
   

   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.

4. Before restoring the database, ensure that the database backup is copied to the desired Microsoft SQL Server node. Now, restore the database <db_name> using the below command.

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

5. Set the ENCRYPTION OFF for database <db_name>.

   Use <db_name>;
   
   ALTER DATABASE <db_name>
   SET ENCRYPTION OFF;
   

6. Drop the Database Encryption Key (DEK).

   DROP DATABASE ENCRYPTION KEY;
   

7. Set the database recovery mode from FULL to SIMPLE.

   USE master;
   
   ALTER DATABASE <db_name> SET RECOVERY SIMPLE WITH NO_WAIT
   

8. Set the VKM_Mode property to no in the cakm_mssql_ekm.properties file and restart the Microsoft SQL Server service.

9. Create a new asymmetric key for the database <db_name>.

   USE master;
   
   CREATE ASYMMETRIC KEY <new_key_name>
   FROM PROVIDER <cakm_ekm_provider_name>
   WITH ALGORITHM = <algorithm_name>,
   PROVIDER_KEY_NAME = '<key_name_on_cm>',
   CREATION_DISPOSITION=CREATE_NEW
   

   Note

   <algorithm_name> should be same as that was used with VKM.

10. Create a new TDE login and credentials.

    CREATE LOGIN <new_tde_login_name>
    FROM ASYMMETRIC KEY <new_asym_key_name>;
    
    CREATE CREDENTIAL <new_tde_cred_name>
    WITH IDENTITY = '<domain_name||cm_username>',
    SECRET = '<cm_password>'
    FOR CRYPTOGRAPHIC PROVIDER <cakm_ekm_provider_name>;
    
    ALTER LOGIN <new_tde_login_name>
    ADD CREDENTIAL <new_tde_cred_name>;
    

11. Create a new DEK for the database <db_name>.

    USE <db_name>
    
    CREATE DATABASE ENCRYPTION KEY
    WITH ALGORITHM = <algorithm_name>
    ENCRYPTION BY SERVER ASYMMETRIC KEY <new_asym_key_name>;
    

12. Set ENCRYPTION ON for database <db_name>.

    Use <db_name>;
    ALTER DATABASE <db_name>
    SET ENCRYPTION ON;
    

13. Set the database recovery mode from SIMPLE to FULL.

    USE master;
    ALTER DATABASE <db_name> SET RECOVERY FULL WITH NO_WAIT
    

    This completes the passive migration for Transparent Database Encryption (TDE). TDE is enabled on with CAKM for Microsoft SQL EKM provider.