Transparent Data Encryption

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.



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