Admin: Securing your data with TDE

As SQL Server has developed over the past few version, security has been a significant area of improved both in the ability to lock down a server and to encrypt your data. In saying that, despite all the obvious benefits its something that I have rarely seen implemented on production systems. My guess is that a lot of legacy systems have been built on previous versions without the fancy features and it would take a significant development effort to introduce it.

However, one feature that can be harnessed and will give you a level of encryption is Transparent Data Encryption (TDE) introduced in SQL2008. The benefits of using this is that, theoretically speaking, it can just be turned on and your data is encrypted. Sounds great doesn't it? Well, it is but its perhaps not quite what you'd expect.

Lets delve into an example:

USE MASTER;
GO
-- we need a master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'BryanRiggs';
GO
-- and a server certificate
CREATE CERTIFICATE TestTDECert WITH SUBJECT = 'A Test TDE Certificate'
GO
-- and this will be our securable DB
CREATE DATABASE MySecureDB
GO
-- and we'll put in some sensitive data
USE MySecureDB
GO
CREATE TABLE dbo.Person
(Nm NVARCHAR(255), Phone NVARCHAR(50))
GO
INSERT INTO dbo.Person SELECT 'Andy', '07733403304'
INSERT INTO dbo.Person SELECT 'Mandy', '07970402401'
INSERT INTO dbo.Person SELECT 'Gandhi', '07652123957'
GO
-- we can obviously view this data
SELECT * FROM dbo.Person
GO
-- now we just need a en encryption key for the securable database
USE MySecureDB
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE TestTDECert
GO
-- look out for this warning

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.

-- now turn on TDE
ALTER DATABASE MySecureDB
SET ENCRYPTION ON
GO
-- but we can still view the data
SELECT * FROM dbo.Person
GO

So whats happened? Well, for me, the main crux of TDE is that it protects your data at the OS level. In other words, if you are connected a database which has TDE turned enabled, you can view the data within the tables in the same way as you can in a database which has TDE turned OFF. However, the value comes in that a malicious user can't steal your data by copying a backup file and restoring it to their local machine.

Lets look at that:



Essentially, because the database is encrypted the new server will not even accept a restore of the database. A similar error will occur if you just try and copy the MDF/LDF files and attach them to another server.

But of course, there are valid reasons why you would want to move your database to a new server and fortunately this is not too much of an arduous process. Remember, the error we got when setting up TDE? Well, that gives us a hint that our certificates should be backed up and its with these backups that we can create a valid database on an alternative server. So lets go through the steps:

-- on the source server, lets backup that certificate
USE MASTER
GO
BACKUP CERTIFICATE TestTDECert TO FILE = 'C:\SQLBackups\TestTDECert.cert'
WITH PRIVATE KEY
   (FILE = 'C:\SQLBackups\EncryptionPrivateKey.key', ENCRYPTION BY PASSWORD = 'SECUREME')
GO
-- now copy this server to the remote server

-- on the remote server
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'S3cur£Me';
GO
-- create the certificate from the copied backup cert
CREATE CERTIFICATE MyTDECert
FROM FILE = 'c:\SQLBackup\TestTDECert.cert'
WITH PRIVATE KEY (FILE = 'C:\SQLBackup\EncryptionPrivateKey.key', DECRYPTION BY PASSWORD = 'SECUREME')
GO
-- now we should be good to go
RESTORE DATABASE MySecureDB
FROM DISK = 'c:\SQLBackup\MySecureDB.bak'
GO

And there you have it. A quick demonstration of TDE and how it helps secure your data. Roy Ernest has written a more thorough post outlining on TDE over at SQL Server Central and this is definitely worth a read.