SQL Transparent Data Encryption SQL 2008 (TDE)

SQL Transparent Data Encryption (TDE) Definition

SQL Transparent data encryption (TDE) can be considered as a new security tool for security administrators and SQL Server Database administrators which performs a real time I/O encryption and decryption while reading sql data and log files.
By using transparent data encryption SQL 2008 administrators can keep data and log files encrypted on the data disks, on storage media.
When database content is already encrypted on the data disks, unauthorized users can not view the contents of the data, log or even backup of the database when they even reach the storage media or backup tapes physically.
So even unauthorized people stole the database disks, they can not view and display the data in the database files.

How SQL Server 2008 Transparent Data Encryption (TDE) Works ?

Here is a flow diagram how the SQL Server 2008 handles sql transparent data encyption from TechNet on secure data operation section.

sql transparent data encyription sql 2008 tde

How to enable SQL Transparent Data Encryption on SQL Server 2008

The process flow diagram of the transparent data encyription process is actually summarizing the steps that a database administrator should do to enable tde on any MS SQL Server 2008 database instance.

First step is to create a master key
Following the master key creation, a certificate protected by the master key should be created.
Later create database encryption key protected by the certificate created in the previous step
Last step is to enable the transparent data encyription for the database to use TDE encryption method

Let's create a new SQL Server database and insert into sql tables some sensitive data.
You can either create a new database on a MS SQL Server 2008 database instance using the Microsoft SQL Server Management Studio object explorer window. Or you can create the new sql database just by executing the below CREATE DATABASE t-sql command where in our sample case the TDE is the name of the sample SQL Server database.


CREATE DATABASE TDE

Code

Now assume that we have a customers table where we keep the customer credit card number in it.


Create Table Customers

(

  CustomerId int,

  FirstName nvarchar(50),

  LastName nvarchar(50),

  CreditCardNumber varchar(50)

)

Code

I know CCN 's are not kept in sql databases using methods in this sql example. But I'm trying to illustrate transparent data encryption adds to security in means of reading data files or SQL back up files.
Let me insert credit card number of Anakin Skywalker, the greatest Jedi ever lived!


Insert Into Customers

Select 1, N'Anakin', N'Skywalker', '1234567812345678'

Code

It is now time to take the back-up of TDE sql database. You can take the back up of SQL Server TDE database using the SQL Server Management Studio database Tasks, Backup database tool dialog screen.
After the database sql backup task is completed, please run the Notepad application and open the database backup file with Notepad.
Since the database is small the Notepad can handle to display the contents of the sql TDE database and its table Customers.

tde-sql-server-database-backup-file-contents

Unfortunately, in default cases if you can not keep your database backup files secure as well as backup tapes and disks, your sensitive data can be hacked by unauthorized users at least by obtaining the backup files pyhsically in illegal ways.
If you stop the SQL Server (MSSQLSERVER) service, you can also open the data file (TDE.mdf) and the log file (TDE.ldf) by using a text editor like Notepad and view how easy it is to find the sensitive credit card number without knowing any SQL login name and password, etc.

SQL Server TDE Data Log Backup Files

Now we will see how we can keep our data more secure.

Remember the summarized list of steps in section how to enable transparent data encryption.
Go to master database and create master key if you have not already created it for some other reason.


USE master;

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'masterkeypassword';

GO

CREATE CERTIFICATE TDECertificate WITH SUBJECT = 'TDECertificate';

GO

Code

The following steps include creating the database encryption key for the related sql database and altering the database to enable the transparent data encryption.


USE TDE;

GO;

CREATE DATABASE ENCRYPTION KEY
 
WITH ALGORITHM = AES_128

ENCRYPTION BY SERVER CERTIFICATE TDECertificate;

GO;

Code

The SQL engine will warn the executing user or the database administrator about taking backup of the certificate and database encryption key.

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.


ALTER DATABASE TDE SET ENCRYPTION ON;

GO

Code

Now, activated or enabled sql TDE, Transparent Data Encryption will perform an online decryption of data while reading it from data pages and encryption of data during writing it on data pages.
When you take the sql TDE database backup and try to view the contents of it using Notepad for example, you will not be able to find our sample Customer data since all sql data is encrypted with transparent data encryption method on SQL Server 2008 database.
.mdf SQL data file and .ldf log files also contain encrypted data so your company's sensitive data is in secure hands. And neither sql developers or the SQL Server database administrators do not required to build extra applications or run additional scripts, code, etc in order to decrypt or encrypt data.

Download transparent data encryption not enabled sql TDE database backup, 84 KB
Download transparent data encryption enabled sql TDE database backup, 1.36 MB