Implementing Transparent Data Encryption (TDE) in SQL Server

Introduction 

Transparent Data Encryption (TDE) is a technology employed by  Microsoft to encrypt database content.

"Transparent data encryption (TDE) performs real-time I/O encryption and decryption of the data and log files. The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery. The DEK is a symmetric key secured by using a certificate stored in the master database of the server or an asymmetric key protected by an EKM module. TDE protects data "at rest", meaning the data and log files. It provides the ability to comply with many laws, regulations, and guidelines established in various industries. This enables software developers to encrypt data by using AES and 3DES encryption algorithms without changing existing applications."  MSDN

Implementing Transparent Data Encryption Step by Step

To implement TDE you should follow the following steps:
1.  Create a master key
2.  Create or obtain a certificate protected by the master key
3.  Create a database encryption key and protect it by the certificate
4.  Set the database to use encryption

Step #1 Create a master key.

"The database master key is a symmetric key used to protect the private keys of certificates and asymmetric keys that are present in the database. When it is created, the master key is encrypted by using the AES_256 algorithm and a user-supplied password. To enable the automatic decryption of the master key, a copy of the key is encrypted by using the service master key and stored in both the database and in master. " MSDN

To create a Master key you need to execute the followin Tsql comand.

    *  create database test
      USE master;
      CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'masterkey1';*

Step #2 Validate if  a master key has been created.

 

To validate if the master key has been created you can use the folllowing TSQL statement.

*    USE Master;
    select * from sys.symmetric_keys;

*

 Figure1.  Query result showing Master data key has been created (click image to enlarge)

Step #3.  Create a Database Certificate

"A certificate is a database-level securable that follows the X.509 standard and supports X.509 V1 fields. CREATE
CERTIFICATE can load a certificate from a file or assembly. This statement can also generate a key pair and
create a self-signed certificate. " MSDN

The following script should allow you to create a database certificate
   *   Use Master
      CREATE CERTIFICATE mytestcert
      WITH SUBJECT = 'Mytestcertificate'

*

Step #4.  Validate that a Database Certificate has been created

If you want to check if your database has certificate you can inspect it through Management studio
or query the sys.certificates system tables as shown below.

Figure 2.  A database certificate as shown in management studio (click on the image to enlarge)

Step #5.  Create a Database Encryption Key

"A database encryption key is required before a database can be encrypted by using Transparent Database Encryption (TDE). When a database is transparently encrypted, the whole database is encrypted at the file level, without any special code modifications. The certificate or asymmetric key that is used to encrypt the database encryption key must be located in the master system database." MSDN

To create a database encryption Key You can use the following codes:

*      use test
      CREATE DATABASE ENCRYPTION KEY
      WITH ALGORITHM = AES_256
      ENCRYPTION BY SERVER CERTIFICATE [mytestcert]*

Step #6.  Set the Database to use Encryption

The final step is to run an alter database command to turn on the encryption. To do this you will need to run the following commands
*      ALTER DATABASE test
      SET ENCRYPTION ON;*