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