Wednesday, 19 March 2014

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

4 comments:

  1. Please Note That:
    -------------------------
    A certificate or asymmetric key is used with encryption algorithm to achieve the goal. We have multiple options of algorithm with this feature. The supported algorithms are AES 128, AES 192, AES 256, and Triple DES.

    ReplyDelete
    Replies
    1. Create an encrypted backup file:
      -----------------------------------------------

      BACKUP DATABASE DEMODB
      TO DISK = 'C:\MyBackUp\DEMODB_Encrypted.bak'
      WITH COMPRESSION, ENCRYPTION
      (ALGORITHM = AES_256, SERVER CERTIFICATE = MyCertificate);

      Delete