Aamir Syed

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

December 19, 2016 by

Introduction and Overview

Transparent Data Encryption 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). TDE 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 TDE, 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 TDE 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.

TDE 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

TDE Hierarchy

Now let’s have a quick overview of the TDE 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 TDE 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.

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.

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.

Enable Encryption

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

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.

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.

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

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 TDE 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.

  • For more information regarding secure connections, please refer to this link
  • For more information regarding moving a TDE Database to another server, please refer to this link
  • If you’d like to investigate column level encryption, please refer to this link
 

Aamir Syed

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
Aamir Syed
Security

About 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

8,577 Views
  • Markus

    Hello. Thank you for this description of the procedure. When trying to execute the CREATE DATABASE ENCRYPTION KEY command, I receive this message and don’t know what to do:

    “Cannot use certificate ‘name’, because its private key is not present or it is not protected by the database master key. SQL Server requires the ability to automatically access the private key of the certificate used for this operation.”

    Do you have any idea?! Thank you.

  • Tom

    When you back up the cert key you back it up with file name TDE_CertKey.pvk, but in your restore script you reference TDECert_key.pvk.

  • Mauricio Zaragoza

    Aamir Syed, I am studying for the 70-473 exam. I am a cloud architect that is dealing with database infrastructure configurations everyday. That is why I am pursuing this certification. Your explanation about this topic is way better than Microsoft’s official documentation. Keep the good work and thanks.