TDE was introduced in SQL Server 2008 as an Enterprise/Developer edition feature.This feature allows to encrypt the whole database and includes database backup’s.

I was interested to test TDE in Denali to understand if there are any major changes for encryption,and with the release of SQL Server 2012 RC 0 I decided to do this testing.

This blog post deals with TDE on SQL Server 2012.

Note  – TDE has performance overheads,hence proper testing is required before its implemented to production environments.

A detailed explanation of  TDE can be found under this Microsoft Article.

I decided to go with this pattern where I will first create a Master Key followed by creating a certificate which will be protected by the Master Key.Create a Database Encryption Key and protect it with the certificate and then turn on encryption for the database.

–Create a Master Key and this will reside inside Master Database

USE [master]
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD='Test@123Test'
GO

–Create a Certificate and protect it with Master Key

USE [master]
CREATE CERTIFICATE TDECertificate WITH SUBJECT = 'SQL SRVR TDE Certificate' 
GO

If we query the sys.certificates table we can see that the Certificate is already encrypted with Master Key

SELECT name,pvt_key_encryption_type_desc FROM sys.certificates where [name] = 'TDECertificate'
GO

name                                     pvt_key_encryption_type_desc

——-                                     —————————————-
TDECertificate                  ENCRYPTED_BY_MASTER_KEY

The next step is to create a  Database Encryption Key for a database.For this testing purpose I had already created a database called TestEncryption.

–Create Database Encryption Key

Use [TestEncryption] 
GO 
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 
ENCRYPTION BY SERVER CERTIFICATE TDECertificate 
GO

As soon as I fire this command to create the DEK,I got the message

Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.

This message is really important and clearly gives us an idea of how important a Certificate and a private key is.I fired the backup of the same using

--Backup the Certificate and the private key
BACKUP CERTIFICATE TDECertificate 
TO FILE = 'C:\Certificate Backup\TDECertificate.cert' 
WITH PRIVATE KEY ( FILE = 'C:\Certificate Backup\EncryptionPrivateKey.key', ENCRYPTION BY PASSWORD = 'PrVTKeyyyPa55W0ord') 
GO

The certificate and Private key was backed up successfully to my local disk.

–Turn on Encryption for the test database TestEncryption

ALTER DATABASE TestEncryption SET ENCRYPTION ON

Now my database TestEncryption is encrypted!

As the database is now encrypted the best test which I would like to conduct is backup and restore to a different server.

--Backup the database
BACKUP DATABASE [TestEncryption]
TO DISK='C:\Test_Backups\TestEncryption_After_Encryption.bak'
GO

Processed 280 pages for database ‘TestEncryption’, file ‘TestEncryption’ on file 1.
Processed 2 pages for database ‘TestEncryption’, file ‘TestEncryption_log’ on file 1.
BACKUP DATABASE successfully processed 282 pages in 0.309 seconds (7.106 MB/sec).

As the backup is completed I went ahead to do a restore in another Instance which is there on the same server.An expected error came right away

—Restore the database to Instance B

RESTORE DATABASE [TestEncryption]
FROM DISK='C:\Test_Backups\TestEncryption_After_Encryption.bak'
GO

Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint ‘0xC94A1B6784EA5EAE8C34C7DF2775997AD504E4FB’.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

What does this mean ? As the second instance doesn’t got a certificate,its impossible to restore the database and the solution is to create a certificate from the backup which we had taken earlier.

–Create the certificate on Instance B

USE master
CREATE CERTIFICATE TDECertificate 
FROM FILE = 'C:\Certificate Backup\TDECertificate.cert' 
WITH PRIVATE KEY (FILE = 'C:\Certificate Backup\EncryptionPrivateKey.key', 
DECRYPTION BY PASSWORD = 'TDEtesting123')
GO

 

Msg 15465, Level 16, State 6, Line 2
The private key password is invalid.

Wow! What is that error now?   – For this testing purpose,I had given a wrong password for the Private key for decryption and SQL Server picked it up and straight away gave me the error.

I changed the password and was successful next time

–Create the certificate on Instance B after correcting the password

USE master
CREATE CERTIFICATE TDECertificate 
FROM FILE = 'C:\Certificate Backup\TDECertificate.cert' 
WITH PRIVATE KEY (FILE = 'C:\Certificate Backup\EncryptionPrivateKey.key', 
DECRYPTION BY PASSWORD = 'PrVTKeyyyPa55W0ord')
GO

Command(s) completed successfully.

We have the certificate available now and the restore should work well,Lets try that out

–Restore database to Instance B

RESTORE DATABASE [TestEncryption]
FROM DISK='C:\Test_Backups\TestEncryption_After_Encryption.bak'
WITH MOVE 'TestEncryption' TO 'C:\SQL_Data\Testencrptiontest.mdf',
MOVE 'TestEncryption_log' TO 'C:\SQL_Log\Testencrptiontest.ldf'
GO

Processed 280 pages for database ‘TestEncryption’, file ‘TestEncryption’ on file 1.
Processed 2 pages for database ‘TestEncryption’, file ‘TestEncryption_log’ on file 1.
RESTORE DATABASE successfully processed 282 pages in 0.290 seconds (7.571 MB/sec).

Phew!!! That’s some serious security isnt? Great! Now even if some one stole our backups,they just cant afford to restore it.

Again Note  – TDE has performance overheads and it should be not used without proper study and requirements,considerations.

With TDE its hard time for hackers  to break this tight security wall!

Conclusion – I could not find any major changes in TDE for SQL Server 2012 RC 0,it behaved all the same like 2008 and R2.

Looking forward to hear from all you people and thanks for reading.