Transparent Data Encryption in SQL Server 2008
Posted by decipherinfosys on July 24, 2008
Transparent Data Encryption (TDE) is another new feature in SQL Server 2008 which implements database level encryption. SQL Server 2005 already had the column level encryption and with TDE we can now prevent scenarios where the backups or the physical media (tape drive) which contains sensitive data is stolen and then someone reads it by restoring the backups or re-attaching the database files. This encryption is done at the page level. The data that is read off the disk is decrypted when reading the data and encrypted when writing it to the disk. The encryption is done using a database encryption key also called as DEK which is protected using a certificate that is stored on the master database of the instance.
One thing to note (and something that we had not realized when initially working with TDE) is that if any user database on an instance uses TDE, then the tempdb system database will also be encrypted. We have not done benchmarks yet to see the performance penalty of this but wanted to point this out so that you are aware of it. The column level encryption which was introduced in SQL Server 2005 offers more granularity but TDE can be useful as well since not only does it protect your back-ups, it allows the ability to search encrypted data.
Let’s create a new database and then a table in it that has credit card information, SSN information etc.:
create database decipher_test
create table dbo.test_encryption
set nocount on
insert into dbo.test_encryption values (‘Joe’, ‘Snyder’, ‘234423456’, ‘1234567890123456’);
Now, let’s take a backup of this database. After taking the back-up, if we open it up using Notepad, we can easily read this data – see the image below:
Please do note that we searched for “J o e” since this is unicode. The whole point of doing this exercise was to show that the native backups are readable. One can also easily restore this database on another instance and take all the SSN and Credit Card related information. There are third party tools out there that allow for the encryption as well for the backups but that is a discussion for another post. When this functionality is already built in SQL Server 2008, why should we invest in another third party tool.
So, how do we go about using TDE? We first need to create a database master key:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘supersecret!Nahhhh’;
Next, we will create a certificate to protect the DEK:
CREATE CERTIFICATE DCTEST WITH SUBJECT = ‘DEK DCTEST CERTIFICATE’;
And now, we can set the encryption for the DECIPHER_TEST database by creating the DEK:
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE DCTEST;
We used 128 bit AES as our encryption algorithm but you can choose from others as well. Those are listed in BOL and you can also access them in the online version over here.
The last step is to bring it on! (Sorry – turn it on!):
ALTER DATABASE DECIPHER_TEST SET ENCRYPTION ON;
And now, if you try to take a backup and open it up with notepad, you will see that the data is not readable. This time, let us try to restore this encrypted database. If I try to restore this on another instance on which the certificate does not exist, it will fail with error message:
“Cannot find server certificate with thumbprint…”
In order to be able to restore that backup on another instance, we will need to backup the certificate as well:
BACKUP CERTIFICATE DCTEST
TO FILE = ‘E:\DCTEST.cer’
WITH PRIVATE KEY (FILE = ‘E:\DCTEST_Key.pvk’ ,
ENCRYPTION BY PASSWORD = ‘supersecret!Nahhhh’ )
The next step is to then copy the certificate to the other instance, create the master encryption key just like we did above (with a different password):
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘gotit!Nahhhh’;
And now, we can simply import the certificate that we copied over:
CREATE CERTIFICATE DCTEST
FROM FILE = ‘E:\DCTEST.cer’
WITH PRIVATE KEY (FILE = ‘E:\DCTEST_Key.pvk’ ,
DECRYPTION BY PASSWORD = ‘supersecret!Nahhhh’ );
Instead of “TO FILE”, we used “FROM FILE” and instead of “ENCRYPTION”, we used “DECRYPTION” in order for the import to go through. After the certificate has been created on the other instance, the restore will go through fine and we can look at the data the same was as on the source system.
There is one more feature along the same lines that needs to be mentioned here. Besides using a certificate in the master database to protect the DEK, we can also use what is called as the EKM (Extensible Key Management) feature. EKM allows us to store the keys used to encrypt the data separately as compared to the data that we are protecting. This is made possible by exposing the encryption functionality to the hardware vendors that address EKM using Hardware Security Modules (HSM). More and more acronyms, eh! We will discuss EKM and HSM in a future post.