Implementing Transparent Database Encryption

Last time out we looked at the concepts behind implementing Transparent Database Encryption (TDE) on a SQL Server database. This time we’re going to go through a worked exampled of how to implement this practically so we’ll get into some scripting. For this example I’m using a database named ‘Blog’ on my local instance.

First things first

The first step before we can get started is to create a master key for our environment. This will be used to encrypt the certificates. This operation runs against the master database and only requires a password so it’ll look something like this:

USE master;
GO

CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'Str0ngPassw0rdG0esHere';
GO

With that in place we want to create a certificate which we can use to encrypt one or more of our databases. We’ll have a name for the certificate as well as a description for it (called Subject). This will again be ran against the master database so it’ll look a little like this:

USE master;
GO

CREATE CERTIFICATE MyFirstCertificate
WITH SUBJECT = 'My very first certificate - how special';
GO

With those two elements set up we’re now ready to start encrypting some data.

Lets get encrypting

So lets look at how we get to encrypting the database. There are two steps to this – the first is to create an encryption key for the database and the next is to actually enable and encrypt the actual data in the database.

To start we’ll create a database encryption key targeted specifically at our database and using the certificate we’ve just created:

USE Blog;
GO

CREATE DATABASE ENCRYPTION KEY  
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MyFirstCertificate;  
GO

You may notice that when you create the encryption key that you get a warning about backing up the certificate – we’ll come back to that:

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.

Once that’s in place, we’re now able to apply the encryption to the database which is as straight forward as turning it on!

ALTER DATABASE [Blog]
SET ENCRYPTION ON;  
GO

Once the encryption has been enabled the database isn’t immediately encrypted. The database engine must go through the files and rewrite them to disk. You can check the state of this using the DMV sys.dm_database_encryption_keys which will show us some of the encryption details for the database:

SELECT
	db_name(database_id) [Database],
	c.name [Certificate],
	dek.encryption_state,
	dek.encryption_state_desc,
	dek.percent_complete
FROM
	master.sys.dm_database_encryption_keys dek
	INNER JOIN master.sys.certificates c
		ON dek.encryptor_thumbprint = c.thumbprint;

Typically in here you’ll either see the encryption state as 0 or 1 (un-encrypted), 2 (encryption in progress) or 3 (encryption complete). You can find more details in the DMV documentation.

Wrap up

That’s all there is to it – we’ve got an encrypted database and we’re ready to go. Not that it’ll change how you interact with the database, its still the same queries you’ll be running, DDL scripts to deploy etc. It’s transparent after all!

This isn’t the end of the story though, we still have that warning to contend with – so next time out we’ll look at backing up and restoring those keys and certificates which we’ve created this time.