Transparent Data Encryption in SQL Server 2008, 2008R2, 2012 (Part 2)

If you haven’t read Part 1 of this series then you can read it here. Data encryption allows us to encrypt data at field level. It’s done by the developers either in stored procedures or in in-line T-SQL code.

Transparent Data Encryption (TDE) allows DBAs to encrypt the whole database at SQL instance level. Once TDE is enabled at database level data is transparently encrypted and there’s no noticeable performance overhead. Developers don’t have to write any code on their end to encrypt / decrypt data. TDE encrypts both the database data files and the backups. TDE sort of binds the database to the SQL instance, not to the physical machine, where TDE is enabled.  Which means if someone steals the mdf / ndf / ldf files or the .bak files then it’s pointless since no one can attach / restore these files on any other instance of SQL server.

Before we discuss the technical details for enabling TDE it’s very important to know that both data encryption and TDE secure the data at the physical layer level i.e. hard drive. Data is not encrypted when it’s transmitting over the network. For encrypting the communication / network layer we have to encrypt the connections to the database engine which is primarily done by using configuration manager and certificates. I will leave network / communication level encryption for some other day. For now we are going to encrypt data at the physical layer level so that if the physical files (mdf / ndf / ldf / bak) files are stolen or “misplaced” then they shouldn’t be usable on any other instance / machine.

The overall process for with TDE requires creation of master key, certificates and enabling various TDE related options. Here I will guide you through the whole process. If you are following it then please ensure you perform them on a test / dev machine. DISCLAIMER: If these steps fry your machine, make you bald headed or gets your dog abducted by aliens then I won’t be held responsible.

Step 1:

To work with TDE your first step is to create a master key at the SQL instance level i.e. in the master database. Master key is used for creating certificates at the SQL level.

Step 2:

Create certificate that will be used for enabling TDE. Note: initially certificate is created in the master database and then assigned to a custom database

Step 3:

Now before we proceed with the actual encryption we should take the backup of the certificate and the encryption key right away otherwise if we lose the TDE enabled database then it will be nothing more than virtual paper weight, meaning we can’t decrypt the encrypted database without the right certificate. Notice we are taking the backup of the key and encrypting the backup with a password so that if the key gets stolen then it should not be recoverable without the password.

Step 4:

Now we are going to create the encryption key in our custom database (SalesDB) by using the certificate that was generated at the instance level. Since certificates rely on asymmetric cryptography so it means that one key remains in the instance and second key is in our database. This is what that sort of binds the database to the instance.

Step 5:

Time to flip the switch and turn on TDE at the database level.

Houston we have a lift off. TDE has been enabled. All the applications will be able to access data from the database seamlessly without requiring manual encryption / decryption. To test TDE try these:

1. Try taking the database offline, then copy the mdf / ldf files to some other instance and attach the files. It will tell you that it’s not possible to attach it to some other instance since certificate is not found on that instance.

2. Try taking the backup of the TDE enabled database then restore it on some other instance. Again you will be out of luck and won’t be able to restore since the second instance doesn’t have the certificate that was used for encrypting the data.

Restoring certificate / legitimately copying TDE enabled database to another instance

Say that there was a disaster and the whole SQL instance, where TDE enabled database was residing, is lost. You will end up installing SQL server and then you will want to restore your TDE enabled database. Or, say that you want to copy the TDE enabled database, legitimately, to some other SQL server instance. How do we accomplish that? Well for that we have to ensure that the certificate that was used to encrypt the database exists at the instance level. Here are the steps that will be required for successful restoration / copying of database on some other instance.

Step 1:

We have to restore the certificate that was backed up previously.

And that should do. Now you should be able attach your TDE enabled mdf / ldf or restore your .bak file

TDE is not for me. Get me out of here

If you don’t like TDE or if you want to disable TDE at the database level then run the following script:

TDE Pros & Cons

Pros:

1. Encrypts the whole database and sort of binds it to the instance.
2. There’s almost no performance overhead.
3. Encryption / decryption is seamlessly done by SQL server. No manual coding is required.

Cons:

1. It doesn’t prevent “man in the middle” attacks. Data is not encrypted when it’s getting transmitted over the network.
2. Requires Enterprise edition
3. Requires discipline on behalf of DBA. Certificate backup is mandatory otherwise without it there’s no way to recover the TDE enabled database.
4. Older hardware might notice some performance overhead since encryption / decryption is taking place in the background.