Transparent Data Encryption

18 Apr 2021 14070 views 0 minutes to read Contributors

Introduction

 

With the release of SQL Server 2008, Microsoft expanded the database engine’s security capabilities by adding Transparent Data Encryption (TDE), a built-in feature for encrypting data at rest. TDE protects the physical media that hold the data associated with a user database, including the data and log files and any backups or snapshots. Encrypting data at rest can help prevent those with malicious intent from being able to read the data should they manage to access the files.

Transparent Data Encryption (TDE) encrypts the data within the physical files of the database, the 'data at rest'. Without the original encryption certificate and master key, the data cannot be read when the drive is accessed or the physical media is stolen. The data in unencrypted data files can be read by restoring the files to another server. TDE requires planning but can be implemented without changing the database. 

 

The TDE encryption hierarchy

In TDE encryption hierarchy the Windows Data Protection API (DPAPI) sits at the top of the hierarchy and is used to encrypt the service master key (SMK), a symmetric key that resides in the master database. SQL Server creates the SMK the first time the instance is started. You can use the key to encrypt credentials, linked server passwords, and the database master keys (DMKs) residing in different databases.

In the TDE encryption hierarchy, the SMK sits below the DPAPI, and a DMK sits below the SMK. The DMK is a symmetric key, just like you find with column-level encryption. However, with column-level encryption, you create the DMK in the user database where the column data will be encrypted. With TDE, you create the DMK in the master database, even though you’ll be encrypting a user database. SQL Server uses the SMK and a user-supplied password to encrypt the DMK with the 256-bit AES algorithm.

 

The DMK protects the certificate, and the certificate protects the database encryption key (DEK) in the user database. The DEK is specific to TDE and is used to encrypt the data in the user database in which the key resides.

You can skip the DMK and certificate altogether and instead use an Extensible Key Management (EKM) module to secure the DEK. SQL Server 2008 introduced the EKM infrastructure as a way for encryption keys to be stored in hardware outside of the database, essentially integrating the hardware into the encryption stack. That said, the topic of EKM is outside of the scope of this article, but one we might tackle later in this series.

For now, we’ll focus on the TDE encryption hierarchy as it is represented in the figure. From this, we can deduce that to implement TDE on a user database, we must take the following steps:

1. Create the DMK in the master database, if it doesn’t already exist.
2. Create a certificate in the master database for securing the DEK.
3. Create the DEK in the user database to be encrypted.
4. Enable TDE on the user database.

Implementation

Create Master Key

We must first create the master key. It must be created in the master database, so as a precautionary measure I like to begin this statement with the USE MASTER command.

USE Master;

GO

CREATE MASTER KEY ENCRYPTION

BY PASSWORD='InsertStrongPasswordHere';

GO

 

To verify that the DMK has been created, we can query the sys.symmetric_keys catalog view:

SELECT name KeyName,

  symmetric_key_id KeyID,

  key_length KeyLength,

  algorithm_desc KeyAlgorithm

 

FROM sys.symmetric_keys;

Create Certificate protected by master key

 

CREATE CERTIFICATE TDE_Cert

WITH

SUBJECT='Database_Encryption';

GO

  

The certificate’s name is “TDE_Cert” and I gave it a generic subject. Some Database Administrators like to put the name of the actual database that they are going to encrypt in there. It is totally up to you.

After we run the CREATE CERTIFICATE statement, we can verify that the certificate has been created by querying the sys.certificates catalog view:

 

SELECT name CertName,

  certificate_id CertID,

  pvt_key_encryption_type_desc EncryptType,

  issuer_name Issuer

FROM sys.certificates

WHERE issuer_name = 'Database_Encryption';

Create Database Encryption Key

Now, we must utilize our USE command to switch to the database that we wish to encrypt. Then we create a connection or association between the certificate that we just created and the actual database. Then we indicate the type of encryption algorithm we are going to use. In this case it will be AES_256 encryption.

USE

GO

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE TDE_Cert;

 

GO

 

 

Now let’s return to our DEK. Once we’ve created the key, we can verify its existence by querying the sys.dm_database_encryption_keys dynamic management view:

SELECT DB_NAME(database_id) DbName,

  encryption_state EncryptState,

  key_algorithm KeyAlgorithm,

  key_length KeyLength,

  encryptor_type EncryptType

FROM sys.dm_database_encryption_keys;

 

Enable Encryption

Finally, we can enable encryption on our database by using the ALTER DATABASE command.

 ALTER DATABASE

SET ENCRYPTION ON;

GO

 Once the encryption is turned on, depending on the size of the database, it may take some time to complete. You can monitor the status by querying the sys.dm_database_encryption_keys DMV.

Back up the certificate and keys

As already noted, you should back up your certificates and keys, preferably right after you create them. This is also true for the SMK, before you start relying on it to protect your DMKs.

To back up the SMK, you can use a BACKUP SERVICE MASTER KEY statement, as shown in the following example:

Use master;

GO

BACKUP SERVICE MASTER KEY

TO FILE = 'E:\Cert\SvcMasterKey.key'

ENCRYPTION BY PASSWORD = 'pwDD$$1234!';

The statement itself is fairly straightforward. You must provide the full path for the backup file and a password for encrypting the key in that file. One thing to note, however, is that not all the examples in the Microsoft documentation clearly demonstrate that you must provide a full path, including the file name and its extension. Without these, you will receive an error.

It’s also worth noting that the BACKUP SERVICE MASTER KEY statement includes no logic for what to do when the file already exists. If it does exist, you’ll again receive an error.

Backing up the DMK works much the same way, except that you use a BACKUP MASTER KEY statement:

BACKUP MASTER KEY

TO FILE = 'E:\Cert\DbMasterKey.key'

ENCRYPTION BY PASSWORD = 'pw1234!'

Again, you must provide the full path and file name, along with a password. In addition, the file cannot already exist.

Backing up a certificate is a little different because you want to be sure to explicitly back up the private key along with the certificate. For that, you must use a BACKUP CERTIFICATE statement that includes the WITH PRIVATE KEY clause, as shown in the following example:

BACKUP CERTIFICATE TdeCert

TO FILE = ‘'E:\Cert\TdeCert.cer'

WITH PRIVATE KEY(

  FILE = ''E:\Cert\TdeCert.key',

  ENCRYPTION BY PASSWORD = 'pw1234!');

In this case, we’re generating a file for both the certificate and the private key, as well as providing a password for the private key.