Transparent data encryption (TDE) is a new encryption feature introduced in Microsoft®
SQL Server™ 2008. Unlike previous versions of SQL Server, TDE, as an encryption method,
is a lot easier to implement, and is a lot less resource intensive. Essentially, TDE
encrypts everything within the database.
.gif)
Previous versions of data encryption in SQL Server involved cell-level encryption,
which posed problems, as you had to use a Varbinary column. Scans were difficult,
and searching was awful, if not impossible. Essentially, everything had to be handled
out of process for that column. Because encryption happens at the Buffer Pool level,
all processes are transparent to the user. The encryption happens at file level, not
within the file structure. This allows indexes to work without being hindered
by the use of Varbinary columns. For more information check out the MSDN Whitepaper
on TDE at
http://msdn.microsoft.com/en-us/library/cc278098.aspx (which
is where I stole the pretty graphic).
To enable TDE, you must have the normal permissions associated with creating a database
master key and certificates in the master database. You must also have CONTROL permissions
on the user database.
Perform the following steps in the master database:
1. If it does not already exist, create a database master key (DMK) for the master
database. Ensure that the database master key is encrypted by the service master key
(SMK).
CREATE MASTER KEY ENCRYPTION BY PASSWORD
= ‘some password’;
2. Either create or designate an existing certificate for use as the database encryption
key (DEK) protector. For the best security, it is recommended that you create a new
certificate whose only function is to protect the DEK. Ensure that this certificate
is protected by the DMK.
CREATE CERTIFICATE
tdeCert WITH SUBJECT = ‘TDE Certificate’;
3. Create a backup of the certificate with the private key and store it in a secure
location. (Note that the private key is stored in a separate file—be sure to keep
both files). Be sure to maintain backups of the certificate as data loss may occur
otherwise.
BACKUP CERTIFICATE tdeCert TO FILE =
‘path_to_file’ WITH PRIVATE KEY ( FILE =
‘path_to_private_key_file’, ENCRYPTION BY PASSWORD = ‘cert
password’);
4. Optionally, enable SSL on the server to
protect
data in transit. Perform the following steps in the user database. These require
CONTROL permissions on the database.
5. Create the database encryption key (DEK) encrypted with the certificate designated
from step 2 above. This certificate is referenced as a server certificate to distinguish
it from other certificates that may be stored in the user database.
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM
= AES_256 ENCRYPTION BY SERVER CERTIFICATE tdeCert
6. Enable TDE. This command starts a background thread (referred to as the encryption
scan), which runs asynchronously.
ALTER DATABASE myDatabase SET ENCRYPTION
ON
To monitor progress, query the sys.dm_database_encryption_keys view (the VIEW SERVER
STATE permission is required) as in the following example:
SELECT db_name(database_id),
encryption_state FROM sys.dm_database_encryption_keys
