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(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
      

  6. 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




  7. We now have all the prerequisites for enabling TDE so we can now enable database encryption

    ALTER DATABASE TDE SET ENCRYPTION ON

  8. 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_idAS DatabaseName,
                
    e.database_id
    ,
                
    e.encryption_state
    ,
        
    CASE 
    e.encryption_state
                    
    WHEN THEN 
    'No database encryption key present, no encryption'
                    
    WHEN THEN 
    'Unencrypted'
                    
    WHEN THEN 
    'Encryption in progress'
                    
    WHEN THEN 
    'Encrypted'
                    
    WHEN THEN 
    'Key change in progress'
                    
    WHEN THEN 
    'Decryption in progress'
        
    END AS encryption_state_desc
    ,
                
    c.name
    ,
                
    e.percent_complete
        
    FROM sys.dm_database_encryption_keys AS 

        
    LEFT JOIN master.sys.certificates AS 

        
    ON e.encryptor_thumbprint c.thumbprint

  9. 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




  10. 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

Attachment: TDE.zip
Published 24 June 2008 15:38 by sqldbatips
Filed under: , ,

Comments

# New in SQL 2008 : Transparent Data Encryption Part II - sqldbatips.com blog

Pingback from  New in SQL 2008 : Transparent Data Encryption Part II - sqldbatips.com blog

24 June 2008 21:55 by sqldbatips.com blog

# Demo code from UK SQL 2008 Launch Event

The code for the demos from the UK SQL 2008 Launch Event are now available from the link below. Given

25 June 2008 11:20 by SQL Server e dintorni

# Transparent Data Encryption

L’infrastruttura di cifratura basata su PKI è stata introdotta in SQL Server 2005 (a questo link trovate

# Transparent Data Encryption in SQL Server 2008 | Brent Ozar - SQL Server DBA

Pingback from  Transparent Data Encryption in SQL Server 2008 | Brent Ozar - SQL Server DBA

# RealTime - Questions: "Encrypt plain text passwords already in database?"

Pingback from  RealTime - Questions: "Encrypt plain text passwords already in database?"