Using SafeNet ProtectApp MSSQL EKM Provider with Transparent Database Encryption

When Transparent Database Encryption (TDE) is enabled on your MSSQL 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 SafeNet ProtectApp MSSQL EKM Provider

To enable TDE using SafeNet ProtectApp MSSQL EKM Provider:

1. Install the EKM provider as described in Installing SafeNet ProtectApp MSSQL EKM Provider.

2. Enable EKM in your SQL Server Database, load the SafeNet ProtectApp MSSQL EKM Provider DLL, and create credentials in SQL Server, as described in Configuring SafeNet ProtectApp MSSQL EKM Provider in SQL Server Database

3. Create an asymmetric key protected by the SafeNet ProtectApp MSSQL EKM Provider.

4. Create a credential protected by the asymmetric key.

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

6. Enable TDE using the database encryption key.

Creating an Asymmetric Key Protected by the SafeNet ProtectApp MSSQL EKM Provider

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

You must use the master database while creating an asymmetric key protected by SafeNet ProtectApp MSSQL EKM Provider.

Ensure that Allow Key and Policy Configuration Operations in the NAE Server Settings section of the CipherTrust Manager's Management Console is enabled. Otherwise, all attempts to create a key will fail.

To enable the settings, follow the below steps:

1. Log on to the Management Console as an administrator with SSL, Advanced Security, and Key Server access controls.

2. Navigate to the NAE Server Configuration page.

3. Click a protocol, or select a protocol and click Properties. The NAE Server Properties section is displayed.

4. Click Edit.

5. Select the Allow Key and Policy Configuration Operations check box.

6. Click Save.

7. Navigate to the High Security Configuration page (Security > High Security).

8. Click Edit under the High Security Settings section.

9. Clear the Disable RSA Encryption and Decryption and Disable Non-FIPS Algorithms and Key Sizes check boxes.

10. Click Save.

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 safenetSQLEKM
WITH ALGORITHM = RSA_2048,
PROVIDER_KEY_NAME = 'EKM_RSA_2048_Key',
CREATION_DISPOSITION=CREATE_NEW

Remember to back up this key. If you lose the key used to encrypt the DEK, you will not be able to access your database.

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

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

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

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

   If the CipherTrust Manager user is <cipherTrust_manager_tde_user>, which is other than the one used in Creating Credentials with 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
   

   If any changes are made to the IngrianNAE.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 Which Tables are Encrypted Using TDE

To verify which tables are encrypted with the 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

All databases on the server are included in the results of this command. Those databases with encryption on are listed as Encrypted. Those databases with encryption off are listed as'Unencrypted.

Rotating a TDE Key

To rotate a TDE key, perform the following steps:

1. Create an asymmetric key protected by SQL 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>;
   

   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 <key_name_in_sql_server>