SQL Server Transparent Data Encryption

SQL Server 2008 Transparent Data Encryption (TDE)
A secure Environment can be built by designing a secure system, encrypting confidential assets, and building a firewall around the database servers. However, in a scenario where the physical media (such as drives or backup tapes) are stolen, a malicious party can just restore or attach the database and browse the data. One solution is to encrypt the sensitive data in the database and protect the keys that are used to encrypt the data with a certificate. This prevents anyone without the keys from using the data, but this kind of protection must be planned in advance.
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. TDE protects data “at rest”, meaning the data and log files. It provides the ability to comply with many laws, regulations, and guidelines established in various industries. This enables software developers to encrypt data by using AES and 3DES encryption algorithms without changing existing applications.
Note: TDE does not provide encryption across communication channels.
After it is secured, the database can be restored by using the correct certificate.
When enabling TDE, you should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database. The encrypting certificate or asymmetric should be retained even if TDE is no longer enabled on the database. Even though the database is not encrypted, the database encryption key may be retained in the database and may need to be accessed for some operations.
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.
The figure below illustrates the architecture of TDE encryption:

Steps involve in enabling TDE are.
• Create a master key
• Create or obtain a certificate protected by the master key
• Create a database encryption key and protect it by the certificate
• Set the database to use encryption
The following example illustrates encrypting and decrypting the AdventureWorks2008R2 database using a certificate installed on the server named MyServerCert.
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘‘;
go
CREATE CERTIFICATE MyServerCert WITH SUBJECT = ‘My DEK Certificate’;
go
USE AdventureWorks2008R2;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
GO
ALTER DATABASE AdventureWorks2008R2
SET ENCRYPTION ON;
GO
The encryption and decryption operations are scheduled on background threads by SQL Server. You can view the status of these operations using the catalog views and dynamic management views in the list that appears later in this topic.
Note: Backup files of databases that have TDE enabled are also encrypted by using the database encryption key. As a result, when you restore these backups, the certificate protecting the database encryption key must be available. This means that in addition to backing up the database, you have to make sure that you maintain backups of the server certificates to prevent data loss. Data loss will result if the certificate is no longer available.
The TDE certificates must be encrypted by the database master key to be accepted by the following statements. If they are encrypted by password only, the statements will reject them as encryptors. Altering the certificates to be password-protected after they are used by TDE will cause the database to become inaccessible after a restart.
Step by Step Screenshots of Transparent Data Encryption with follow soon.