Protecting SQL Server Data : TRANSPARENT DATA ENCRYPTION (part 1) - How TDE Works, Considerations when Implementing TDE

12/25/2013 2:18:08 AM

1. How TDE Works

Transparent Data Encryption (TDE) was introduced in SQL Server 2008, as a feature of the Enterprise Edition of that product. The Developer Edition of SQL Server 2008 also offers TDE, but its license limits its use to development and testing only.

As noted above, TDE's specific purpose is to protect data at rest by encrypting the physical files of the database, rather than the data. These physical files include the database file (.mdf), the transaction log file (.ldf) and the backup files (.bak).

The protection of the database files is accomplished through an encryption key hierarchy that exists externally from the database in which TDE has been enabled. The exception to this is the database encryption key, which was introduced to the database encryption key hierarchy specifically to support the TDE feature, and is used to perform the encryption of the database files.

In Figure 1, the key hierarchy, and their required location of each key, is illustrated. The service master key exists at the instance level. The database master key and certificate at the Master database are used to protect the database encryption key that is located at the user database, which is the HomeLending database in our example. The database encryption key is then used to decrypt the database files of the user database.

Figure 1. TDE Encryption Key Hierarchy.

The dependency upon the encryption key hierarchy in the Master database, as well as the instance, prevents the database files from being restored to an instance of SQL Server that does not contain the referenced keys. This level of protection is a great comfort if a backup tape that contains your database backup files were to fall into the wrong hands.

Additionally, the encryption of the backup files prevents the plain text values that are contained within the database being disclosed by opening the backup files using a text editor and scanning its contents.

2. Benefits and Disadvantages of TDE

Comparing TDE to cell-level encryption is a bit like comparing apples to oranges. They are solutions for different challenges. TDE offers general protection to the database while cell-level encryption offers specific protection to data. I would encourage you to consider using TDE in conjunction with other encryption and obfuscation methods, for a layered approach to protection. To determine whether or not TDE should be part of your protection strategy for sensitive data, consider the following benefits and disadvantages.


  • Implementation of TDE does not require any schema modifications.

  • Since the physical data files and not the data itself are encrypted, the primary keys and indexes on the data are unaffected, and so optimal query execution can be maintained.

  • The performance impact on the database is minimal. In their white paper titled "Database Encryption in SQL Server 2008 Enterprise Edition", Microsoft estimates the performance degradation for TDE to be 3–5%, while cell-level encryption is estimated to be 20–28%. Of course, the impact well may vary, depending upon your specific environment, and volume of data.

  • The decryption process is invisible to the end user.


  • Use of TDE renders negligible any benefits to be gained from backup compression, as the backup files will be only minimally compressed. It is not recommended to use these two features together on the same database.

  • TDE does not provide the same granular control, specific to a user or database role, as is offered by cell-level encryption.

  • TDE is available only with SQL Server 2008, Enterprise Edition and so will probably not be available to all installations within your environment.

3. Considerations when Implementing TDE

Prior to implementing TDE, there are several issues to take into consideration, discussed over the following sections.

Master Key Interdependency

The process of implementing TDE involves the creation of a database master key and certificate, or asymmetric key, on the Master database. Only one database master key can be created for a given database so any other user databases that share the instance, and have TDE implemented, will share a dependency upon the Master database master key.

This interdependency increases the importance of performing a backup of the Master database master key to ensure the continued accessibility of the TDE-enabled databases.

Performance Impact on TempDB

When TDE is initially implemented, the physical file of the TempDB system database is also encrypted. Since the TempDB database contains temporary data from the TDE-enabled database, its encryption is required to maintain full protection by this feature; otherwise the information that is temporarily stored in the TempDB database from the TDE enabled databases would be exposed through the physical files of TempDB.

The TempDB database is used by all user and system databases in the instance to store temporary objects, such as temporary tables, cursors and work tables for spooling. It also provides row versioning and the ability to rollback transactions.

Once the TempDB database is encrypted, any reference and use of this database by other databases, regardless of whether they have TDE enabled or not, will require encryption and decryption. While this encryption and decryption of the TempDB database files remains transparent to the user, it does have a minimal performance impact on the entire instance. Microsoft has estimated the entire impact of TDE on a SQL Server instance to be 3–5% depending on the server environment and data volume.

TDE and Decryption

TDE is designed to protect data at rest by encrypting the physical data files rather than the data itself. This level of protection prevents the data and backup files from being opened in a text editor to expose the file's contents.

TDE encryption occurs prior to writing data to disk, and the data is decrypted when it is queried and recalled into memory. This encryption and decryption occurs without any additional coding or data type modifications; thus it's transparency. Once the data is recalled from disk, into memory, it is no longer considered to be at rest. It has become data in transit, which is beyond the scope of this feature. As such, alongside TDE, you should consider applying additional supporting layers of protection to your sensitive data, to ensure complete protection from unauthorized disclosure. For example, you may wish to implement, in addition to TDE, encrypted database connections, cell-level encryption, or one-way encryption. For additional data in transit protection that is required, externally from the database, you may need to consult with, or defer to, your Network Administration team.

Backup and Recovery

As noted previously, TDE prevents the backup files from being opened by a plain text editor. It also limits the recovery of the database backup file to the instance that holds the encryption key hierarchy that was in existence at the time the backup was created.

As illustrated in Figure 1, backup files of databases with TDE enabled are encrypted using a key hierarchy that includes the service master key of the SQL Server instance, the database master key and certificate for the Master database.

Despite this dependency, none of these keys are included with the standard database backup, and must be backed up separately via the following commands:

  • BACKUP SERVICE MASTER KEY to backup of the service master key.

  • BACKUP MASTER KEY to backup of a database master key.

  • BACKUP CERTIFICATE to backup the certificate.

This behavior is one of the security benefits of TDE. In order to restore the encrypted data to another instance of SQL Server, a user needs to recover the service master key backup file, the Master database master key backup file and the Master database certificate private key, prior to recovering the database backup file.

The database encryption key that is created in the user database, in which TDE has been implemented, is included in the standard database backup. It is stored in the boot record of the database file so that it can be accessed and used to decrypt the user database.

When the service master key and database master key are backed up, it is recommended to store their backup files in a separate location from the database files. This separation will ensure continued protection of the encrypted data in the event that the database backup media is stolen or compromised.

TDE and Replication

If the TDE-enabled database is part of a replication setup, the subscribing database must also have TDE implemented. The data that is traveling between the databases will be in plain text and is vulnerable to unauthorized disclosure. A method of encrypting connections, such as secure socket layers (SSL) or Internet protocol security (IPSec), is recommended.

TDE and FileStream Data

The FILESTREAM data type stores large unstructured objects, such as documents and images, in an integrated physical file that is separate from the database file. When TDE is implemented on a user database that contains FILESTREAM data, the filestream files remain unencrypted.
  •  SQL Server 2012 : Isolation Levels (part 2) - Repeatable Read Isolation Level,Snapshot Isolation Level, Isolation Levels in ADO.NET
  •  SQL Server 2012 : Isolation Levels (part 1) - Read Uncommitted Isolation Level, Read Committed Isolation Level
  •  SQL Server 2012 : Local Transaction Support in SQL Server (part 2) - Implicit Transaction Mode, Batch-Scoped Transaction Mode
  •  SQL Server 2012 : Local Transaction Support in SQL Server (part 1) - Explicit Transaction Mode
  •  SQL Server 2012 : What Is a Transaction?
  •  MySQL : The Binary Log - Binary Log Options and Variables
  •  MySQL : The Binary Log - The mysqlbinlog Utility (part 2) - Interpreting Events
  •  MySQL : The Binary Log - The mysqlbinlog Utility (part 1) - Basic Usage
  •  SQL Server 2012 : Exploring SQL CLR - Examining and Managing CLR Types in a Database
  •  SQL Server 2012 : Exploring SQL CLR - Security
    Top 10
    Extending LINQ to Objects : Writing a Single Element Operator (part 2) - Building the RandomElement Operator
    Extending LINQ to Objects : Writing a Single Element Operator (part 1) - Building Our Own Last Operator
    3 Tips for Maintaining Your Cell Phone Battery (part 2) - Discharge Smart, Use Smart
    3 Tips for Maintaining Your Cell Phone Battery (part 1) - Charge Smart
    OPEL MERIVA : Making a grand entrance
    FORD MONDEO 2.0 ECOBOOST : Modern Mondeo
    BMW 650i COUPE : Sexy retooling of BMW's 6-series
    BMW 120d; M135i - Finely tuned
    PHP Tutorials : Storing Images in MySQL with PHP (part 2) - Creating the HTML, Inserting the Image into MySQL
    PHP Tutorials : Storing Images in MySQL with PHP (part 1) - Why store binary files in MySQL using PHP?
    - First look: Apple Watch

    - 3 Tips for Maintaining Your Cell Phone Battery (part 1)

    - 3 Tips for Maintaining Your Cell Phone Battery (part 2)
    - How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 1)

    - How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 2)

    - How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 3)
    Popular Tags
    Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Indesign Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe After Effects Adobe Photoshop Adobe Fireworks Adobe Flash Catalyst Corel Painter X CorelDRAW X5 CorelDraw 10 QuarkXPress 8 windows Phone 7 windows Phone 8 BlackBerry Android Ipad Iphone iOS