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
Note
Before enabling TDE, ensure that:
CAKM for Microsoft SQL Server EKM provider is installed as described in Installing CAKM for Microsoft SQL Server EKM Provider
EKM is enabled, CAKM for Microsoft SQL Server EKM Provider DLL is loaded, and credentials are created in Microsoft SQL Server, as described in Configuring CAKM for Microsoft SQL Server EKM Provider in SQL Server Database.
To enable TDE using CAKM for Microsoft SQL Server EKM Provider:
Create an asymmetric key protected by the CAKM for Microsoft SQL Server EKM Provider.
Create a symmetric database encryption key, protected by the asymmetric key.
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.
Note
You must use the master database while creating an asymmetric key protected by CAKM for Microsoft SQL Server EKM Provider.
To create a new asymmetric key on the CipherTrust Manager, execute the following command:
For example:
Note
Remember to back up this key. If you lose the key being used to encrypt the DEK, you will not be able to access your database.
Creating a Login Protected by the Asymmetric Key
Execute the following statement to create a credential that will be used by the Database Engine:
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 ServerIf 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:Create a group on the CipherTrust Manager.
Add the user,
<cipherTrust_manager_tde_user>
, to the group.Add the group to the Asymmetric Key.
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:
The CipherTrust Manager Activity Log does not show any error.
Execute the following statement to add a login used by TDE, and add the new credential to the login:
Creating a Symmetric Database Encryption Key
The DEK will encrypt your database. To create the DEK: Change to the database that will be encrypted:
Execute the following code to create the database encryption key that will be used for TDE:
For example:
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
Execute the following to alter the database to enable TDE:
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:
To disable encryption, execute the following:
Verifying the Tables Encrypted Using TDE
To verify the tables which are encrypted with DEK, execute the following:
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:
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:
Create an asymmetric key protected by CAKM for Microsoft SQL Server EKM provider.
Create a credential protected by the asymmetric key.
Regenerate a DEK by executing the following SQL statements: