Enabling Transparent Data Encryption

To enable TDE:

  1. Create a master key.

  2. Create or obtain a certificate protected by the master key.

  3. Create a database encryption key and protect it by the certificate.

  4. Set the database to use encryption.

The following example illustrates encrypting and decrypting the TDEPT85X database using a certificate installed on the server named PeopleToolsEncryptCert.

USE master;
GO
/* Create Master Key Using a strong password. */
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>';
go
/* Create a PeopleTools Encryption Certificate. *?/
CREATE CERTIFICATE PeopleToolsEncryptCert 
WITH SUBJECT = 'PeopleTools Encrypt Certificate';
go
USE TDEPT85X;
GO
/* Create Database Encryption Key Using PeopleTools Encryption Certificate. */
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE PeopleToolsEncryptCert;
GO
/* Enable Database Encryption. */
ALTER DATABASE TDEPT85X
SET ENCRYPTION ON;
GO

Note:

When implementing TDE, all files and filegroups in the database are encrypted. If any filegroups in a database are marked read only, the database encryption operation will fail.