Looking into Transparent Data Encryption

In this post I would like to look into Transparent Data Encryption (TDE) works in SQL Server 2008.I would like to stress that this is an SQL Server 2008/R2 Entreprise/Developer edition feature only.

I know there are samples of how to use Transparent Data Encryption in BOL,MSDN and elsewhere but I thought that it would be a good idea to give it a try with hands-on examples.

Transparent Data Encryption does real time encryption/decryption of data and log files.Real time means that encryption happens as the data flows from memory to the disk and decryption happens when data is read from disk to memory.Obviously we do need more CPU resources to implement TDE.However if your SQL Server instance experiences CPU bottlenecks then you should not use it or use it with caution.

So the encryption happens at the file level. It encrypts all the data in the database.By that I mean that besides .mdf, .ldf files tempdb,backups and snapshots are also encrypted. TDE encrypts and decrypts each 8K page.

There is no proramming required.It is very easy to set up TDE in a database and TDE is transparent to the application.We cannot use TDE with databases that have FILESTREAM data. When we have TDE turned on we cannot have on this database turned backup compression as well.So we cannot have backup compression and encryption on the same database.Obviously you cannot restore an encrypted backup to different SQL Server instance.If any database in the server instance uses TDE then the tempdb is encrypted as well.By that I mean that even if a specific database that does not use the tempdb at all, has TDE turned on, the  tempdb will still be encrypted.

In SQL Server 2005 we had another form of encryption that was called cell-level encryption. It provides encryption at the cell level or column level(I will provide an example later on).In previous versions of SQL Server e.g SQL Server 2000 we had to write code in our application where all the encryption/decryption occurred.Obviously with this option we could achieve the granularity that we miss with TDE but using cell level encryption still left much of the data unencrypted while it requires changes to the underlying database schema. Finally I would like to dissolve a myth regarding TDE and restricting access to a spesific database data. TDE is not a way to restrict data access through the database engine. If the application has permissions on a database that is encrypted then it will be able to access the data.TDE is not a substitution for securing the SQL Server instance or securing sensitive data in a database.We still need to implement security at any level.

Now let’s have a closer look on how TDE works.

We need to create a master key which will use to protect the certificate. All master keys are encrypted in the master database.

You also require to create a certificate to access the physical files.We should not ship the certificate with the encrypted database files.This certificate will protect the database encryption key. This certificate is also stored in the master database.Then we need to create the database encryption key. This is the key that will encrypt the data. Finally we need to turn TDE on.

In case you do not have an instance of Enterprise edition or Developer edition  of SQL Server 2008 / R2, you can download it from here . This is a trial version.

We need a database as well. I will use the Northwind database.

If you need the installation scripts for the sample Northwind database, click here

Let’s move on with our hands on example

1) Launch SSMS and connect to the local instance of SQL Server.First I will create a copy of the Northwind database.I do not want to work with the original I will use back and restore T-SQL commands.Execute the code below to perform the backup

USE master
GO

BACKUP DATABASE [Northwind]
TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Northwind.bak'
WITH NOFORMAT, NOINIT,  NAME = N'Northwind-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Now execute the code below to perform the restore operation.

USE master
GO
RESTORE DATABASE [NorthwindCopy]
FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Northwind.bak' WITH
FILE = 1,  MOVE N'Northwind' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\NorthwindCopy.mdf',
MOVE N'Northwind_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\NorthwindCopy_1.ldf',  NOUNLOAD,  STATS = 10
GO

2) Now let’s create the master key in master database to use with TDE.Execute the code below.

USE master
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '!@#$%^&*()'
GO

3) Now we need to create the certificate used to protect the database encryption key.Execute the code below.

USE master
GO

CREATE CERTIFICATE NorthwindCopyCertification WITH SUBJECT
= 'Certificate to implement TDE on NorthwindCopy'
GO

4) Now we need to backup the certificate.If the certificate becomes corrupted then we can use the backup copy of the certificate.Exeute the code below.

USE master
GO

BACKUP CERTIFICATE NorthwindCopyCertification
TO FILE = 'C:\Data\NorthwindCopyCertification'
WITH PRIVATE KEY ( FILE = 'C:\Data\NorthwindCopyCertificationPrivateKey' ,
ENCRYPTION BY PASSWORD = '!@#$%^&' );
GO

5) We can check to see if there are any databases encrypted in this instance of SQL Server.Execute the code below. In my case when I execute the code I get no results.

USE master
GO

SELECT DB_NAME(database_id) AS DatabaseName,
key_algorithm AS [Algorithm],
key_length AS KeyLength,encryption_state

FROM sys.dm_database_encryption_keys
GO

6) Now we need to create the database encryption key for TDE.This is analogous to database master key for data encryption.

USE NorthwindCopy
GO

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = TRIPLE_DES_3KEY
ENCRYPTION BY SERVER CERTIFICATE NorthwindCopyCertification
GO

7) Now we need to turn TDE on.This will take some time. That depends on the hardware level and size of the database.Then we check again to see if the database is encrypted. Execute the code below.

USE NorthwindCopy
GO

ALTER DATABASE NorthwindCopy SET ENCRYPTION ON
GO

SELECT DB_NAME(database_id) AS DatabaseName,
key_algorithm AS [Algorithm],
key_length AS KeyLength,encryption_state

FROM sys.dm_database_encryption_keys
GO

After executing this code I see that tempdb and NorthwindCopyare both encrypted. That was to be expected.

Have a look at the picture below.Encryption_state 3 means that the database is encrypted.

8) Now we can run a SQL statement to see if we can select data from the database.Execute the code below and you will see the results.There will be no difference.The encryption applies to the files.

USE NorthwindCopy
GO

SELECT [CategoryID]
,[CategoryName]
,[Description]
,[Picture]
FROM [NorthwindCopy].[dbo].[Categories]

If you need to turn TDE off you can run the following T-SQL statement.It will take some time for SQL Server to decrypt the data.

ALTER DATABASE NorthwindCopy SET ENCRYPTION OFF
GO

If you try to restore the encrypted database to another instance it will fail.

Now let me show you an example where I can use Cell level Encryption. The first thing we can say about cell level encryption is that it is more granular than TDE.Data is decrypted only when used. It requires changes to the schema and applications. Bear in mind that you cannot search,sort data and indexes are of no use.You can use cell level encryption in SQL Server 2005 and later.

Use cell encryption when performance is not an issue and for small amounts of data.If the threat is the theft of database files then you should use TDE

9)Let me provide you with a simple example of cell level encryption.I will create a new database. I will create a new table and insert some records in it.

CREATE DATABASE footballdata

USE footballdata
go

CREATE TABLE [dbo].[footballers](
[id] [int] PRIMARY KEY IDENTITY(1,1),
[firstname] [varchar](50) NOT NULL,
[lastname] [varchar](50) NOT NULL,
[isActive] [bit] NULL
)

GO

INSERT INTO Footballers(firstname,lastname,isActive)

VALUES ('Steven','Gerrard',1);

INSERT INTO Footballers(firstname,lastname,isActive)

VALUES ('John','Terry',1);

INSERT INTO Footballers(firstname,lastname,isActive)

VALUES ('Kenny','Dalglish',0);

SELECT * FROM footballers
go

10) Now we need to follow more or less the same process as with TDE.I create the master key, the encryption certificate and the symmetric key.Execute the code below and note the comments.

--Create Database Master Key
USE footballdata
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD = '!@#$%^&'
GO

-- Create Encryption Certificate
USE footballdata
GO
CREATE CERTIFICATE EncryptfootballdataCert
WITH SUBJECT = 'Encrypt footballdata'
GO

--Create Symmetric Key
USE footballdata
GO
CREATE SYMMETRIC KEY footballdataKey
WITH ALGORITHM = TRIPLE_DES ENCRYPTION
BY CERTIFICATE EncryptfootballdataCert
GO

11) Let’s say we want to encrypt the third column-lastname. The next step is to add a column of type varbinary to the table which will store the encrypted values from the second column.Execute the code below.

USE footballdata
GO
ALTER TABLE footballers
ADD Encryptlastname VARBINARY(256)
GO

12) Now we need to update the column with the encrypted data created by key and certificate. I use the ENCRYPTBYKEY function. Execute the code below.

USE footballdata
GO

OPEN SYMMETRIC KEY footballdataKey DECRYPTION
BY CERTIFICATE EncryptfootballdataCert
UPDATE footballers
SET Encryptlastname = ENCRYPTBYKEY(KEY_GUID('footballdataKey'),lastname)
GO
--now we drop the third column from the table

ALTER TABLE footballers
DROP COLUMN lastname

--select the data from the table and see the encrypted data.
SELECT * FROM footballers

Now we have encrypted the column (lastname) successfully.

13) Now we can decrypt the data of the column (lastname) if we want and are authorised to do so.I use the DECRYPTBYKEY function. Execute the code below and see the decrypted results.

USE footballdata
GO
OPEN SYMMETRIC KEY footballdataKey DECRYPTION
BY CERTIFICATE EncryptfootballdataCert
SELECT CONVERT(VARCHAR(50),DECRYPTBYKEY(Encryptlastname))
FROM footballers
GO

That is all for now.You can drop the database (footballdata) if you want.

Hope it helps!!!