Introduction

Transparent Data Encryption (TDE) is one of the key security features available in SQL Server from SQL Server 2008 onwards. Using this feature, the ‘data at rest’ in the physical files for the database, are protected from unauthorized access if the files are copied, or the physical media is stolen. TDE is available with SQL Server, Azure SQL Database, and Azure Synapse Analytics (SQL DW) data files.

This level will explain how TDE works and cover the steps to implement TDE using certificates and a Database Master Key.

How TDE Works

When data is read from disk, SQL Server decrypts the entire block, making the data visible to the database engine. When data is inserted or updated, the SQL Server database encrypts the entire block written to disk. TDE performs this real-time I/O encryption and decryption of both the data and log files to protect data at rest. The encryption is transparent to any user or application querying the database.

Once TDE is enabled for a user database, then the tempdb database in the instance will also be encrypted, as the database may contain the temporary user objects, internal objects, and row versions which can expose the sensitive data.

The following diagram illustration in Books Online gives a clear-cut idea on the architecture of Transparent Data Encryption using Certificates and the Database Master Key (DMK).

Fig 1 – From the SQL Server documentation

The keys involved in TDE are as follows:

• Service Master Key (SMK) - Created during the first start up of SQL Server after the installation. There can be only one SMK for a SQL Server instance.
• Database Master Key (DMK) - The DMK is a symmetric key that is used to protect the private keys of certificates and asymmetric keys that are present in a database. There can be a DMK in each database.
• Database Encryption Key (DEK) -  The DEK exists in the user database and is used to encrypt and decrypt data in the user database.

The Service Master Key is protected and encrypted in the OS Level by a Local Machine Key, using the DPAPI (Data Protection API). The Local Machine Key used by the DPAPI is derived from the SQL Server Service account and computer’s credentials. The Windows credential of the SQL Server service account and the computer credentials are responsible for creating a key that is used by DPAPI to encrypt the SMK. The SMK can only be decrypted by the service account under which it was created or by a principal that has access to the machine's credentials.

The entire encyption hierarchy starts from the Windows DPAPI(Data Protection API) level which encrypts the Service Master Key (SMK) which further encrypts the Database Master key(DMK). Then, DMK creates a certificate in the master database which encrypts the Database Encryption Key residing in the user database.  The entire user database is protected by the Database Encryption Key(DEK) using Transparent Data Encryption (TDE).

Enabling TDE

To enable TDE for a user database, we create a Database Master Key (DMK), a Certificate, then a Database Encryption Key and turn on the encryption in the user database. The steps below outlines the steps.

Create a  Database Master Key (DMK)

The first step is to create a DMK in the master database. The below DDL command (CREATE MASTER KEY) is used for the DMK creation. The password used in the script should be strong as per your password policy and kept safe . It is required to recover the user database in another server with the same master database restored.

USE master;  
GO  
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@$$w0rd';  
GO
               

Fig 2 - Master Key creation

Create the certificate

A certificate is a digitally signed security object that contains a public (and optionally a private) key for SQL Server. The certificates can be a self signed certificate or externally signed certificate (by a third party Certificate Authority like Verisign, Symantec etc). We cannot create an externally signed certificate with SQL Server but is possible to import an existing externally signed certificate.

The CREATE CERTIFICATE statement can load a certificate from a file, a binary constant, or an assembly. This statement can also generate a key pair and create a self-signed certificate. In this scenario , we are using self signed certificate as an external validation is not quite often used while working with SQL Server.

The below script will create the certificate 'TDETest_Cert' in the master database.

USE master;  
GO
CREATE CERTIFICATE TDETest_Certificate WITH SUBJECT = 'TDETest_Cert',
START_DATE = '2020-02-02',
EXPIRY_DATE = '2020-12-12';
GO

Arguments :

SUBJECT =  Field in the metadata of the certificate as defined in the X.509 standard.

START_DATE = Date on which the certificate becomes valid.

EXPIRY_DATE = Date on which the certificate expires.

Once the script is executed, a certifcate named 'TDETest_Certificate' will be created under the Certificates folder under Security folder in the master database.

Fig 3 - Certificate creation

However, it should be noted that the certificate created above should be backed up to a secure location. If the server ever goes down and if you need to restore the database in another server , you will have to import the certificate to the new server.

Create the Database Encryption Key (DEK)

The DEK is required to be created in the user database before the database can be encrypted by using Transparent Database Encryption (TDE). When a database is transparently encrypted, the whole database is encrypted at the file level, without any special code modifications.

The Certificate created above encrypts the DEK in the user database.

USE [TDETest]
GO
CREATE DATABASE ENCRYPTION KEY  
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDETest_Certificate;
GO

The arguments used in this code are as follows:

• ALGORITHM : This is the encryption algorithm that is used for the encryption key. Beginning with SQL Server 2016, all algorithms other than AES_128, AES_192, and AES_256 are deprecated. Using older algorithms is not recommended, and to use one, you must set the database to database compatibility level 120 or lower.
• ENCRYPTION BY SERVER CERTIFICATE : This is the object name of the encryptor, which encrypts and protects.

Once the script is executed, we will receive the below warning message in Fig 4 stating that the certificate created for encrypting database has to be backed up in a secure location as it is required to restore or recover the TDE enabled database on another server.

Fig 4 - Database Encryption Key creation

Enable Encryption

The final step is to turn on the encryption database option in the database properties. The code shown below will set the encryption state to ON for the user database.

-- Alter the database to enable transparent data encryption.  
ALTER DATABASE AdventureWorks2016
SET ENCRYPTION ON;  
GO  

The encryption progress of the database can be checked by querying the DMV sys.dm_database_encryption_keys  with the below query:

select db_name(database_id) as DBName,encryption_state,percent_complete from  sys.dm_database_encryption_keys

The encryption_state value from the above query will be an integer value that can be interpreted with these values:

• 0 = No database encryption key present, no encryption
• 1 = Unencrypted
• 2 = Encryption in progress
• 3 = Encrypted
• 4 = Key change in progress
• 5 = Decryption in progress
• 6 = Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)

Fig 5 - Turn ON encryption in user database

Once the script execution is complete, the Encryption Enabled property will be changed to True, as shown in the figure below.

Fig 6- Figure showing Encryption Enabled option status

Summary

Transparent Data Encryption is a feature available with SQL Server for  securing the data "at rest". This article explains the steps to be followed for configuring TDE in a user database in SQL Server using a certificate and a Database Master Key.