Overview

This article does not explain how Transparent Database Encryption (TDE) works or much about what it is because this is already covered extensively elsewhere. Not so readily available is practical and pragmatic help on how to actually use it in the real world. This article aims to meet that requirement without giving you a steep learning curve.

Throughout this article I make reference to a HR database as an example. The aim is to make the HR database "data at rest" (xxx.mdf, xxx.ldf, xxx.bak) encrypted so that it is impossible for anyone to read the data without access to the required certificates and keys. Naturally access to the data via SQL Server is protected by normal logins, users, roles and permissions.

In summary TDE will encrypt:

• configured database and transaction log files
• backup files for encrypted databases
• tempdb database files and transaction log files

In addition:

• TDE also means that database backups can only be restored to a server with the correct certificate and keys installed.
• database files can only be attached to a server with the correct certificate and keys installed. It is not possible to install the correct certificate and keys unless they has been backed up from a source server and/or restored to the target server. The backup of the certificate is also separately encrypted.

The beauty of TDE is that although the data in the files is encrypted this is transparent to a database user - the required data encryption and decryption happens automatically.

There are numerous aspects of TDE configuration which need to be protected by passwords or pass phrases. You could set them all to different things if you were a masochist. Remember this article is supposed to be pragmatic and practical so the obvious thing to do is set them all to the same thing. It doesn't matter if only the right people know what the universal TDE password/pass phrase is (I'll just call it the TDE pass phrase from now on). In our HR database example the only people with access to the TDE pass phrase required are HR staff. The actual administration required is all scripted by the DBA Team but a HR representative is required to type in the pass phrase once per HR controlled script.

TDE Administration Scripts

The scripts described below are attached to this article in TDE_SetupAndAdmin.zip. Once you have extracted the files you will need to perform a global search and replace in all the scripts to change all paths and database names to those used at your site. The database name used in the scripts is XX_DATABASE_XX so that is pretty obvious. The paths are potentially more fiddly but still fairly easy to get right. It will help you undertstand the scripts if you have to look through them and make a minor change! SSMS is good at making the same changes across several files as for XX_DATABASE_XX:

• Start SSMS and connect to the required server
• Use Windows Explorer to navigate to the folder where you extracted the scripts in TDE_SetupAndAdmin.zip
• Select all the scripts and press RETURN
• Wait for all the scripts to load into SSMS
• Click in one of the query windows and press CTRL H. Set the parameters to find XX_DATABASE_XX and replace with YourDatabaseName, set the scope to "all open documents", press Replace All
• Say "Neat" out loud and look around nervously to see if anyone noticed you talking to yourself especially that girl you quite like. Oh no she did hear. That CD you burned for her will mean nothing now. Never mind there are plenty of other nice women in your IT department right?

If you want to know in detail how the scripts work the best way is to study the scripts and use BOL for more detail. There are some comments in the scripts and the layout is well structured. An overview is given in this article.

Note (especially 7 and 8 - you have been warned!):

1. AES 128 bit encryption is used for the HR database. AES 256 bit encryption is used for tempdb. The former is configurable but the latter is not.
2. None of these scripts are required for failover unless a new server is required. Setup on each server in advance - use RestoreTDE on the failover server.
3. Some of the scripts need the TDE pass phrase.
4. Each script has a debug flag which can be on (1) or off (0). The SQL commands used are displayed if the flag is on. If the TDE pass phrase is required the debug flag should be off so that the pass phrase is not displayed when the script runs.
5. Some scripts take several minutes to run because they include an encryption or decryption of the entire HR database.
6. If the certificate and keys backup files are lost they can be recovered from backup tape.
7. If the certificate and keys in the TDE backup folders are completely lost (or are not backups for the certificate and keys actually used) it will be impossible to access the database.
8. If the TDE pass phrase is lost it will be impossible to restore the certificate and keys from the backup files that were created using the pass phrase. This means that it will not be possible to move/failover the HR database from one SQL server to another. The database will remain accessible without knowing the pass phrase. If it becomes known that the pass phrase has been lost TDE should be removed from the server and setup again via RemoveTDE and SetupTDE. The setup process will allow a new pass phrase to be defined.

Initial Setup

To setup TDE on the first server use SetupTDE which also backs up the certificate and keys to:

I:\Program Files\MSSQL\MSSQL10.HR\MSSQL\TDE\TDEServerMasterKey.key

I:\Program Files\MSSQL\MSSQL10.HR\MSSQL\TDE\TDEServerCertificate.cer

I:\Program Files\MSSQL\MSSQL10.HR\MSSQL\TDE\TDEServerCertificate.key

At my site we use the I: drive for backups only. This TDE folder should be backed up using the same strategy as for database backups e.g. daily backup to tape.

1) Start SQL Server Management Studio (SSMS), navigate to the HR instance and open the script SetupTDE. Ask the HR representative to decide, record and type in a pass phrase replacing the dummy pass phrase on the line:

set @PassPhrase = '1_HR_Supplied_PassPhrase_For_TDE'

(No peeping please - you can ask the HR representative to scroll the pass phrase out of site.)

The pass phrase should be at least 22 characters long and include one or more of each of upper case, lower case, numeric and special characters. The script file must not be saved and only the HR representative should see the pass phrase. This same pass phrase will also be required to run the scripts RestoreTDE and BackupTDE at any future date.

Also make sure that the debug flag is set to zero on the line:

set @Debug = 0

If the debug flag is set to 1 the SQL used to backup the certificate and keys is displayed and this will include the pass phrase.

If it becomes necessary to run SetupTDE more than once it will fail unless the previous certificate and keys are removed from I:\Program Files\MSSQL\MSSQL10.HR\MSSQL\TDE

2) Change the file permissions to the TDE backup folder so that the backups cannot be accidentally deleted. No users including administrators should have write access to the files. Specifically only the permissions "Read and Execute", "List Folder Contents" and "Read" should be allowed. As an added warning create a file called DO NOT DELETE THESE FILE.txt in the folder including the reasons why. Somebody's butt is lined up to get kicked - make sure it is not yours!

3) Check the status of TDE is correct in SSMS via script StatusOfTDE which generates three result sets. Check that:

• tempdb and HR database have encryption state Encrypted (3).
• there is a server master certificate called TDEServerCertificate in master.sys.certificates.
• the thumbprints match for TDEServerCertificate and the HR database encryptor.

4) To setup on another server copy the certificate and keys from source to target backup folder and then use RestoreTDE in SSMS. Note that the HR representative will need to type in the pass phrase and set the debug flag.

5) Check the status of TDE is correct in SSMS via script StatusOfTDE. In particular the thumbprints should match between the two servers and within each server.

Turning TDE Off and On

It is not usually required to turn TDE off and on but it can be achieved using the scripts TurnOffTDE and TurnOnTDE. The certificate and keys are not deleted from the server and database. It will take several minutes to turn TDE off or on because there is a decryption or encryption phase for the entire database. Always use StatusOfTDE before and after using these scripts to see the effect.

Removing TDE and Restoring

*** UPDATE *** If you need to remove TDE you need to also do what it says here:

http://www.sqlservercentral.com/articles/Security/76141/

It is not usually required to remove TDE but it can be achieved using the script RemoveTDE. It is not necessary to run TurnOffTDE first. The certificate and keys are deleted from the server and database but not from the backup folder (you might need these to restore an old database backup). It will take several minutes to remove TDE because there is a decryption phase for the entire database. Always use StatusOfTDE before and after using this script to see the effect. Note that tempdb will remain encrypted until you restart SQL Server. In other words always restart SQL Server after running RemoveTDE. TDE can be restored again using RestoreTDE. Note that RestoreTDE only restores the certificates and keys - it does not encrypt the database. Once TDE has been restored the database can be encrypted via TurnOnTDE or an old encrypted database backup can be restored (if it was backed up when the same certificate and keys as just restored where operational.

Conclusion

We have seen how we can setup and administer TDE using a set to scripts to make the process pragmatic and practical. Some guidance is given to make sure that the TDE certificates and keys are backed up to tape similarly to the database backups themselves.