Transparent Data Encryption

SQL Server 2008 Enterprise introduced a new way to encrypt a SQL Server.

To use TDE, follow these steps.

• • 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

Here is an example encrypting and decrypting the AdventureWorks database using a certificate installed on the server named MyServerCert:

USE master;

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>';

go

CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate'

go

USE AdventureWorks

GO

CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE MyServerCert

GO

ALTER DATABASE AdventureWorks SET ENCRYPTION ON

GO