Transparent Data Encryption In SQL Server

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, it is AES_256 and a key. The key acts as the locking mechanism. To decrypt 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 are given below.

• 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, which 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, which is required to 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, which 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 Asymmetric and Symmetric keys. Let’s check Encryption status given below.

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 are given in the table below.

1. Unencrypted
   
2. Encryption in progress
   
3. Encrypted
   

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