Transparent Data Encryption (TDE) is a very cool feature of SQL Server that has been with us since SQL Server 2008 but has been underused. “It sounds great, but what is it?” I hear you ask.

Transparent Data Encryption is one of the several built-in data protection technologies available to you. It protects your data “at rest”. WTF? (Why the face? Thanks Phil Dunphy!) This means that your database (note the granularity here) is protected on the disk. So, if someone breaks in and steals your drive(s), they won’t be able to access the data in the .mdf, .ndf or .ldf files without the correct keys. Yay! It’s transparent otherwise (hence the “T” in TDE).

There are some huge benefits here for you. The SQL Server engine handles all the encryption and decryption work. Phew! It makes use of the AES and 3DES encryption algorithms and the encryption and decryption is run on background threads. There are no application level changes required. As a bonus, your backups are protected by TDE. Awesome!

“I want to do this NOW!” Cool your jets! Here are a few simple steps to take in order to achieve this.

Firstly, you’ll need to create a Service Master Key (SMK).

1
2
3

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘some lame password';

Before the SMK can be used to protect any databases, you’ll need to create an encryption certificate. It’s the certificate that actually protects the Database Encryption Key (DEK).

1
2
3

USE master;
GO
CREATE CERTIFICATE tdeCert WITH SUBJECT = ‘TDE Certificate’

Now, and this next step is absolutely THE MOST IMPORTANT STEP! DO NOT PROCEED BEYOND THIS POINT UNTIL YOU HAVE DONE THIS BIT! Backup the certificate! If you lose this certificate make sure you have your resignation letter handy… you won’t be able to restore, attach or detach any databases without it.

1
2
3
4
5
6

USE master;
GO
BACKUP CERTIFICATE tdeCert TO FILE = ‘path_to_file’
WITH PRIVATE KEY (
FILE = ‘path_to_private_key_file’,
ENCRYPTION BY PASSWORD = ‘cert password’);

Okay… once you are 100% sure that you’ve backed up the certificate, you can enable encryption on your user database. You’ll need to create a DEK (Database Encryption Key) in each user database you want to encrypt.

1
2
3
4
5

USE myDatabase;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE tdeCert;

Now everything’s in place…

1

ALTER DATABASE myDatabase SET ENCRYPTION ON;

Voila! You cannot attach the database to a SQL Server instance without having the DMK and certificate from the original instance. You can’t even restore it to another instance since the backup is encrypted. More to the point, it’s not even possible to create an unencrypted backup now (unless you turn encryption off).

Disadvantages of Transparent Data Encryption 

There are a handful of downsides that you should be aware of:

  • TDE is only available in the Enterprise Editions of SQL Server 2008, 2008R2 and 2012.
  • The lowest granularity is the database.
  • The data is not protected from authenticated, authorised database users, including the DBA.
  • Backup compression could potentially increase the size of the backup instead of reducing it.
  • CPU utilisation is higher for the compressed backup than the uncompressed backup since the database engine attempts to compress the encrypted data which is not very compressible.
  • It requires more time to perform a compressed backup on a TDE enabled database than it would be otherwise.

So there are some downsides, notably the fact that it becomes practically pointless to use Transparent Data Encryption with database compression. That’s the dirty little secret - there are third-party options that allow you to do both, and to do them well. However, as we’ve have already gone through, there are some great benefits. As with anything to do with SQL Server, you must weigh the benefits against the drawbacks and come up with the best solution for your situation. If you’d like to get your hands dirty with SQL Server, I would recommend that you take a look at New Horizons’ SQL Server training programs and even consider getting certified in SQL Server to open a whole new window of opportunities.