Configuring a Transparent Data Encryption Database
I have a By Example coming soon on Transparent Data Encryption (TDE), but thought it was worth showing the steps needed to configure TDE on a database. Using TDE gives you a lot of benefits especially if your on pre 2014 and want to have your backups encrypted without using a 3rd party software for the sake of around 1-5% extra cpu usage (going by the reports ive seen, your systems may very).
The Benefits of TDE are:
data and log files are secure at rest – this means if someone comes in and steals your harddrive, they cant just attach those files and have your data, you cant attach a TDE database without having the cert on the instance!
Backups are encrypted, this applies to log shipping as well, if your sending your log files across to a secondary site you might not be happy to have people able to phish and steal you files in transite and restore them (Yes theres IPSec and SSL certs for network traffic but thats for another time!)
It works with Log Shipping, mirroring, Repliation, AlwaysOn AG (Note that you cant use the GUI on TDE db to create an AlwaysOn AG) pretty much everything! which means there isnt much reason to not do it, if you need want some extra protection. for all the HA/DR solutions you will need to have the cert installed on all instances where the databases will be (
The Negatives for TDE are:
Well to start you have that CPU hit, if your instance is already riding the crest of the cpu wave its not going to be a good idea to slam this ontop of it!
TempDB becomes encrypted when you encrypt any database, that means if you ahve other databases on the instance that need to use tempdb everything will need to be encrtpyed and decryped.. which could cause slow downs, (think about your online rebuild times etc they could be effected)
Your lovely compressed backups are not going to be so slim anymore, as with any encrypyion putting it on always add weight so expected them to grow (AdventureWorks2014 goes from 45,664KB to 194.372KB) this could have a lot of implications to your storage planning and should be worked into the planning.
So high level overview over, lets see what we have to do get this up and running.
Configure TDE on AdventureWorks2014
First we need to create a Master Key in the Master database,
-- Create Master Key Use Master go create master key encryption by password = 'Pa$$W0rd' ; go
Next create a certificate that is protected by the Master KEy
-- Create cert for db create certificate TDECert with subject='TDE Test Certificiate' go
Now you’ve made the key its a good time to get it backed up! watching Denny Cherrys “Where Should I Be Encrypting My Data” (http://www.sqlpass.org/summit/2012/Sessions/SessionDetails.aspx?sid=2654) talk he suggest backing it up twice to USB/Disk and handing one copy to your IT manager and one to your HR team. Remember the data files are useless unless you cert for it.. This works for both a person stealing your data and you if you lose it! Denny also mentioned in the talk that if you lose that cert and call up MS, there isn’t anything they can do to help you! so make sure you have it safe..
-- backup cert use master go backup certificate TDECert to file = 'C:\temp\TDECert.cer' with private key ( file='C:\temp\TDECert.pvk' , encryption by password = 'Pa$$w0rd')
Now we create a DEK (Database Encryption Key) for the database and encrypt it with the certification we made before
-- enable tde db USE [AdventureWorks2014] GO create database encryption key with algorithm = AES_128 encryption by server certificate TDECert GO
and finally we Enable TDE on the Database.
alter database AdventureWorks2014 set encryption on go
There we have it, to view that the database is using TDE you can query sys.dm_database_encryption_keys to confirm