Using CAKM for Microsoft SQL Server EKM Provider with Transparent Database Encryption

When Transparent Database Encryption (TDE) is enabled on your Microsoft SQL Server, a Database Encryption Key (DEK) is created on the database server. The DEK encrypts database pages as they are written to disk, and decrypts pages as they are read. You can provide an extra level of security by using a CipherTrust Manager key to encrypt the DEK itself.

Enabling TDE Using CAKM for Microsoft SQL Server EKM Provider

To enable TDE using CAKM for Microsoft SQL Server EKM Provider:

1. Create an asymmetric key protected by the CAKM for Microsoft SQL Server EKM Provider.

2. Create a credential protected by the asymmetric key.

3. Create a symmetric database encryption key, protected by the asymmetric key.

4. Enable TDE using the database encryption key.

Creating an Asymmetric Key Protected by the CAKM for Microsoft SQL Server EKM Provider

You must create an asymmetric key that will secure the DEK.

To create a new asymmetric key on the CipherTrust Manager, execute the following command:

CREATE ASYMMETRIC KEY <key_name_in_sql_server>
FROM PROVIDER <provider_name>
WITH ALGORITHM = <algorithm_name>,
PROVIDER_KEY_NAME = <key_name_in_cipherTrust_manager>,
CREATION_DISPOSITION=CREATE_NEW

For example:

CREATE ASYMMETRIC KEY SQL_EKM_RSA_2048_Key
FROM PROVIDER <provider_name>
WITH ALGORITHM = RSA_2048,
PROVIDER_KEY_NAME = 'EKM_RSA_2048_Key',
CREATION_DISPOSITION=CREATE_NEW

Creating a Login Protected by the Asymmetric Key

1. Execute the following statement to create a credential that will be used by the Database Engine:

   CREATE CREDENTIAL <tde_credential_name>
   WITH IDENTITY = <cipherTrust_manager_user>,
   SECRET = <cipherTrust_manager_user_password>
   FOR CRYPTOGRAPHIC PROVIDER <provider_name>;
   

   Note

   The credential name <tde_credential_name> can be any value, and must be different than the credential name <credential_name>, we already created under Creating Credentials with Microsoft SQL Server.

   The provider name <provider_name> must be the same as we already created under Loading the DLL.

   Note

   It is recommended that the CipherTrust Manager user <cipherTrust_manager_user> is the same as the user used in Creating Credentials with Microsoft SQL Server

   If the CipherTrust Manager user is <cipherTrust_manager_tde_user>, which is other than the one used in Creating Credentials with Microsoft SQL Server, you need to perform the following steps:

   1. Create a group on the CipherTrust Manager.

   2. Add the user, <cipherTrust_manager_tde_user>, to the group.

   3. Add the group to the Asymmetric Key.

   4. Grant Encrypt-Always and Decrypt-Always permissions.

   If you do not perform these steps, then the following error occurs while trying to create the database encryption symmetric key:

   Msg 15209, Level 16, State 1, Line 1
   An error occurred during encryption.
   

   The CipherTrust Manager Activity Log does not show any error.

2. Execute the following statement to add a login used by TDE, and add the new credential to the login:

   CREATE LOGIN <tde_login_name>
   FROM ASYMMETRIC KEY <asymmetric_key_name>;
   GO
   ALTER LOGIN <tde_login_name>
   ADD CREDENTIAL <tde_credential_name>;
   GO
   

Creating a Symmetric Database Encryption Key

1. The DEK will encrypt your database. To create the DEK: Change to the database that will be encrypted:

   USE <db_name>;
   GO
   

2. Execute the following code to create the database encryption key that will be used for TDE:

   CREATE DATABASE ENCRYPTION KEY
   WITH ALGORITHM = <algorithm_name>
   ENCRYPTION BY SERVER ASYMMETRIC KEY <key name in database>;
   GO
   

   For example:

   CREATE DATABASE ENCRYPTION KEY
   WITH ALGORITHM = AES_128
   ENCRYPTION BY SERVER ASYMMETRIC KEY SQL_EKM_RSA_2048_Key ;
   GO
   

   Note

   If any changes are made to the cakm_mssql_ekm.properties file, we need to refresh the setup to reflect the changes. It includes the following steps:

   • Dropping the credentials

   • Dropping Cryptographic Provider

   • Creating Cryptographic Provider

   • Creating credentials

   • Associating the credentials with sa login

3. Execute the following to alter the database to enable TDE:

   ALTER DATABASE <database_name>
   SET ENCRYPTION ON;
   GO
   

The database is now stored in an encrypted format.

Setting Encryption for the Database

After DEK is created, you must alter the database to set the encryption on.

To set encryption on, execute the following:

ALTER DATABASE <database_name> SET ENCRYPTION ON;

To disable encryption, execute the following:

ALTER DATABASE <database_name> SET ENCRYPTION OFF;

Verifying the Tables Encrypted Using TDE

To verify the tables which are encrypted with DEK, execute the following:

SELECT DB_NAME(e.database_id) AS DatabaseName, e.database_id, e.encryption_state,
CASE e.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'
END AS encryption_state_desc, c.name, e.percent_complete
FROM sys.dm_database_encryption_keys AS e
LEFT JOIN master.sys.asymmetric_keys AS c
ON e.encryptor_thumbprint = c.thumbprint

Only the TDE databases available on the server are included in the result of this command. The databases with Encryption ON are listed as Encrypted and the databses with Encryption OFF are listed as Unencrypted.

Retrieving the list of Databases available in a SQL Server Instance

To retrieve the list of all databases available in a SQL Server instance, execute the following:

select name, database_id, create_date, user_access_desc, state_desc, is_encrypted, physical_database_name FROM sys.databases;

It will list all the databases whether they are encrypted by TDE or not.

Rotating a TDE Key

To rotate a TDE key, perform the following steps:

1. Create an asymmetric key protected by CAKM for Microsoft SQL Server EKM provider.

2. Create a credential protected by the asymmetric key.

3. Regenerate a DEK by executing the following SQL statements:

   ALTER DATABASE ENCRYPTION KEY
   REGENERATE WITH ALGORITHM = <algorithm_name>
   ENCRYPTION BY SERVER ASYMMETRIC KEY <key_name_in_sql_server>;
   
   !!! note
   
       In the Microsoft 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 `<key_name_in_sql_server>`