— 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.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘C001P@sswo4d!!’;
OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘C001P@sswo4d!!’
BACKUP MASTER KEY TO FILE = ‘C:\KeyBkup\SQLDMK.dmk’
ENCRYPTION BY PASSWORD = ‘0urDB1sS3cur3#’
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##’;
3.Create and backup a certificate.
CREATE CERTIFICATE TDECert WITH SUBJECT = ‘Cert used for TDE’;
BACKUP CERTIFICATE TDECert
TO FILE = ‘C:\KeyBkup\Cert4TDE.cer’
WITH PRIVATE KEY
FILE = ‘C:\KeyBkup\TDEPvtKey.pvk’,
ENCRYPTION BY PASSWORD = ‘*DBA$@r3C00l’
4.Create a database encryption key (DEK) encrypted with the certificate created.
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256 –AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY
ENCRYPTION BY SERVER CERTIFICATE TDECert
5.Enable TDE for the database.
ALTER DATABASE TDEtest SET ENCRYPTION ON
6.Use this query to monitor encryption progress.
SELECT db_name(database_id), encryption_state, percent_complete, key_algorithm, key_length
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’;
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’
3.Restore the database.
And finally, here are the steps to disable TDE and cleanup everything if you ever need to.
ALTER DATABASE TDEtest SET ENCRYPTION OFF
2.Drop the database encryption key.
DROP DATABASE ENCRYPTION KEY
3.Get rid of the certificate and master key.
DROP CERTIFICATE TDECert
DROP MASTER KEY
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!
Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.