How to configure Transparent Data Encryption (TDE) in SQL Server

Introduction and Overview

Transparent Data Encryption (TDE) was introduced in SQL Server 2008. Its main purpose was to protect data by encrypting the physical files, both the data (mdf) and log (ldf) files (as opposed to the actual data stored within the database). Transparent Data Encryption Encrypts SQL Server, Azure SQL Databases, and Azure SQL Data Warehouse data files.

*This is a quick overview; a more detailed hierarchy will appear later in the article.

This technology was designed to have the entire encryption process be completely transparent to the applications accessing the database. It does this by using either Advanced Encryption Standard (AES), or Triple DES, encrypting the file pages and then decrypted as the information goes into memory. This inhibits limitations from querying the data in an encrypted database. This is essentially real time I/O encryption and decryption and does not increase the size of said database.

Also note, that as a result of Transparent Data Encryption, database backups will also be encrypted. In the event that a backup of the database gets lost or stolen, the culprit will not be able to restore the database without the appropriate certificate, keys and passwords.

Also, the TempDB database will be automatically encrypted. Since the tempdb is used by all user databases (processing/storing temporary objects). You shouldn’t notice much of a difference in how Transparent Data Encryption operates, but this is good to know and often overlooked. What good is an encrypted database if the data placed in TempDB isn’t encrypted?

However, this does not encrypt the data “across the wire” so to speak. If there is a requirement to encrypt data across the network an SSL connection must be implemented on the clients. (For more information regarding this please see this link)

If you’re a DBA there is a very strong chance that you are in charge of securing some very sensitive information.

Transparent Data Encryption Eligible SQL Server Editions

First we must determine the correct version of SQL Server that allows Transparent Data Encryption. I like to call it an expensive feature as it requires Enterprise Editions. It also works with Developer Edition, but of course, this is just for testing and development purposes. When implementing this in a production environment you must have the correct version of SQL Server. I’ve listed the eligible editions below.

SQL 2016 Evaluation, Developer, Enterprise
SQL 2014 Evaluation, Developer, Enterprise
SQL Server 2012 Evaluation, Developer, Enterprise
SQL Server 2008 R2 Datacenter, Evaluation, Developer, Enterprise, Datacenter
SQL Server 2008 Evaluation, Developer, Enterprise

Transparent Data Encryption Hierarchy

Now let’s have a quick overview of the Transparent Data Encryption architecture and hierarchy. First we have the Windows Operating System Level Data Protection API, which decrypts the Service Master Key found in the SQL Server instance level. The Server Master Key is created at the time of the initial SQL Server instance setup. From there we go the database level. The Service Master Key encrypts the database Master Key for the master database. The database master key creates a certificate in the master database. Keep in mind that you must create a backup of this certificate. Not only for environmental refreshes but disaster recovery purposes. Once Transparent Data Encryption is enabled on the database you won’t be able to restore or move it another server unless this same certificate has been installed. Keep good (and secure records) of the certificate and password.

The certificate is then used to enable encryption at the database level, thus creating the database encryption key.

To help visualize this process, please refer to the following diagram:

Implementation

As always I like to do my work in SQL Server Management Studio. So please open up SSMS and log into the server that you will be using.

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;

CREATE MASTER KEY ENCRYPTION

BY PASSWORD='InsertStrongPasswordHere';

Create Certificate protected by master key

Once the master key is created along with the strong password (that you should remember or save in a secure location), we will go ahead and create the actual certificate.

CREATE CERTIFICATE TDE_Cert

WITH

SUBJECT='Database_Encryption';

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.

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 <DB>

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE TDE_Cert;

Enable Encryption

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

ALTER DATABASE <DB>

SET ENCRYPTION ON;

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.

Backup Certificate

It’s important to backup the certificate you created and store it in a secure location. If the server ever goes down and you need to restore it elsewhere, you will have to import the certificate to the server. In certain environments, the DR servers are already stood up and on warm/hot standby, so it’s a good idea to just preemptively import the saved certificate to these servers.

BACKUP CERTIFICATE TDE_Cert

TO FILE = 'C:\temp\TDE_Cert'

WITH PRIVATE KEY (file='C:\temp\TDE_CertKey.pvk',

ENCRYPTION BY PASSWORD='InsertStrongPasswordHere')

Remember to store the certificate in a safe and available locations (not a temporary one like this example).

Restoring a Certificate

In order to restore the certificate, you will once again have to create a service master key on the secondary server.

USE Master;

CREATE MASTER KEY ENCRYPTION

BY PASSWORD='InsertStrongPasswordHere';

Once that is done, you must remember where you backed up the certificate and the encryption/decryption password.

USE MASTER

CREATE CERTIFICATE TDECert

FROM FILE = 'C:\Temp\TDE_Cert'

WITH PRIVATE KEY (FILE = 'C:\TDECert_Key.pvk',

DECRYPTION BY PASSWORD = 'InsertStrongPasswordHere' );

Be mindful of the paths used in this example. You must specify the path that you have stored the certificate and private key. Also keep good and secure records of the encryption passwords.

Once the certificate is restored to the secondary server you may restore a copy of the encrypted database.

Some things to note before applying TDE. There are some drawbacks. Remember that Transparent Data Encryption encrypts the underlying database files including the backups. You can’t just take the files and dump them onto another SQL Server without the appropriate encryption keys and certificates. It does NOT allow for granular user level encryption. If that is the type of encryption you are looking for, you should investigate column level encryption.

Author
Recent Posts

Aamir Syed

Aamir is a SQL Server Database Administrator in the NYC/NJ area (and has recently taken a role as a Database Developer).

He started his IT career in helpdesk world and eventually moved into the networking/systems administrator side of things. After some time, he developed an affection for working with Databases (mainly SQL Server) and has focused his career on that for the past eight years. He has worked for various industries in both large and small environments all with different needs.

SQL Shack has provided him with an opportunity to contribute to a community that has given him so much throughout the years.

View all posts by Aamir Syed