SQL Server 2008: Transparent data encryption feature – a quick overview

I have kept silent on this feature while it was being developed, but as it has now been publicly advertised in various ways (being mentioned here, here, here, and here, for example), I think it is probably time to write a bit about it. Given that my posts so far have covered SQL Server 2005, I’ll point out again that this is a SQL Server 2008 feature!

The transparent data encryption feature in the next SQL Server version is the last feature I worked on while I was in the SQL Server security team, before my move to MSR. Transparent data encryption is quite a long name, so I’ll just refer to it as TDE from now on.

TDE addresses two of the encryption requests we have received most often from forum questions and from discussions with customers:

(1) a way to enable data encryption without affecting applications that consumed that data (for an idea of what problems are raised when encrypting a table column in a database, see this post)
(2) a way to encrypt an entire database

TDE addresses both these requests by providing a database level encryption feature. Both the data and the log files of a database are encrypted on disk and get decrypted when they are read into memory. This makes encryption really transparent to applications. Because database applications attempt to minimize their I/O (a costly operation), tying encryption and decryption to I/O will also take advantage of existing application design optimizations to minimize the performance impact of encryption.

It is important to note that TDE should not be perceived as a replacement of the encryption solutions shipped with SQL Server 2005; instead, TDE should be viewed as a complementary feature providing encryption at a coarser granularity level. Whereas the encryption features introduced in SQL Server 2005 provide a way to encrypt individual pieces of data (something we refer to as cell-level encryption, because it offers finer granularity than column level encryption), the new TDE feature enables encryption at database level. Also, from a cryptographic point of view, the algorithms used in TDE have also been available with the cell-level encryption, so the two features offer the same encryption strength.

The encryption of a database with TDE is done using a special key called, simply, database encryption key (or DEK, for short). The DEK can be managed using standard DDL (CREATE/ALTER/DROP) and the actual state of the encryption can be turned on or off using the ALTER DATABASE statement and a new clause: SET ENCRYPTION [ON | OFF]. Information about DEKs can be gathered from a DMV called sys.dm_database_encryption_keys. If you like using a GUI for database operations, all these features are also exposed from within Management Studio. I prefer typing SQL queries when it comes to managing a database, which is the reason why in my posts you’ll find more details about DDL and system catalogs than about menus and dialog options.

If you have a CTP build, you can quickly check for the presence of the TDE feature by issuing the following statement: alter database <db_name> set encryption on. A syntax error will mean that the feature is not present, while a more specific error message will indicate that the feature is available – note that the statement should result in no state changes anyway, as long as you have not used TDE before. As of today, to my knowledge, only the latest CTP build includes this feature.

Comments (4)

  1. lcris says:

    I just noticed a new article in Books Online that provides more details about this feature:


  2. lcris says:

    And here’s a link to a whitepaper about TDE:


  3. is a DDL on a data object allowed while encryption is "on"? says:

    While the DDL executes, an update lock is taken on the database.  So, with the encyption scan taking a shared lock, would altering a table cause conflict?  Can alter table be done while encyption is "on"?

  4. lcris says:

    Turning on or off the encryption doesn't interfere with the normal operation of a database. This is why it is called "transparent".