Looking into Transparent Data Encryption October 30, 2011Posted by fofo in Sql Server 2005, SQL Server 2008.
Tags: cell level encryption, certificate, encryption, key, TDE, 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!!!