Using Transparent Data Encryption

Securing sensitive data is a critical concern for organizations of all types and sizes. Credit card numbers, medical and health records, and other personal information must be stored and secured in such a way that only authorized personnel are able to access the information. Before SQL Server 2008, this type of encryption needed to be handled by the application. Managing the encryption keys was a manual process, and accessing the encrypted data required writing application code. SQL Server 2008 addressed these issues by bringing the ability to encrypt data into the database engine itself with its Transparent Data Encryption (TDE) technology. TDE is essentially an enterprise-oriented feature and is available only in the following editions of SQL Server:

Related: Securing Credit Card Data Through SQL Server 2008

Related: Database Encryption Solutions

Understanding TDE

TDE provides the ability to encrypt an entire database and to have the encryption be completely transparent to the applications that access the database. TDE encrypts the data stored in both the database's data file (.mdf) and log file (.ldf) using either Advanced Encryption Standard (AES) or Triple DES (3DES) encryption. In addition, any backups of the database are encrypted. This protects the data while it's at rest as well as provides protection against losing sensitive information if the backup media were lost or stolen.

Like data compression, TDE database encryption is performed at the page level. Data is encrypted on the disk and is decrypted as it's read into memory. Performing the encryption at the page level enables the encryption process to be completely transparent to the client applications. There are no limitations on the ability to search or query the data in the encrypted database. In addition, since most database applications are optimized to minimize I/O for performance reasons, tying the encryption process to the database engine takes advantage of the existing application optimization and makes the encryption process very efficient. If the database is being used with AlwaysOn Availability Groups, database mirroring, or log shipping, the databases on all of the participating systems will be encrypted. It's important to note that although TDE encrypts the stored data, it doesn't encrypt the communications link between the server and the client applications. If you need to encrypt the data connection between the application and the server, you need to use an SSL connection for the clients. Other technologies such as database mirroring and AlwaysOn Availability Groups support network transport encryption in the properties of their endpoints.

Encrypting a Database

Using TDE is relatively straightforward. To encrypt a database, follow these steps:

1. Create a master key for the database.

2. Create a certificate that's protected by the master key.

3. Create a special key that's used to protect the database. This key is called the database encryption key (DEK) and you secure it using the certificate.

4. Enable encryption.

The code in Listing 1 demonstrates how to enable TDE on a sample database named MyDatabase. Using this code as a reference, let's take a closer look at these steps.

-- The master key must be in the master database.USE Master;GO-- Create the master key.CREATE MASTER KEY ENCRYPTIONBY PASSWORD='MyStrongPassword';GO-- Create a certificate.CREATE CERTIFICATE MySQLCertWITH SUBJECT='MyDatabase DEK';GO-- Use the database to enable TDE.USE MyDatabaseGO -- Associate the certificate to MyDatabase.CREATE DATABASE ENCRYPTION KEYWITH ALGORITHM = AES_128ENCRYPTION BY SERVER CERTIFICATE MySQLCert;GO-- Encrypt the database.ALTER DATABASE MyDatabaseSET ENCRYPTION ON;GO

To enable TDE, you must first create a master key. It's important to note that the master key must be created in the master database. At the top of Listing 1, you can see where the USE statement changes the current database to the master database. The CREATE MASTER KEY statement is then used to create a master key along with an associated password in the master database.

Next, the CREATE CERTIFICATE statement is used to create a new certificate named MySQLCert. After the certificate is created, the USE statement switches the current database to the MyDatabase database, where the CREATE DATABASE ENCRYPTION KEY statement is used to create a new DEK with the AES encryption algorithm. You can choose from one of the following encryption algorithms: AES_128, AES_192, AES_256, or TRIPLE_DES_3KEY.

After the database encryption key is created, the ALTER DATABASE command is used to enable encryption, as shown near the bottom of Listing 1. The encryption process runs as a background task, so the database remains available while it's being encrypted. However, there's a performance impact during this time. It's important to note that if any database on a server is encrypted using TDE, the tempdb database will also be encrypted.

Restoring and Moving Encrypted Databases

Backing up the certificate is extremely important because the encrypted data can't be accessed or restored without the certificate. Likewise, if you need to move an encrypted database from one SQL Server instance to another server, you also need to move the certificate. The code in Listing 2 shows how to back up the certificate to a file in the file system. This file can then be backed up or copied to another system to allow the encrypted database to be restored.

USE MasterGOBACKUP CERTIFICATE MySQLCertTO FILE = 'C:tempMySQLCert'WITH PRIVATE KEY (file='C:tempMySQLCertKey',ENCRYPTION BY PASSWORD='MyStrongPassword2')

Because the master key is in the master database, the code in Listing 2 begins by applying the USE statement to switch to the master database. It then uses the BACKUP CERTIFICATE statement to copy the certificate to the C:tempMySQLCert file and the key to the C:tempMySQLCertKey file.

To move or restore the database to another SQL Server instance, you need to restore the certificate to the new server instance. Listing 3 demonstrates how to move a backed up certificate to a new SQL Server instance. The USE statement switches the current database to the master database, which is where the new master key must be created. Next, the CREATE MASTER KEY ENCRYPTION statement is executed to create a new master key. The new master key has a strong password associated as well. The CREATE CERTIFICATE statement is used to create a new certificate named MySQLCert. This statement is different from the one used in Listing 1, because this statement uses the FROM FILE clause to point to the backed up certificate that was created with the code in Listing 2. The WITH PRIVATE KEY clause points to the backup of the decryption key file. Finally, the password to decrypt the certificate is supplied using the DECRYPTION BY PASSWORD clause.

USE MasterGO-- Create a new master key.CREATE MASTER KEY ENCRYPTIONBY PASSWORD = 'MyNewStrongPassword' -- Restore the certificate.CREATE CERTIFICATE MySQLCertFROM FILE='c:tempMySQLCert'WITH PRIVATE KEY (FILE = 'c:tempMySQLCertKey',DECRYPTION BY PASSWORD='MyStrongPassword2')

Monitoring TDE

You can monitor TDE with the sys.certificates catalog view and the sys.dm_database_encryption_keys dynamic management view (DMV). Listing 4 shows how to query sys.certificates to see the certificates that have been created. It also shows how to query sys.dm_database_encryption_keys to see the databases that are encrypted.

USE masterGOSELECT * FROM sys.certificates-- encryption_state = 3 is encryptedSELECT * FROM sys.dm_database_encryption_keys  WHERE encryption_state = 3;

TDE Exposed

TDE makes it easy to encrypt the sensitive data in your SQL Server databases -- and that data is also encrypted in any database backups you perform. Better yet, TDE provides this added security without requiring any application code changes.

Related: Securing Your SQL Server Environment