Transparent Data Encryption FAQs

If nothing else, the recent security scandals about the NSA and its PRISM surveillance program have raised everyone’s awareness about the importance of data protection. Databases are some of an organization’s most important assets, and they often contain sensitive information.

SQL Server has several built-in data protection technologies, and one of the most important is Transparent Data Encryption (TDE). Here are some of the most common FAQs about TDE.

Q: What is TDE and why would I want to use it?

A: TDE was first introduced with SQL Server 2008, and it protects your data at rest by performing real-time I/O encryption and decryption of a SQL Server database’s data and log files. One of the biggest benefits of TDE is that the SQL Server engine handles all of the encryption and decryption work.

TDE uses the AES and 3DES encryption algorithms, and the encryption and decryption operations are run on background threads by SQL Server. No application changes are required to take advantage of TDE. Backups of databases protected by TDE are also encrypted.

Q: What version of SQL Server supports TDE?

A: TDE requires SQL Server 2012 Enterprise edition. It’s not available in SQL Server 2012 Standard or Business Intelligence editions. TDE is also available in SQL Server 2008 and SQL Server 2008 R2 Datacenter and Enterprise editions.

Q: Does TDE prevent the security administrator or database administrator from seeing the data in the database?

A: No. TDE protects the data at rest, but an authorized user such as a security administrator or a database administrator can access the data in a TDE-encrypted database. To prevent an SA or DBA from accessing selected parts of the data, you need to use application-level encryption.

Q: How do you enable TDE?

A: To use TDE, first you need to create a master key using the CREATE MASTER KEY T-SQL statement. Next, you need to create a certificate using the CREATE CERTIFICATE command.

Then you need to use the CREATE DATABASE ENCRYPTION KEY statement to encrypt the Database Encryption Key (DEK).  Finally, you need to use the ALTERDATABASE SET ENCRYPTION ON statement to actually encrypt the user database with the DEK. As soon as you use TDE for a user database, TempDB is automatically encrypted as well.

Q: Is there a performance impact for using TDE?

A: Yes, some performance overhead is involved in using TDE. The encryption and decryption process do require additional CPU cycles. The overhead for using TDE ranges from about 3 percent to 30 percent, depending on the type of workload.

SQL Server instances with low I/O and low CPU usage will have the least performance impact. Servers with high CPU usage will have the most performance impact.

Q: Does TDE encrypt the data stream sent to the client?

A: No. TDE encrypts the data stored on disk at the page and log level. The data that’s sent across the wire to the client connections isn’t encrypted.

If you want to encrypt the link between SQL Server and the client systems, then you need to use Secure Sockets Layer (SSL). You can find out more information about how to use SSL at Encrypting Connections to SQL Server.

Q: Where can I find out more about TDE?

A: You can check into Using Transparent Data Encryption on the SQL Server Pro website. You can also find out more from the Microsoft Transparent Data Encryption (TDE) page on TechNet.