MS SQL 2008 TDE

Introduction

Transparent Data Encryption (TDE) is the feature of MS SQL Server 2008. Data is encrypted before it is written to disk and decrypted when it is read from disk.  It is so easy to implement, just follow some steps and that’s all.

By the new feature of MS SQL 2008 TDE the backup files are also encrypted, when we take STANDARD BACKUP.

Before MS SQL Server 2008 it is not possible to secure the Backup Data. Anyone can restore and read the sensitive data that any developer does not want.

In this article we are trying to discuss related to TDE feature of MS SQL Server 2008.

Here we assume that the we all know about the Encryption Mechanism.

So what are the Steps that we must follow for TED

Step - 1  [ Create a MASTER KEY ]                                                                       

USE master;

GO

CREATE MASTER KEY

ENCRYPTION BY PASSWORD = 'My@Key';

GO

Step - 2  [ Create a CERTIFICATE that is Protected by Master Key ]

USE master;

GO

CREATE CERTIFICATE MyCertificate

WITH SUBJECT = 'TDE Certificate';

GO

Step - 3  [ Create a DATABASE ENCRYPTION KEY protected by Certificate ]

USE master;

GO

CREATE DATABASE DEMODB;

GO

USE  DEMODB;

GO

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE MyCertificate;

GO

Step - 4  [ Set DATABASE to Use Encryption ]

ALTER DATABASE DEMODB

SET ENCRYPTION ON

GO

SELECT [name], is_encrypted FROM sys.databases

GO

Step - 5  [ For BACKUP Purpose ]

Once our certificate is created we can either backup using the WITH ENCRYPTION option, or through backup options in the GUI.

Hope you like it.

Posted by: MR. JOYDEEP DAS