Implementing \ Configuring Transparent Data Encryption ( 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 I’m 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 won’t 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 I’m going to create a certificate in master database using the command below. I’m 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 I’m going to create database encryption with the help of certificate created in the previous step. I’m going to use AES algorithm for encrypting the database. I’m 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

That’s 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 it’s not encrypted.

select name,is_encrypted from sys.databases

where name='TDE_Testing'

 

 

To know the encryption status you can use the query below, I’ve 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 can’t find value 1 on column is_encrypted for tempdb database.

Backup Certificate & Private Encryption Key

Ok now it’s time to backup the certificate created in master because the certificate is very important because without this certificate you can’t restore \ attach the database to any of the SQL Server 2008 or you can’t 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. I’m 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 it’s your responsibility to take backup of the certificate created, because without that certificate you can’t do anything with the physical files of the database.