Implementing Transparent Data Encryption (TDE)

In my previous article we have discussed what is TDE, how it works, its pros and cons. In this article Im going to cover how to implement transparent data encryption to your server. Its very simple to implement TDE, below are the steps that needs to be completed to enable TDE.

• Create a master key
• Create or obtain a certificate protected by the master key
• Create a database encryption key and protect it by the certificate
• Set the database to use encryption

Lets discuss on each of this title to create TDE

Create a master key

In this first step Im going to create a master key with the password TDEtesting123. If you are already having a master key in the master database proceed to the next step. Please note that without this master key you wont be able to create a certificate.

USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'TDEtesting123'
GO

Create or obtain a certificate protected by the master key

In this step Im going to create a certificate in master database using the command below. Im going to create a certificate named TDE_testing in master database. Please note that if you are already having a certificate in master database you can skip this step and you can use that certificate name in the consecutive steps.

USE master
GO
CREATE CERTIFICATE TDE_testing WITH SUBJECT = 'TDE Certificate'
GO

If you want to check whether the certificate is created, you can query sys.certificates catalog view. All the certificates in a database can be retrieved using this view, now you should be able to view the created certificate.

Create a database encryption key and protect it by the certificate

In this Im going to create database encryption with the help of certificate created in the previous step. Im going to use AES algorithm for encrypting the database. Im going to use the database TDE_testing

USE TDE_Testing
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE TDE_testing
GO

Set the database to use encryption

We have reached to the final step in TDE, If all the steps above is successful then we need to enable the database to use encryption.

ALTER DATABASE TDE_Testing
SET ENCRYPTION ON
GO

Thats it TDE_Testing database is now encrypted with TDE.

To confirm whether the database is set to encryption you can use the query below, if it returns 1 then the database is encrypted else its not encrypted.

SELECT name,is_encrypted FROM sys.databases
WHERE name='TDE_Testing'

To know the encryption status you can use the query below, Ive used a where condition on encryption_state column for 3, Where 3 represents an encrypted state on the database and transaction logs.

USE TDE_Testing
GO
SELECT DB_NAME(database_id),*
FROM sys.dm_database_encryption_keys
WHERE encryption_state = 3
GO

As I mentioned in my previous article on TDE that tempdb will also be encrypted if any of the database participates in TDE. You can find from the output above that tempdb is also in encrypted state, however when you query in sys.databases you cant find value 1 on column is_encrypted for tempdb database.

Backup Certificate & Private Encryption Key

Ok now its time to backup the certificate created in master because the certificate is very important because without this certificate you cant restore attach the database to any of the SQL Server 2008 or you cant recover the database in case of failure from backup. So Please take a backup of the certificate created in master database and keep it in a safe location.

In the above step 3 (Create a database encryption key and protect it by the certificate) if you create the database encryption key without backing up the certificate you will be thrown with a warning as shown below

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.

So the next most important step is to backup the certificate, lets discuss how to backup the certificate. Im going to backup the certificate TDE_testing created in step 2 and the private key, you can modify the query below to backup your certificate.

USE master
GO
BACKUP CERTIFICATE TDE_testing TO FILE = 'c:\TDE_testing_cert_backup'
WITH PRIVATE KEY ( FILE = 'c:\TDE_testing_key' , 
ENCRYPTION BY PASSWORD = 'TDEtesting123' )
GO

You can find from the below screenshot that the certificate and its private key are backed up successfully. Copy and keep this backup in a safe location.

Conclusion

TDE is a nice security feature available in SQL 2008 and its very easy to implement too. If you enable TDE for a database then its your responsibility to take backup of the certificate created, because without that certificate you cant do anything with the physical files of the database.