What are the steps to TDE encrypt a SQL Server 2008 database?

Question

We have been trying the steps here: http://msdn.microsoft.com/en-us/library/cc278098.aspx but keep getting this error: Msg 15151, Level 16, State 1, Line 1 Cannot find the certificate 'tdeCert', because it does not exist or you do not have permission.

Answer 1

It means you didn't follow the steps correctly. Make sure the tdeCert exists in select * from master.sys.certificates, as the certificate is created at step 2: CREATE CERTIFICATE tdeCert WITH SUBJECT = ‘TDE Certificate’ and has to exists. You had either skipped a step, or created the certificate in the wrong database, or have a typo in the certificate name (including case on case sensitive servers).

Answer 2

You need to take care to use the correct databases:

use master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD= 'password';
go
CREATE CERTIFICATE tdeCert WITH SUBJECT = 'TDE Certificate';
go
BACKUP CERTIFICATE tdeCert TO FILE = 'C:\location'
WITH PRIVATE KEY ( FILE = 'C:\location2', ENCRYPTION BY PASSWORD = 'password');
go

use your_db
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE tdeCert

Note the use master and use your_db.

Answer 3

Transparent Data Encryption (TDE) encrypts the database by using a Database Encryption Key (DEK) that is stored in the database boot record. The DEK is in turn protected by the database master key, which is in turn protected by the service master key

1. The first step involves creating a master encryption key:

   USE master;
   GO
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password';
   GO
   

2. After creating the master encryption key, create the certificate that will be used to encrypt the database:

   CREATE CERTIFICATE servercertificate WITH SUBJECT = 'DataCertificate';
   

3. When the master key and certificate are created, you can create the DEK for the specific database, by using the CREATE DATABASE ENCRYPTION KEY statement:

   USE DecryptTesting;
   GO
   CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER      CERTIFICATE        servercertificate;
   GO
   

You will get a warning in the result window:

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.

When using TDE, create a backup of the server certificate in the master database. You can use the BACKUP CERTIFICATE statement to create a backup of the certificate and private key, both of which are required for certificate recovery. The private key password does not have to be the same as the database master key password:

    BACKUP CERTIFICATE ServerCertificate TO FILE = 'ServerCertExport' WITH PRIVATE KEY (     FILE = 'PrivateKeyFile',     ENCRYPTION BY PASSWORD = '<PrivateKeyPasswordHere>' );

Now you can encrypt the database:

    ALTER DATABASE DecryptTesting SET ENCRYPTION ON;