Working with Transparent Data Encryption in SQL Server

Article
1/17/2024

Introduction

Transparent Data Encryption(TDE) is an encryption mechanism of encrypting the data at rest. The data is encrypted before writing to the database and is decrypted before being read from it. This process happens behind the scene such that the client is unaware about the Encryption/Decryption process, hence the name ‘Transparent’.

Encryption Internals

Transparent Data Encryption is implemented by converting the plain text to a cipher text using an encryption algorithm, in our case AES_256, and a Key. The key acts as the locking mechanism. To decrypt the cipher text, the same algorithm and key must be used which prevents spurious hackers from decrypting the cipher as they don’t know the key used for initial encryption. Thus, the key serves the locking/unlocking mechanism of the encryption algorithm.

The overall steps involved in the implementation of TDE is:

Create a Master key which is a Symmetric Key
Create the Certificate using the Master Key
Create Encryption Key and Use the Certificate to protect the Asymmetric Encryption Key.
Alter the Database to Enable TDE

Create Master Key

As the first step, we must create a Master Key which is a Symmetric Key. It will be later used to create the certificate that protects the Asymmetric Encryption Key. We will be using the ‘Create Master Key’ command to generate the Master Key.

USE master;
GO
  
 
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'password-1';

Create the Certificate

Once the Master Key is in place, we will then use it to create the Certificate. This certificate will finally be used to generate the Asymmetric Encryption Key required encrypt the data.

CREATE CERTIFICATE SelfSignedCert
WITH SUBJECT = 'Self Signed Certificate';

Create Encryption Key

Once the certificate has been created, we will create the final Encryption key that will be used to encrypt the data. The Encryption key in turn will be encrypted using the previously created certificate.

USE DB;
GO
 
 
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE SelfSignedCert;

Set Transparent Data Encryption

As the final step, we will enable the Transparent Data Encryption on the database using the ‘Set Encryption On’ command.

ALTER DATABASE DB
SET ENCRYPTION ON;

Summary

Thus, we have enabled Transparent Data Encryption on the database using the Asymmetric and Symmetric keys. Let’s check the Encryption Status:

SELECT DB_NAME(database_id) DatabaseName,
  encryption_state EncryptionState,
  key_algorithm EncryptionAlgorithm,
  key_length KeyLength,
  encryptor_type EncryptionType
FROM sys.dm_database_encryption_keys;

As we can see there are two databases that are listed. ‘DB’ is the name of the database in use. When we implement TDE on any database the Tempdb will also be encrypted along with it. The encryption State ‘3’ indicates that the DB is in encrypted state. The values of encryption state is given in the below table.

1	Unencrypted
2	Encryption in progress
3	Encrypted

Though the encryption has been implemented the end user wouldn’t feel the encryption in action. We can check the results by running the select query and we will get the results as usual.

Share via