Why Transparent Data Encryption ?

As a Database or System Administrator, security is one of the most important areas to consider when it comes to protecting the databases that you support. We use various mechanisms and technologies to secure our data and databases such as firewalls, certificates, and data encryption. Having said that although we have secured our environment, questions will always be raised regarding database security. Although we have protected our databases, what would happen if someone steals the mdf file or if someone steals the backup file.

Although there are few ways to control this scenario using third-party solutions up until SQL Server 2008 there has been no native way to handle this problem. SQL Server 2008 introduces a new feature that protects the database called Transparent Data Encryption – TDE which provides protection to the entire database, that is Data is encrypted before it is written to disk; data is decrypted when it is read from disk. There is no need of changes in the application when it is implementing with existing applications that means there is no headache for developers!

Note: Applies Only to Microsoft SQL Server 2008 Enterprise Edition

I hope this article will guide you throughout the implementation of TDE in MSSQL SERVER 2008.

The diagram below shows how SQL Server encrypts a database with TDE:

Implementation of TDE

There are four steps included in Implementation of TDE

* Create a master key

* Create or obtain a certificate protected by the master key

* Create a database encryption key and protect it by the certificate

* Set the database to use encryption

1. Create a master key

A master key is a symmetric key that is used to create certificates and asymmetric keys.  Execute the following script to create a master key:

[sql]
USE master;

CREATE MASTER KEY

ENCRYPTION BY PASSWORD = ‘Pass@word1’;
GO
[/sql]

2. Create Certificate
Certificates can be used to create symmetric keys for Transparent Data Encryption or to encrypt the data directly.  Execute the following script to create a certificate:

[sql]

CREATE CERTIFICATE TDECert

WITH SUBJECT = ‘TDE Certificate’

GO

[/sql]

3. Create a database encryption key and protect it by the certificate

1. Go to object explorer in the left pane of the MSSQL SERVER Management Studio

2. Right Click on the database on which TDE Requires

3. Click Tasks and Navigate to Manage Database Encryption

4. Select the encryption algorithm (AES 128/192/256) and select the certificate you have    created

5. Then Mark the check Box for Set Database Encryption On

You can query the is_encrypted column in sys.databases to determine whether TDE is enabled for a particular database.

[sql]

SELECT [name], is_encrypted FROM sys.databases

[/sql]

You are Done !