Migration from SQL EKM to CAKM MSSQL EKM Provider
This section provides information/steps for migrating a database that has TDE enabled using SQL EKM to third party SQL EKM provider; for example,CAKM MSSQL EKM Provider. Previously your database master key is encrypted by either Certificate or Asymmetric Key which was generated in SQL Server. Now you want to generate a new database master key encrypted by asymmetric key generated on CipherTrust Manager. To do this you need to perform the following steps:
Note
We have tested these steps in SQL Server 2016.
Check the encryption status of the database (AdventureWorks).
select db.name, case dbe.encryption_state when 0 then 'No database encryption key present, no encryption' when 1 then 'Unencrypted' when 2 then 'Encryption in progress' when 3 then 'Encrypted' when 4 then 'Key change in progress' when 5 then 'Decryption in progress' when 6 then 'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)' end EncryptionState, dbe.percent_complete from sys.dm_database_encryption_keys dbe inner join sys.databases db on db.database_id = dbe.database_id
Run the following commands using SQL Server Management Studio or SQL Server command prompt.
sp_configure 'show advanced options', 1; RECONFIGURE; sp_configure 'EKM provider enabled', 1; RECONFIGURE; CREATE CRYPTOGRAPHIC PROVIDER <provider_name> FROM FILE "<path_ekm_dll>"
Create a credential.
CREATE CREDENTIAL <cred_name> WITH IDENTITY = <ks_user_name>, SECRET=<ks_user_password> FOR CRYPTOGRAPHIC PROVIDER <crypto_provider_name>
Add the credential to a user with the sysadmin role.
ALTER LOGIN <user-with-sysadmin-role> ADD CREDENTIAL <cred_name>
Create an asymmetric key and a login from that key.
CREATE ASYMMETRIC KEY <sql_asym_key_name> FROM PROVIDER <crypto_provider_name> WITH ALGORITHM=<algorithm_name>, PROVIDER_KEY_NAME=<key_name_in_ks>, CREATION_DISPOSITION=CREATE_NEW/OPEN_EXISTING
Create a login using the asymmetric key.
CREATE LOGIN <login_name> FROM ASYMMETRIC KEY <sql_asym_key_name>
Create a new credential.
CREATE CREDENTIAL <cred_name> WITH IDENTITY = <ks_user_name>, SECRET=<ks_user_password> FOR CRYPTOGRAPHIC PROVIDER <crypto_provider_name>
Add the credential to the new login created in the above step.
ALTER LOGIN <login_name> ADD CREDENTIAL <cred_name>
Recreate the database encryption key using this asymmetric key.
USE <db_name> ALTER DATABASE ENCRYPTION KEY REGENERATE WITH ALGORITHM = <algorithm_name> ENCRYPTION BY SERVER ASYMMETRIC KEY <sql_asym_key_name>
Note
In the SQL Server versions 2016 and above, separate commands to rotate database encryption key and master key are introduced.
Rotate Database Encryption Key:
ALTER DATABASE ENCRYPTION KEY REGENERATE WITH ALGORITHM=<algorithm_name>
Rotate Master Key:
ALTER DATABASE ENCRYPTION KEY ENCRYPTION BY SERVER ASYMMETRIC KEY <asymmetric_key>
For more information, refer to the Supported Algorithm section.
Alter the database again and set the encryption 'ON'.
ALTER DATABASE <db_name> SET ENCRYPTION ON
Now, check the database encryption status.
select db.name, case dbe.encryption_state when 0 then 'No database encryption key present, no encryption' when 1 then 'Unencrypted' when 2 then 'Encryption in progress' when 3 then 'Encrypted' when 4 then 'Key change in progress' when 5 then 'Decryption in progress' when 6 then 'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)' end EncryptionState , dbe.percent_complete from sys.dm_database_encryption_keys dbe inner join sys.databases db on db.database_id = dbe.database_id