Understanding and Implementing Transparent Data Encryption (TDE) using MS SQL 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 a Master Key

USE master 

GO    

CREATE MASTER KEY 

ENCRYPTION BY PASSWORD = ‘MySTr0ngPass@Me’

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

  1. Create TDE Certificate

USE master 

GO  

CREATE CERTIFICATE MyFirstTDECert  WITH SUBJECT = ‘My TDE Certificate’ 

  1. 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’)

  1. 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

  1.   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’)

By: Arun Halyal

About nishikantsharma

We are a global IT services company with sharp focus on ERP and BI services. Founded by first generation entrepreneurs with initial niche focus on PeopleSoft technologies. We have completed several large ERP and BI servces USA and India. Founded in 2004 with offices in Atlanta, GA and sales offices in Chicago, IL. Delivery Centers in Atlanta, Bangalore and Chennai, India. We are ISO 9001:2008 certified company

Comments are closed.

Post Navigation