Database encryption becomes transparent with SQL Server TDE!

Encrypting your data at rest with SQL Server Transparent Data Encryption!

In the previous article Encryption in your SQL Server backup strategy, the first one in this series of articles dedicated to SQL Server Encryption features, we described how to implement encryption in your backup strategy. In this article, we will describe another encryption feature known with the name of Transparent Data Encryption.

Transparent Data Encryption (TDE) was introduced in SQL Server 2008 to protect data by encrypting it at the I/O level, thus referred to as data-at-rest encryption. Transparent Data Encryption encrypts physical files, both data files (.mdf, .ndf) and log file (.ldf).

Up to SQL Server 2017 Transparent Data Encryption was available, for production environments, only in the Enterprise edition as described here. SQL Server 2019 extends this security feature to Standard and Web editions without limitations as described here.

As the name suggests, Transparent Data Encryption was designed to make the entire encryption process completely transparent to applications accessing the database. TDE uses Advanced Encryption Standard (AES) or Triple DES, data file pages are encrypted at rest and then decrypted as they are read from disk and moved to the buffer pool. This technique eliminates the problems and limitations that arise when an application queries an encrypted database.

Transparent Data Encryption produces encrypted backups by design for the databases in which it is enabled. As described in the previous article, also in this scenario, the backup cannot be restored without the availability of the certificate and related cryptographic keys. It is therefore very important to back up the certificate, used for encryption, to a safe location.

In addition to the encrypted database, the Transparent Data Encryption architecture involves the Operating System and the SQL Server instance. The operating system-level data protection APIs allow you to decrypt the master key located in the master database of the SQL Server instance. The master key is used to encrypt the database master key. The database master key is used to create the certificate in the master database.

Following we will describe the steps to enable TDE on the AdventureWorks2022 sample database.

If not already in place, in the master database, the master key must be created. The master key is a symmetric key used to protect certificate private keys and asymmetric keys. For more details see SQL Server and Database Encryption Keys (Database Engine).

The following T-SQL code creates the master key in the master database.

USE [master];
GO
-- Create a database master key
-- The database master key is a symmetric key that is used to protect the private keys
-- of certificates and asymmetric keys that are present in the database
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SQLProtectionEncryption2023';
GO

After creating the master key, it is necessary to create a certificate to be used for encrypting the database files. To increase the security level, you can create different certificates for the different databases you want to enable TDE.

The following T-SQL code creates the AdventureWorks2022TDECertificate certificate, it is specific for the AdventureWorks2022 database.

USE [master];
GO
-- Create a certificate for transparent data encryption
CREATE CERTIFICATE [AdventureWorks2022TDECertificate]
  WITH SUBJECT = 'Certificate for database transparent data encryption';
GO

After creating the master key and the certificate to be used for encryption, it is a best practice to take a certificate backup immediately and store it in a reserved area. The purpose of backing up the certificate on the file system is to protect the data from any server failure.

Let’s back up the certificate using BACKUP CERTIFICATE command, the name of the certificate for the AdventureWorks2022 database is AdventureWorks2022TDECertificate, the backup will be saved in a secure location on a remote drive. We will also export the private key (.key) file that encrypts the certificate file, everything is protected by a password.

USE [master];
GO
-- pvt_key_last_backup_date = NULL
-- Export the backup certificate to a file
BACKUP CERTIFICATE [AdventureWorks2022TDECertificate]
  TO FILE = 'X:\SQL\DBs\Backup\Certificate\AdventureWorks2022TDECertificate.cert'
  WITH PRIVATE KEY (
                     FILE = 'X:\SQL\DBs\Backup\Certificate\AdventureWorks2022TDECertificate.key'
                     ,ENCRYPTION BY PASSWORD = 'royalbreeze489'
                   );
GO

Let’s move to the AdventureWorks2022 database to create the encryption key using the AdventureWorks2022TDECertificate certificate.

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

We are now able to activate Transparent Data Encryption on the AdventureWorks2022 database. Let’s do it with the following piece of T-SQL code.

-- Enable TDE
-- It is an asynchronous process, no blocking is required!
ALTER DATABASE [AdventureWorks2022] SET ENCRYPTION ON;
GO

The sys.dm_database_encryption_keys returns information about the encryption status of a database and the encryption keys associated with it. It will be interesting to run the DMV on the SQL Server instance hosting the AdventureWorks2022 database to see the result. The first time I ran the DMV after activating Transparent Data Encryption on the AdventureWorks2022 database I expected it to return only one row with the encryption status of that database, but the returned rows were two! The second line was for the system database tempdb. Although TDE is not available for the master, model, and msdb system databases, tempdb is automatically encrypted when a user database enables TDE, however, tempdb cannot be encrypted directly.

The cryptographic status of a database can be queried with the following T-SQL code.

-- Query the DMV sys.dm_database_encryption_keys
SELECT
  DB_NAME(database_id) DBName
  ,encryption_state
  ,encryption_state_desc
  ,percent_complete
  ,*
FROM
  sys.dm_database_encryption_keys;
GO

This article on SQL Server Transparent Data Encryption ends with some considerations about the TDE scan.

To enable TDE on a database, SQL Server must do an encryption scan. The scan reads each page from the data files into the buffer pool and then writes the encrypted pages back out to disk. To give you more control over the encryption scan, SQL Server 2019 introduces TDE scan, which has a suspend and resume syntax (ENCRYPTION SUSPEND and ENCRYPTION RESUME commands). You can pause the scan while the workload on the system is heavy or during business-critical hours and then resume the scan later.

For more details, you can see the official Transparent Data Encryption documentation.

Enjoy TDE!