SQL Server 2008 : Transparent Data Encryption

• Transparent data encryption (TDE) is a new feature introduced in SQL Server 2008 that allows an entire database to be encrypted. 
• Unlike column-level encryption, in TDE the encryption and decryption of data is performed automatically by the Database Engine, and this is fully transparent to the end user and applications.
• The encryption of a database using TDE helps prevent the unauthorized access of data in the scenario in which physical media or backups have been lost or stolen.
• Transparent data encryption uses a database encryption key (DEK) for encrypting the database.
• The DEK is stored in the database boot record and is secured by a certificate stored in the master database. 
• The database master key is protected by the service master key, which is in turn protected by the Data Protection API. 
• When TDE is enabled on a database, attaching data files to another SQL Server instance or the restoring of a backup to another SQL Server instance is not permitted until the certificate that was used to secure the DEK is available. 

Implementing Transparent Data Encryption:

Implementing TDE for a specific database is accomplished by following these steps:

• Create a master key.
  USE master;
  GO
  
  --Create the master key which is stored in the master database
  CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mystrongpassword$$';
  GO
  
  
• Create or obtain a certificate protected by the master key.
  CREATE CERTIFICATE MyCertificate
  with SUBJECT = 'Certificate stored in Master Db'
  GO
  
  
• Create a database encryption key and protect it by the certificate.
  USE AdventureWorks2008R2
  GO
  CREATE DATABASE ENCRYPTION KEY
  WITH ALGORITHM = AES_256
  ENCRYPTION BY SERVER CERTIFICATE MyCertificate
  GO
  
  
• Configure the database to use encryption.
   ALTER DATABASE AdventureWorks2008R2
  SET ENCRYPTION ON
  GO 
  
  
  After you enable TDE, you might want to monitor the progress of the encryption. This can be done by running the following query:
  
  SELECT DBName = DB_NAME(database_id), encryption_state
  FROM sys.dm_database_encryption_keys ;
  GO 
  
  DBName                           encryption_state
  --------------------                 ----------------------
  tempdb                                   3
  AdventureWorks2008R2        3
  
  
  This query returns the database encryption state. A database encryption state of 2 means that encryption has begun, and an encryption state of 3 indicates that encryption has completed. When the tempdb database and user database you are encrypting reach a state of 3, the entire user database and tempdb database are encrypted.

 

When TDE is enabled for a given database, encryption is applied to a variety of files related to the database, including the following:

• Database Data Files— All data files that contain the database data are encrypted. These files typically have the extension .mdf or .ndf.
• Database Log Files— The transaction log files are encrypted so that no clear text is visible in the files. These files typically have the extension .ldf.
• Database Backups— All database backups, including full, differential, and log, are encrypted.
• Tempdb— If any databases on a server are encrypted with TDE, the tempdb database is also encrypted.