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
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKeyPassword';
CREATE CERTIFICATE ServerCertificate WITH SUBJECT = 'Server Certificate';
- Create database encryption key
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE ServerCertificate;
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 ServerCertificate TO FILE = 'c:\backups\storedcerts\ServerCertificate'
WITH PRIVATE KEY ( FILE = 'c:\backups\storedkeys\ServerCertificate' ,
ENCRYPTION BY PASSWORD = '997jkhUbhk$w4ez0876hKHJH5gh' );
ALTER DATABASE AdventureWorks2012
SET ENCRYPTION ON;