Transparent data encryption (TDE) encrypts SQL Server data files. This encryption is known as encrypting data at rest. The encryption uses a database encryption key (DEK). The database boot record stores the key for availability during recovery. The DEK is a symmetric key, and is secured by a certificate that the server's master database stores or by an asymmetric key that an EKM module protects.

TDE protects data at rest, which is the data and log files. It lets you follow many laws, regulations, and guidelines established in various industries. This ability lets software developers encrypt data by using AES and 3DES encryption algorithms without changing existing applications.

Install the Akeyless EKM provider

1. Download and run the official Akeyless EKM provider:

curl https://akeylessservices.s3.us-east-2.amazonaws.com/services/akeyless-crypto-provider/release/latest/AkeylessEkmProviderInstaller.msi --output AkeylessEkmProviderInstaller.msi

Follow the wizard installation steps, enter your Akeyless Gateway URL on port 8081 when prompted, and choose a path in Akeyless platform to store the keys.

Choose the OS installation path and save it for later. This will copy the dll files, and also creates a configuration file that can be edited later.

Configure the Akeyless EKM provider

Open Microsoft SQL Server Management Studio, and run the SQL commands below to complete the installation.

1. Enable the EKM provider on the MSSQL server:

USE master;
GO
sp_configure 'show advanced', 1
GO
RECONFIGURE
GO
sp_configure 'EKM provider enabled', 1
GO
RECONFIGURE
GO

2. Create the EKM provider named Akeyless using the dll file from the installation folder:

CREATE CRYPTOGRAPHIC PROVIDER Akeyless
FROM FILE = 'C:\Program Files\Akeyless\Akeyless Ekm Provider\AkeylessEkm.dll'

3. Create a SQL CREDENTIAL that will be used by the system administrators to access Akeyless from the SQL server, for example using an API Key which is stored inside a SQL CREDENTIAL named akeyless_tde

CREATE CREDENTIAL akeyless_tde
WITH IDENTITY = '<ACCESS_ID>', SECRET = '<ACCESS_KEY>'
FOR CRYPTOGRAPHIC PROVIDER Akeyless ;
GO

• For instance, if you wish to utilize'azure ad authentication'you will need to modify the configuration file located in the installation directory at'C:\Program Files\Akeyless\Akeyless Ekm Provider\sqlcrypt.conf' Specifically, add the following lines: '[auth] access_type="azure_ad" object_id="..." # optional'

4. Add the credential to a privileged user, in the following example replace the [DOMAIN\login] with your privileged username format and add the SQL CREDENTIAL:

ALTER LOGIN [DOMAIN\login]
ADD CREDENTIAL akeyless_tde;
GO

5. Create an asymmetric key for the EKM provider. This will create a key in Akeyless named SQL_Server_Key. To work with an existing key add theCREATION_DISPOSITION = OPEN_EXISTING. The following algorithms are supported: RSA_2048, RSA_3072, or RSA_4096:

CREATE ASYMMETRIC KEY akls_ekm_login_key
FROM PROVIDER Akeyless
WITH ALGORITHM = RSA_2048,
PROVIDER_KEY_NAME = 'SQL_Server_Key'
GO

📘

Working on Cluster

When working with cluster, the above command should be executed only on the Primary server, on all other servers run the following statement:

CREATE ASYMMETRIC KEY akls_ekm_login_key FROM PROVIDER Akeyless WITH PROVIDER_KEY_NAME = 'SQL_Server_Key' , CREATION_DISPOSITION=OPEN_EXISTING;

6. Create another SQL credential that the database engine (TDE) will use:

CREATE CREDENTIAL akls_ekm_tde_cred
WITH IDENTITY = '<ACCESS_ID>', SECRET = '<ACCESS_KEY>'
FOR CRYPTOGRAPHIC PROVIDER Akeyless ;  
GO

7. Create a login that will be used by the database engine (TDE) using the key that we created, and add the new credential to the login.

CREATE LOGIN akls_EKM_Login
FROM ASYMMETRIC KEY akls_ekm_login_key ;
GO  

ALTER LOGIN akls_EKM_Login
ADD CREDENTIAL akls_ekm_tde_cred ;
GO  

8. Create the database encryption key that will be used for TDE. In the following example AdventureWorks is a placeholder for the database name. Supported algorithms are AES_128 or AES_256.

USE [AdventureWorks] ;
GO  
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM  = AES_128  
ENCRYPTION BY SERVER ASYMMETRIC KEY akls_ekm_login_key ;
GO  

Note: This doesn’t create a new key in the Akeyless platform, the key is created inside the database and encrypted by using the key from Akeyless.

9. Alter the database to enable transparent data encryption.

ALTER DATABASE [AdventureWorks]
SET ENCRYPTION ON ;
GO