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.
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.
Benefits
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.
Disadvantages
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.
