Transparent Data Encryption

My last post covered encrypting column level data using t-sql.  The need to encrypt data beyond column or row level may mandated by organizational governance or oversight.  Transparent data encryption is a feature available in SQL Server 2008 Enterprise edition which allows you to encrypt the database files, both log and data, as well as all backups and database snapshots.  As this works at the page level TDE does not increase the size of the database, but is CPU intensive, please refer to the technical article relating to data encryption in SQL Server.

The steps to enabling TDE begins with the creation of the of a master key in the master database and then creating a certificate from the master key.  It is critical that the certificate be backed up as the database will not be able to be decrypted if it is lost:

USE MASTER;      
GO      
--Create the master key using a password      
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd';      
GO      
--Create the certificate with the subject TDE      
CREATE CERTIFICATE TDECert      
WITH SUBJECT='TDE';      
GO      
--ALWAYS backup the certificate to file to assure you will be able to recover or to restore      
BACKUP CERTIFICATE TDECert TO FILE = 'c:\cert\TDECert'      
WITH PRIVATE KEY ( FILE = 'c:\cert\TDECertKey' ,      
   ENCRYPTION BY PASSWORD = 'Pa$$w0rd' );      
GO  

Once the certificate is create then you need to create an encryption key in the database you wish to encrypt using the certificate:

--Change the database to AdventureWorks2008      
USE AdventureWorks2008;      
GO      
--Create the database encryption key using the certificate created in the master database      
CREATE DATABASE ENCRYPTION KEY      
WITH ALGORITHM = AES_128      
ENCRYPTION BY SERVER CERTIFICATE TDECert;      
GO

Read more: SQLServerPedia

Posted via email from .NET Info