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 ]
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'My@Key';
Step - 2 [ Create a CERTIFICATE that is Protected by Master Key ]
CREATE CERTIFICATE MyCertificate
WITH SUBJECT = 'TDE Certificate';
Step - 3 [ Create a DATABASE ENCRYPTION KEY protected by Certificate ]
CREATE DATABASE DEMODB;
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MyCertificate;
Step - 4 [ Set DATABASE to Use Encryption ]
ALTER DATABASE DEMODB
SET ENCRYPTION ON
SELECT [name], is_encrypted FROM sys.databases
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