Jon Morisi's Configuring MS SQL TDE

Contents

Configuring TDE

Understanding Transparent Data Encryption (TDE)

Configure TDE

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.

Configure the database to use encryption

Monitor Progress of Encryption

Backup Configuration

Backup Master Key

Backup certificate with private key (database encryption key)

Removing TDE

Configure the database not to use encryption

Drop database encryption key

Drop certificates

Drop master key

Notes 

Understanding Transparent Data Encryption (TDE)

http://msdn.microsoft.com/en-us/library/bb934049.aspx

Configure TDE

Create a Master Key

USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd!';

GO

Create or obtain a certificate protected by the Master Key

--Create Certificate

CREATE CERTIFICATE Cert

   WITH SUBJECT = 'Certificate for TDE',

   EXPIRY_DATE = '10/31/2099';

GO

--Create Certificate from File (used for restores of encrypted Database)

CREATE CERTIFICATE Cert

    FROM FILE = 'E:\Backup\Cert.cer'

    WITH PRIVATE KEY (FILE = 'E:\Backup\PK.pvk' ,

    DECRYPTION BY PASSWORD = 'P@ssw0rd!');

GO

Create a database encryption key and protect it by the Certificate.

--This step is not necessary for restores to a new instance

Use AdventureWorks

GO

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE Cert

GO

Configure the database to use encryption

ALTER DATABASE AdventureWorks

SET ENCRYPTION ON

Monitor Progress of Encryption

--encryption_state 2 = encryption has begun

--encryption_state 3 = encryption has completed

Use master

GO

select DB_NAME(database_id), * from sys.dm_database_encryption_keys

Backup Configuration

Backup Master Key

http://msdn.microsoft.com/en-us/library/ms174387.aspx

use master

go

BACKUP MASTER KEY TO FILE = 'E:\Backup\MasterKey.cer'

    ENCRYPTION BY PASSWORD = 'P@ssw0rd!'

Backup certificate with private key

use master

GO

BACKUP CERTIFICATE Cert TO FILE = 'E:\Backup\Cert.cer'

    WITH PRIVATE KEY ( FILE = 'E:\Backup\PK.pvk' ,

    ENCRYPTION BY PASSWORD = 'P@ssw0rd!');

GO

Removing TDE

(DROP everything)

Configure the database not to use encryption

ALTER DATABASE AdventureWorks

SET ENCRYPTION OFF

Drop database encryption key

Use master

GO

select DB_NAME(database_id), * from sys.dm_database_encryption_keys

USE AdventureWorks;

GO

DROP DATABASE ENCRYPTION KEY;

GO

Drop certificates

use master

go   

SELECT * from    sys.certificates

use master

go   

DROP certificate Cert     

Drop master key

use master

go

SELECT * from sys.symmetric_keys

use master

go

DROP MASTER KEY

Notes

TDE is not compatible with instantaneous file initialization or backup compression.  Proactively disable backup compression and adjust file growth increments down.  Without instantaneous file initialization I set max growth rates no larger than 1 GB.