Protecting Your SharePoint 2010 Content with SQL Server 2008 Transparent Database Encryption

High Level Steps to enable TDE
Create the DMK
Create the TDE Cert
Backup the TDE Cert
Create the DEK
Encrypt the DB
Monitor Progress

1. Creating the Database Master Key (DMK)
Symmetric key used to protect private keys and asymmetric keys
Protected itself by Service Master Key (SMK), which is created by SQL Server setup
Use syntax as follows:
 
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssword1';
GO

2. Create Certificate Protected by DMK
Protected by the DMK
Used to protect the database encryption key

Use syntax as follows:
USE master;
GO
CREATE CERTIFICATE CompanyABCtdeCert WITH SUBJECT = 'CompanyABCTDE Certificate' ;
GO

3. Backup Master Key and Cert
Without a backup, data can be lost
Backup creates two files, the Cert backup and the Private Key File
Use following syntax:

USE master;
GO
BACKUP CERTIFICATE CompanyABCtdeCert TO FILE = 'c:\Backup\BackupCompanyABCtdeCERT.cer'
WITH PRIVATE KEY (
FILE = 'c:\Backup\BackupCompanyABCtdeDECert.pvk',
ENCRYPTION BY PASSWORD = 'P@ssword1' );
GO

4. Create a Database Encryption Key (DEK)
DEK is used to encrypt specific database
One created for each database
Encryption method can be chosen for each DEK
Use following syntax:

USE SharePointContentDB;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE CompanyABCtdeCert
GO

5. Enable TDE
Data encryption will begin after running command
Size of DB will determine time it will take, can be lengthy and could cause user blocking
Use following syntax:
USE SharePointContentDB
GO
ALTER DATABASE SharePointContentDB
SET ENCRYPTION ON
GO

6. Monitor TDE Progress
State is Returned
State of 2 = Encryption Begun
State of 3 = Encryption Complete
Use following syntax:
USE SharePointContentDB
GO
SELECT *
FROM sys.dm_database_encryption_keys
WHERE encryption_state = 3;
GO

7. Restoring TDE Encrypted DB to Other Server

Step 1: Create new Master Key on Target Server (Does not need to match source master key)
Step 2: Backup Cert and Private Key from Source
Step 3: Restore Cert and Private Key onto Target (No need to export the DEK as it is part of the backup)
USE master;
GO
CREATE CERTIFICATE CompanyABCtdeCert
FROM FILE = 'C:RestoreCompanyABCtdeCert.cer'
WITH PRIVATE KEY (
FILE = 'C:RestoreCompanyABCtdeCert.pvk'
, DECRYPTION BY PASSWORD = 'CrypticTDEpw4CompanyABC!'
)
Step 4: Restore DB