Setting Up Transparent Data Encryption in SQL Server

Introduction

This is geared more toward DBA's than developers, but developers need to know how this works. Transparent Data Encryption or TDE is a feature of Microsoft SQL Server in version 2008 and up that allows you to encrypt the data and log files of your SQL server with no special operations in your transactions at all. If you have used column level encryption with symmetric or asymmetric keys then you know what a blessing this is. Everything happens behind the scenes and your files and backups are safe from data theft with very little work at all on your part.

The What

TDE protects your data at rest at the database level. What this means is your backups and data and log files for the encrypted database as well as tempDB are encrypted and protected in most cases with a symmetric key called a DEK (Database Encryption Key) that is stored in the database and protected with a certificate in the Master database secured with the service master key of the server. Sometimes an asymmetric key stored in an EKM module is used instead of a certificate but we're going to look at symmetric key TDE in this BLOG.

The multi-layer encryption scheme SQL Server uses for protecting data is a complex one to say the least.  Here we're just going to look at how to get it rolling quickly. Below is the diagram of the TDE hierarchy.

TDE is set on a per database basis. There are a few gotcha's that need to be considered when enabling TDE on a database.

• Encrypted data is NOT compressible. Read that carefully! Row and Page level data compression is still possible since this happens before TDE does its thing. However, your nice little compressed backups that have been saving your space on the NAS...you can kiss those goodbye. Make sure you take this into consideration when capacity planning so you don't run out of space.
• Once encrypted, the whole database is inaccessible without the certificate and key. BACK THIS UP IMMEDIATELY and put it somewhere safe! The same protection that keeps data thieves from stealing your data and log files and attaching them or restoring your backups will also prevent you from accessing the data. You have been warned.
• You can't encrypt a read-only database or a database with any read-only file groups.
• Encryption happens in the background once you enable TDE on the database. Until it has finished, most maintenance operations on the database are not allowed. Review the link to the MSDN at the bottom of the article for a complete listing of restrictions on this.
• Filestream data is not encrypted.
• Replication data is unencrypted before publishing. You will have to enable TDE on each member of the replication team individually.
• TDE is available on Enterprise and higher level editions of SQL server, Development and Evaluation editions starting with version 2008.  They have recently added TDE to Azure but on your local servers you're going to pay the price of taking the easy road. 
• There is a bit of a CPU increase when using TDE obviously so if you're flirting with the 85% usage mark on your server it's time to add some cores before stepping into this.

The How

Okay! Let's take a look at what it takes to enable TDE on a database. Just a hint here, the scripts I give you are fill in the blank kind of things that you can save for a quick script to get it done in most cases. You can save these to file or refer back to the blog for a quick select and copy. I know I am always stealing code from some of Peter's and Daniel's blogs to save typing time.

-- Step 1 -- Create a Master Key
USE [master]
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyR3a11yL0ng&c0mpl3xP@$$20rd!'

-- Step 2 -- Create the server certificate
CREATE CERTIFICATE [CERTIFICATENAMEHERE] 
WITH SUBJECT = 'DATABASENAMEHERE TDE'

-- Step 3 -- Create the database encryption key
USE [DATABASEIMENCRYPTING]
GO 
CREATE DATABASE ENCRYPTION KEY 
 WITH ALGORITHM = AES_256 --<--Your choice here, but anything less is uncivilized!
ENCRYPTION BY SERVER CERTIFICATE [CERTIFICATENAMEHERE]
GO 

-- Step 4 -- Backup the key and certificate NOW!!!!
USE [master] 
GO 
-- NOTE! You must use seperate locations for the keys and certs.
BACKUP CERTIFICATE [CERTIFICATENAMEHERE]  
TO FILE = '\\UNC PATH TO MY BACKUP REPOSITORY\certs\[CERTIFICATENAMEHERE].cer' 
WITH PRIVATE KEY (FILE = '\\UNC PATH TO MY BACKUP REPOSITORY\Keys\[CERTIFICATENAMEHERE].pvk' , 
ENCRYPTION BY PASSWORD = 'An0th3rR3a11yL0ng&c0mpl3xP@$$20rd!' ) 
GO 

-- Step 5 -- Enable TDE on the database
ALTER DATABASE [DATABASEIMENCRYPTING]
SET ENCRYPTION ON

-- Step 6 -- Check the progress
USE [master] 
GO
--This will show you the status of the encryption operation going on in the background.
--The database is fully online while this is happening. Neat huh!?
SELECT 
    db.name,
    db.is_encrypted,
    dm.encryption_state,
    dm.percent_complete,
    dm.key_algorithm,
    dm.key_length
FROM sys.databases db
LEFT OUTER JOIN sys.dm_database_encryption_keys dm
 ON db.database_id = dm.database_id;
GO

That was easy enough huh!? But what if I want to move the database to another server? There is a procedure for this as well, I'll throw them all into a handy script block as well but here is the procedure.

Moving an encrypted database

• Create a database master key on the new server. This doesn't have to use the same password.
• Recreate the certificate using the backup you did when encrypting the database. You did back it up? Right??? The password is the same one that you used when backing up the cert originally! 
• Attach the database files from their location or restore the backup, whichever method you are using.
• Test access to the data.
• Here is the handy script for doing these operations and a few more you might need at some point.

-- Drop the database encryption key. REMOVE ENCRYPTION FIRST!!!!
USE [MYENCRYPTEDDATABASE]
GO
DROP DATABASE ENCRYPTION KEY

-- Drop certificate
USE [MASTER]
GO
DROP CERTIFICATE [CERTIFICATENAMEHERE]

-- Drop Master Key
USE [MASTER]
GO
DROP MASTER KEY 

Conclusion

That's it for our short lesson on TDE. I hope this has at the very least given you some clarity on the subject. Maybe it's motivated you to give TDE a shot! I know I love it. Encryption and security is an art form in itself and you never can learn too much about it or do to much to protect your server and your data.

References

TDE MSDN Documentation: https://msdn.microsoft.com/en-us/library/bb934049.aspx