Sergey Nivens - Fotolia

Manage Learn to apply best practices and optimize your operations.

Use Transparent Data Encryption to protect data at rest

Transparent Data Encryption (TDE) is an effective tool for encrypting data at rest. An expert walks you through the features and future of TDE.

SQL Server Enterprise provides Transparent Data Encryption (TDE), an encryption technology built into the database engine for protecting confidential information at the database level. Unlike cell-level encryption, which encrypts the data itself, TDE encrypts the physical database files, preventing unauthorized users from being able to attach or restore a TDE-protected database to another server and then accessing the data.

Compliance and privacy requirements make protecting data more critical than ever. With TDE, administrators can encrypt data at the database level to protect all data at rest, without needing to modify the schema or T-SQL statements accessing the data.

Transparent Data Encryption uses Advanced Encryption Standard and Triple Data Encryption Standard algorithms to protect data files (.mdf), transaction log files (.ldf) and backup files (.bak). The TDE encryption and decryption processes are transparent to the applications and users connecting to the database. You don't need to grant special permissions to the accounts outside the normal required authorization.

TDE performs real-time I/O encryption and decryption at the page level. The database engine transparently encrypts the pages before writing them to disk and decrypts them when reading the data into memory, without requiring any special coding or data type modifications.

In a TDE-protected database, a symmetric database encryption key (DEK) is stored in the database's boot record. To secure the DEK, you must use a certificate created in the master database and protected by the master key, or use an asymmetric key stored in an extensible key management (EKM) module. Either approach creates a key hierarchy that prevents database files from being accessed on a SQL Server instance that does not contain the certificate or asymmetric key.

What all this implies is that you must have the correct certificate or key to restore or attach a database. For that reason, whenever you use TDE to encrypt databases, you should also back up any certificates or keys needed to unlock those databases. Otherwise, you might not be able to access the data if a disaster occurs.

The process of using TDE to encrypt a database is fairly straightforward if you're using a certificate:

  1. Create a master key
  2. Set up the certificate
  3. Create the DEK
  4. Protect it with the certificate
  5. Enable TDE on the database

If you plan to use an asymmetric key in an EKM module to protect the DEK, the process is a bit more complex. For details about using EKM, see "Enable TDE Using EKM."

The Transparent Data Encryption difference

Microsoft introduced TDE in SQL Server 2008 Enterprise. Before that, SQL Server supported only cell-level encryption, which targets specific columns of data. Cell-level encryption provides a more granular approach to encryption because you can control exactly what data to encrypt.

On the other hand, cell-level encryption comes with a certain amount of overhead. For example, with cell-level encryption, you can encrypt only varbinary columns, which means you will likely have to create additional columns or modify existing one. In addition, you must specifically encrypt the data when inserting it into the column, and explicitly decrypt and convert the data when retrieving it, which means existing queries have to be modified.

Cell-level encryption also comes with performance penalties because the encryption limits the query engine's ability to use indexes and optimization techniques effectively.

TDE is much simpler to implement. You do not have to create special columns or modify code. The database engine handles all the encryption and decryption operations, which are transparent to the users and applications. Although TDE can affect performance, that effect is nowhere as significant as with cell-level encryption because TDE is applied at the file level, not the data level.

However, performance can still be a consideration because TDE requires additional CPU cycles to encrypt and decrypt the data pages. The higher the I/O rates, the greater the potential effect on performance, especially with large amounts of data. In addition, if TDE is implemented on any database on a SQL Server instance, the database engine also encrypts the tempdb database, which can affect the performance of all databases on that instance.

Transparent Data Encryption moves to the cloud

Microsoft recently made TDE available as a preview feature in Azure SQL Database. Like its SQL Server Enterprise counterpart, SQL Database TDE provides transparent encryption at the database level. However, because of its preview status, SQL Database TDE is not intended for use in production databases until the feature has been officially promoted to general availability.

SQL Database TDE performs real-time encryption and decryption of the database as well as its backups and transaction logs, without requiring modification to the calling applications. You can configure TDE through the Azure portal or by using PowerShell or a RESTful API, although you must first sign up for the TDE preview feature.

SQL Database uses a DEK to encrypt the database and a built-in server certificate to protect the DEK. Each SQL Database server uses a unique certificate that SQL Database creates automatically and stores in the master database. In addition, Azure rotates the certificates at least every 90 days. To move a database with TDE enabled, you must decrypt the database and then re-crypt it after it is moved.

Transparent Data Encryption: One piece of the puzzle

SQL Server TDE protects only data at rest. Data transmitted over the network or held in memory is no longer considered at rest and consequently is no longer protected. To fully protect your databases and their data, you should use TDE in conjunction with other technologies, such as SSL connections or cell-level encryption.

Even so, when it comes to encrypting data at rest, TDE can be an effective tool. You can easily implement encryption at the database level without changing your applications or database schemas. Although performance can be an issue with TDE, the effect is not as great as with cell-level encryption. And even if TDE does affect performance, the ability to protect sensitive data is often worth the trade-off. If protecting data is essential to your operations, then TDE should be one of your top considerations.

Next Steps

Learn about another SQL Server security tool, row-level security for Azure SQL databases

Check out the new encryption option in SQL Server 2016, Always Encrypted

Dig Deeper on SQL Server Security