Implementing New Manageability Features and Practices - Microsoft SQL Server 2008 R2 Administration - page 297

How to do it...

The following steps must be followed to design and implement transparent data encryption on
an existing database without making any changes to client applications.

1. TDE can be managed by using TSQL statements or by using the SSMS tool. For this

recipe, let us use TSQL statement methods.

2. To implement the transparent data encryption, enter the following TSQL statements

from the SSMS tool query window:
--Encrypting AdventureWorks database
USE master;
GO

--First create the master key which is stored in the master
database
CREATE MASTER KEY ENCRYPTION BY PASSWORD =
'EncR^pt$erverDAta8ase';
GO

-- Then create a certificate to encrypt user database that is also
stored in the master
-- database.
CREATE CERTIFICATE DBIA_DBEncryptCertificate
with SUBJECT = 'Adventure Works 2008 R2 certificate store'
GO

3. As soon as we execute the TSQL statement, a warning message will be displayed as
follows:
Warning:

The certificate used for encrypting the database encryption key has not been backed up.
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.

4. Now, let us continue to create a DEK based on a previously created certificate by
using the following TSQL statement:
USE AdventureWorks2008R2
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE DBIA_DBEncryptCertificate
GO