Backup Using Transparent Data Encryption

Transparent Data encryption is one of the newly introduced feature in sql server 2008. Transferring the data between two networks/machines must be enriched with security limits to get rid of malicious  attacks.It ensures the best possibility of encryption for data and log files.Sql server engine perform the certain operation in order get transparent of data.Certainly encrypts the data before it written into the disk and Decryption the data before reads from the disk.

Usually backups perform on disk or tapes contains sensitive information that has to protect.Configuration of TDE is not as complex as clustering,Mirroring etc, just using the standard backup command after the encryption option is truned on. The backup files with TDE is highly protected,without the access with key is completely failure.

As we all know  that there are various algorithms have already been exposed in cryptography world..In our case AES algorithm is best for to make the backup file encrypt.lets bit concentrate more on encryption and decryption.

Normal definition as follows

Encryption--Plane text to cipher text
Decryption--cipher text to plane text.

basically there are two types of keys symmentric  key and asymmentric key,by using of these keys in order to storage of encrption data is effective.

USE master GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mY_P@$$w0rd'

Then, we create the server-based certificate which will be used to encrypt the database

CREATE CERTIFICATE AdventureWorksCert WITH SUBJECT = 'My DEK Certificate for AdventureWorks database'

We will then set the encryption for the AdventureWorks database by creating a database encryption key and password using the certificate we just created.

USE Adventureworks GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE AdventureWorksCert GO

The encryption algorithm choice is yours, since a few different options exist.  For guidance on selecting an encryption algorithm on SQL Server, check out this Microsoft TechNet article.  After creating the database encryption key, we'll enable encryption on the database level.

ALTER DATABASE AdventrueWorks  SET ENCRYPTION ON

Let's backup the encrypted AdventureWorks database this time but using a different filename.

BACKUP DATABASE AdventureWorks  TO DISK = 'C:\Adventureworks_encrypted.bak' WITH INIT, STATS = 10

Thats all Done!