Transparent Data Encryption on SQL Server

Transparent data encryption is a fairly new option that is available in SQL Server 2008 onwards. It has the ability to protect databases from opportunist access, this covers the following;

  • an opportunist user who takes a copy of the backup file and restores it to another server.
  • an opportunist user who takes copies of the database files and restores them to another server.

TDE protects against both these scenarios. Now, there are some very good 3rd party products such as Redgate's SQL Backup Pro and Quest's Litespeed; they both offer the ability to combine compression with full encryption up to 256bit, something that native compression and TDE don't combine very well. However, this would still leave the actual database files vulnerable.

Implementing TDE also brings with it a whole set of extra tasks to consider. You have the added overhead of managing passwords and certificates and securing backups of all your certificates. Also, what happens if you have to send the database out to your vendor and it's TDE protected?

Looking at some key points (this is by no means an exhaustive list) 

What doesn't TDE do? 

  • does not encrypt data at the object level, users can still see the data via queries, etc.
  • does not support instant file initialisation for database files.
  • does not interract well with backup compression.
  • does not encrypt read only filegroups.
  • does not encrypt databases used in replication topologies.
  • does not encrypt filestream data.

What does TDE do? 

  • does encrypt the data "At rest", the actual files themselves are encrypted via a symmetric database encryption key usually protected by an asymmetric key or a certificate.
  • does encrypt database files (data and log) and transaction log records for log shipped and mirrored databases.
  • may affect performance for non encrypted databases on the same instance due to addition of the encrypted TempDB.

Yes, that's correct, for TDE to fully protect any given database it also has to encrypt the TEMPDB too. Before implementing TDE you should carefully weigh up the options and the "pros and cons". If backup compression is paramount TDE will not be a good partner, look at the NTFS ACLs instead as these are used to secure the raw files.

Something I tend to see quite a lot, are posts from users requesting information on how to move their TDE protected database to a new server, the reply has nearly always been, "backup your master key and restore it on the new server". This is simply untrue!

The SQL Server instance has an encryption key called the Service master key and sits at the top of the SQL Server encryption hierarchy. Please see the following link for details of the SQL Server encryption hierarchy:

This is the principal under which all objects are secured, linked servers are one example. The key is generated the first time its required, more can be found at this link:

Encryption is applied using the following crptographic sequence;

  • The Service master key is used to protect the database master key (although you can change this behaviour).
  • The database master key is the key we use for Transparent Data Encryption, this key is stored in the master database.
  • This master key is then used to protect any certificates that we store in the master database.
  • A database encryption key is then created in your TDE database and is bound by the server certificate.

It's important to understand that the certificate used to secure the database encryption key used for TDE has no direct dependency on the service or database master keys, you do not need to backup and restore either of these keys when moving your TDE protected database to a new server. You do, however, require a backup of the certificate to create a matching cert on the new server.

Implementing TDE involves the following steps; 

  1. If you haven't already, create the database master key and store the password securely.
  2. Create a server certificate in the master database and take a backup of this certificate.
  3. Create a database encryption key in your database you wish to enable for TDE
  4. Set the encryption option on.
  5. Check the encryption state using the following query

select DB_NAME(database_id), encryption_state, percent_complete from sys.dm_database_encryption_keys

Moving your TDE protected database to a new server involves the following steps; 

  1. You've already completed the steps above
  2. If you haven't already, create the database master key on your target server and store the password securely.
  3. Create a server certificate from the backup of the certificate on the source server.
  4. Restore the TDE protected database to the target server.

TDE can provide great benefit when protecting database files\backups, there are restrictions so choose carefully whether to implement this technology. There are also possible performance ramifications for encrypted databases due to the encrypted TEMPDB and the realtime I/O encryption applied to the protected database(s). Like most options, they're not for everyone and require careful thought and planning, just be sure the effort provides sufficient benefit to the business.