SQL Server TDE Encryption and Query Performance

— By Lori Brown  @SQLSupahStah

I recently was tasked with finding out how implementing TDE on a database along with encrypting several columns in a heavily used table was going to affect application code and query performance. Here are the steps I took to implement TDE encryption along with some query run statistics. I also have the steps needed to restore a TDE encrypted database to another SQL instance.

I will make another blog post that contains the steps for column encryption with query performance so stay tuned for that.

Encrypting a database at rest using TDE

TDE (Transparent Data Encryption) is only available in Enterprise Edition and is enabled at the database level. Turning on TDE for a database will encrypt the database at the file level including tempdb and is often described as protecting the database at rest. So what does “at rest” mean? It means that the database files at the IO level including the backups are encrypted so that if a thief took a backup and tried to restore it they should be unable to if they do not have the master key. The same goes for detaching, copying the database and transaction log files and attaching them to another server.

While many companies are reluctant to encrypt because of the potential changes that may have to be made to application code, TDE as the name states is transparent. With TDE enabled if a user has permission to access the database then they will still be able to access data without any change to code. Notice that a master key and certificate are created as part of the process of enabling TDE encryption. It is also extremely important to back these up and document any passwords associated with them.

Here is how to enable TDE for a database.

1.Create (DMK) database master key, open it up and back it up.

USE master

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘C001P@sswo4d!!’;

GO

OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘C001P@sswo4d!!’

GO

BACKUP MASTER KEY TO FILE = ‘C:\KeyBkup\SQLDMK.dmk’

ENCRYPTION BY PASSWORD = ‘0urDB1sS3cur3#’

GO

2.Use this query to check to make sure that the master key has been set up.

SELECT b.name, a.crypt_type_desc

FROM sys.key_encryptions a

INNER JOIN sys.symmetric_keys b

ON a.key_id = b.symmetric_key_id

WHERE b.name = ‘##MS_DatabaseMasterKey##’;

GO

3.Create and backup a certificate.

CREATE CERTIFICATE TDECert WITH SUBJECT = ‘Cert used for TDE’;

GO

BACKUP CERTIFICATE TDECert

TO FILE = ‘C:\KeyBkup\Cert4TDE.cer’

WITH PRIVATE KEY

(

FILE = ‘C:\KeyBkup\TDEPvtKey.pvk’,

ENCRYPTION BY PASSWORD = ‘*DBA$@r3C00l’

);

GO

4.Create a database encryption key (DEK) encrypted with the certificate created.

USE TDEtest

GO

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM   = AES_256 –AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY

ENCRYPTION BY SERVER CERTIFICATE TDECert

GO

USE master

GO

5.Enable TDE for the database.

ALTER DATABASE TDEtest SET ENCRYPTION ON

GO

6.Use this query to monitor encryption progress.

SELECT db_name(database_id), encryption_state, percent_complete, key_algorithm, key_length

FROM sys.dm_database_encryption_keys

GO

Simple test of performance

So, let’s take a quick look at a how query performance is impacted by TDE with a very simple test. Most shops avoid encrypting data because of fear of how it will impact performance and code. My test server only has 2 processors and the database is on a small RAID5 LUN with no other activity going on. I created a database named TDEtest with a few small tables with indexes and before actually implementing TDE I ran the following query every few seconds and captured perfmon and SQL stats:

SELECT c.companyname, c.contactname, c.address, c.city, c.country,

o.orderdate, o.requireddate, o.shipaddress, o.shipcity, o.shipcountry

FROM Orders o

JOIN Customers c ON (o.custid = c.custid)

WHERE o.shipcountry = ‘USA’

Before TDE is enabled, CPU utilization averaged 22% utilization for the duration of the test. Query elapsed time was about 48 ms.

After TDE is enabled using the steps above and ran the same query as before. CPU utilization averaged 28% utilization for the duration of the test. Query elapsed time was about 253 ms.

So, you can see that there is an increase in CPU utilization and queries might run longer. The good thing is that I did not have to change anything about my query to make it successfully run. On a system that has more processor power and better disks than my small test server, you very well may not notice a large uptick in CPU utilization or query duration but everyone’s mileage will vary in this. I would advise to carefully test performance if you are going to implement TDE encryption.

Restoring a TDE encrypted database

Now that I have TDE enabled, let’s look at how it protects the database from being restored to another SQL instance without the certificate. I took a backup of my TDEtest database and moved the backup file to another server. Since I know I will need them I also moved the backups of the certificate and private key as well.

First I tried restoring without creating a master key or restoring the certificate. It fails with this message:

It can’t find the certificate so I can’t restore the database. Here’s how to do this right….

1.On the second server, create a database master key if you don’t already have one. If you already have one on the second server don’t do this step

CREATE MASTER KEY

ENCRYPTION BY PASSWORD = ‘@n0th3rMKPassw0rd’;

GO

2.Restore the certificate with the private key.

CREATE CERTIFICATE TDECert

FROM FILE = N’V:\SQLBackup\Cert4TDE.cer’

WITH PRIVATE KEY (

FILE = N’V:\SQLBackup\TDEPvtKey.pvk’,

DECRYPTION BY PASSWORD = ‘*DBA$@r3C00l’

);

GO

3.Restore the database.

Cleanup

And finally, here are the steps to disable TDE and cleanup everything if you ever need to.

1.Disable TDE.

USE master

GO

ALTER DATABASE TDEtest SET ENCRYPTION OFF

GO

2.Drop the database encryption key.

USE TDEtest

GO

DROP DATABASE ENCRYPTION KEY

GO

3.Get rid of the certificate and master key.

USE master

GO

DROP CERTIFICATE TDECert

DROP MASTER KEY

GO

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRxSupport@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. We love to talk tech with anyone in our SQL family!