Enabling Transparent Data Encryption on SQL Server 2014

To start with for Transparent Data Encyrption (TDE) we will need to be using Enterprise (or Developer) edition of SQL Server 2014. I am using SQL Server 2014 Enterprise Edition CTP1, which is the early release of SQL Server 2014.

Next we need a database to work with.  I will just use the Sample Database for Common Table Expressions, a simple database for TDE with just a couple tables. After downloading the script and running it, we can see the following in the Object Exporer.

Encrypting the Database

First lets set up the master key to be used for encryption, then create a certificate for the database we are going to encrypt.

1 2 3 4 5 6 7

USE master; GO CREATE MASTER KEY ENCRYPTION     BY PASSWORD = 'BeSureToUseABetterPasswordThanThis!!!!!!'; GO CREATE CERTIFICATE cteDemoCert   WITH SUBJECT = 'cte_demo Certificate';

The next step is to create the encryption key for the database being encrypted. In this case we are encrypting the cte_demo database.

1 2 3 4 5 6

USE cte_demo; GO   CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = TRIPLE_DES_3KEY ENCRYPTION BY SERVER CERTIFICATE cteDemoCert;

As soon as you run this you will be shown a warning stating that key has not been backed up and that you should back it up.

Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.

Now lets backup the certificate as the warning suggests:

1 2 3

USE master; GO BACKUP CERTIFICATE cteDemoCert TO FILE = 'c:\Backups\cteDemoCert';

And the final step is to turn on TDE encryption for the database. This may take a while for larger databases.

1 2 3

USE cte_demo; GO ALTER DATABASE cte_demo SET ENCRYPTION ON;

And to check to see if everything worked, we can just query the sys.databases table to see if we the cte_demo database is now encrypted.

1 2	SELECT NAME, IS_ENCRYPTED  FROM sys.databases;

From there we can see that the cte_demo database has been encrypted with transparent data encryption. Be sure to make a full database backup at this time and to safely store your backed up encryption key.