transparent data encryption steps

In this blog post we cover "configure Transparent Data Encryption (TDE);" requirement which is part of our Exam Guide - 70-462 Administering SQL Server 2012.

We will cover only steps that are needed to enable Transparent Data Encryption. For more information visit http://msdn.microsoft.com/en-us/library/bb934049.aspx

• Create master key encryptioin key

USE master;

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKeyPassword';

• Create certificate

CREATE CERTIFICATE ServerCertificate WITH SUBJECT = 'Server Certificate';

• Create database encryption key

USE AdventureWorks2012;

GO

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_128

ENCRYPTION BY SERVER CERTIFICATE ServerCertificate;

GO

After running the command we get

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.

The command belows backups certificate and creates private key with password encryption.

• Backup certificate

use master

go

BACKUP CERTIFICATE ServerCertificate TO FILE = 'c:\backups\storedcerts\ServerCertificate'

WITH PRIVATE KEY ( FILE = 'c:\backups\storedkeys\ServerCertificate' ,

ENCRYPTION BY PASSWORD = '997jkhUbhk$w4ez0876hKHJH5gh' );

GO

• Enable encryption

ALTER DATABASE AdventureWorks2012

SET ENCRYPTION ON;

GO

Take care

Emil