Understanding and Implementing Transparent Data Encryption (TDE) – SQL Server 2008


Transparent data encryption (TDE) performs real-time I/O encryption and decryption of the data and log files. The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery.

The DEK is a symmetric key secured by using a certificate stored in the master database of the server or an asymmetric key protected by an EKM module.
Transparent Data Encryption (TDE) features supported only in SQL Server 2008 Enterprise/Developer edition. TDE is designed to protect the database files like .mdf or .ndf, .ldf and .BAK.
Is using TDE, Can columns (fields in the tables) will get encrypted? NO. It is not. TDE is designed to protect only database files and backup not the column level encryption. Data in the table is visible to users who have the permission to view.
If the requirement is to encrypt specific columns use ENCRYPTBYKEY and DECRYPTBYKEY. These statements are available in SQL 2005. It can be used in the programming.

Understanding Transparent Data Encryption (TDE) refer Microsoft link http://msdn.microsoft.com/en-us/library/bb934049.aspx

Encryption of the database file is performed at the page level. The pages in an encrypted database are encrypted before they are written to disk and decrypted when read into memory. TDE does not increase the size of the encrypted database

Following are the steps to Enable and Implement TDE

1. Create Master Key

USE master 

GO    

CREATE MASTER KEY 

ENCRYPTION BY PASSWORD = ‘MySTr0ngPass@Me’

/* There can be only one Master Key for SQL Server Instance */

2 . Create TDE Certificate

USE master 

GO  

CREATE CERTIFICATE MyFirstTDECert  WITH SUBJECT = ‘My TDE Certificate’ 

 

3 . Backup the Certificate

It is important to back up the server certificates you use to encrypt your databases. Without the certificate database files and backup files are not accessible and this backup certificate is required to restore the database on another SQL instance

BACKUP CERTIFICATE MyFirstTDECert

TO FILE=’C:MyFirstTDECert.certbak’

WITH PRIVATE KEY (

FILE=’C:MyFirstTDECert.pkbak’,

ENCRYPTION BY PASSWORD=’STr0ngPass@Me’)

 4. Create a Database encryption key

Database to be encrypted requires its own Database Encryption Key (DEK) and while creating DEK specify encryption algorithm. TDE supported algorithms are AES_128, AES_192, AES_256, TRIPLE_DES_3KEY

USE FirstDB 

go

CREATE DATABASE ENCRYPTION KEY 

WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE MyFirstTDECert

 

 Set the database to use encryption

 5.  Set the database to use encryption

USE master 

GO

ALTER DATABASE FirstDB SET ENCRYPTION ON

 

Following are the steps to restore the Certificate

  1. Restore the Master Key  and Certificate

To restore the server certificate from the backup (STEP3) and if the SQL instance does not have the master key then will have to run the following command

USE master   /* use only master key does not found */

GO    

CREATE MASTER KEY 

ENCRYPTION BY PASSWORD = ‘STr0ngPass@Me’

               

CREATE CERTIFICATE MyFirstTDECert 

FROM FILE=’C:MyFirstTDECert.certbak’ 

WITH PRIVATE KEY( 

FILE=’C:MyFirstTDECert.pkbak’, 

DECRYPTION BY PASSWORD=’STr0ngPass@Me’)

 

Advertisements

One Response

  1. TDE is great for securing your data in your SQL Server database without any developer effort. We had applied this feature in our productive databases in my company.
    Here is my notes addition to yours http://www.kodyaz.com/articles/sql-transparent-data-encryption-sql-server-2008.aspx

    Posted by Eralper Yılmaz

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: