sqldbatips.com blog

New in SQL 2008 : Transparent Data Encryption Part I

Overview

Transparent Data Encryption (TDE) provides a way to encrypt all the data in a database with no changes to applications that access that data. It provides protection for the data at rest by performing encryption as data is written to database files and the transaction log and decryption when it is read from those files. This is done automatically once a database has been encrypted using TDE and no changes need to be made to applications that access the data (hence "Transparent").

A symmetric key called the database encryption key (DEK) is used to perform the encryption/decryption and this is stored in the database itself. This key is secured either with a certificate in the master database or by an asymmetric key protected by an EKM (Extensible Key Management) module. In the example code below we'll use a certificate in the master database to secure the database encryption key.

The following diagram (taken from Books Online) demonstrates the key hierarchy used for encryption and in the example code below we will go through the steps required to enable TDE. It is vitally important to backup and the certificate used to secure the database encryption key. This backup can be used to recreate the certificate on another instance which is required in order to restore or attach an encrypted database.

Key Points

• Encrypts data as it is written to disk
• Decrypts data as it is read from disk
• Encryption/Decryption for data files is performed at the page level
• FILESTREAM data is NOT encrypted
• Enabling encryption on a database does NOT increase its size
• Enabling TDE will prevent instant file initalization which may affect performance during file autogrow
• Enabling encryption on any database automatically encrypts tempdb which may have a performance impact
• Transparent to client applications
• Backups of databases encrypted using TDE are also encrypted
• Works with log shipping and database mirroring
• Encrypted data does not compress as well as unencrypted data so backup compression is not very useful for encrypted databases however data compression can still be used effectively
• You cannot restore or attach an encrypted database to another instance without the certificate used to secure the DEK being present in the master database
• The same server certificate can be used to secure multiple database encryption keys but a database encryption key is only ever associated with one certificate

Example Walkthrough

1. In this example we will create a database called TDE which we will then encrypt using a database encryption key secured with a server certificate. First lets create the database and a table with some simple data and then backup the database.
   
   USE master
   GO
   CREATE DATABASE TDE
   GO
   USE TDE
   GO
   CREATE TABLE dbo.t1(a VARCHAR(100))
   INSERT t1(a) 
   VALUES ('This is some plain text'),('This is some more plain text')
   GO
   BACKUP DATABASE TDE TO DISK = 'c:\TDEUnencrypted.bak' WITH init
   GO  
   
   


2. Now open the backup file in Visual Studio 2008 and search for "This is some plain text". You'll see (as below) that you are able to find the text in the backup file
   
   
   
3. 
   
   Now we need to create our server certificate that will be used to secure our database encryption key. In order to create the server certificate we need to create a database master key in the master database (if it doesn't already exist)
   
   USE master
   GO
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'sd092735kjn$&adsg'
   GO
   BACKUP MASTER KEY TO FILE = 'c:\masterkey' 
       ENCRYPTION BY PASSWORD = 'sfg56565kjn$&adsg213'
   GO  
   


4. We can now create and backup the server certificate that we will use to secure the database encryption key
   
   USE master
   GO
   CREATE CERTIFICATE DEKCert WITH SUBJECT = 'DEK Certificate'
   GO
   BACKUP CERTIFICATE DEKCert TO FILE = 'c:\DEKCert'
       WITH PRIVATE KEY ( FILE = 'c:\DEKCertPrivKey' , 
       ENCRYPTION BY PASSWORD = 'sd092735kjn$&adsg' )
   GO  
   


5. Now that we have our server certificate, we can create the database encryption key for the TDE database
   
   USE TDE
   GO
   CREATE DATABASE ENCRYPTION KEY
   WITH ALGORITHM = AES_128
   ENCRYPTION BY SERVER CERTIFICATE DEKCert
   GO  
   

We could also use SSMS to do this using a new option on the Database context Menu - Manage Database Encryption



This will bring up the Manage Database Encryption dialog enabling the selection of the server certificate or asymmetric key to secure our DEK with





6. We now have all the prerequisites for enabling TDE so we can now enable database encryption
   
   ALTER DATABASE TDE SET ENCRYPTION ON
   


7. By setting encryption on, a background task will start encrypting all the data pages and the log file. Since our TDE database is so small this will take very little time however on a larger database this will take considerably longer. BOL has details of the database maintenance operations that are disallowed when this encryption scan is running. To query the status of the database encryption and its percentage completion we can query the new sys.dm_database_encryption_keys DMV
   
   SELECT DB_NAME(e.database_id) AS DatabaseName,
               e.database_id,
               e.encryption_state,
       CASE e.encryption_state
                   WHEN 0 THEN 'No database encryption key present, no encryption'
                   WHEN 1 THEN 'Unencrypted'
                   WHEN 2 THEN 'Encryption in progress'
                   WHEN 3 THEN 'Encrypted'
                   WHEN 4 THEN 'Key change in progress'
                   WHEN 5 THEN 'Decryption in progress'
       END AS encryption_state_desc,
               c.name,
               e.percent_complete
       FROM sys.dm_database_encryption_keys AS e 
       LEFT JOIN master.sys.certificates AS c 
       ON e.encryptor_thumbprint = c.thumbprint
   


8. As you can see from the results below, the TDE database is already fully encrypted. Notice also that tempdb is in the result set. When TDE is enabled for any user database on an instance, tempdb is automatically encrypted. This can have a performance impact and is important to take into consideration when implementing TDE
   
   
   
   
   
9. Now that our database is encrypted, if we take another backup and open it in Visual Studio 2008 as in step 2 you will no longer be able to find the string "This is some plain text" as the data is encrypted!

In Part II we'll look at how to restore or attach our encrypted database to another instance by creating a server certificate from the backup we took in Step 4

The complete code for this example is attached to this post