Safeguarding Confidential Data in SharePoint 2010 : Enabling TDE for SharePoint Content Databases

4/2/2011 3:25:27 PM

The steps for enabling TDE on an existing SharePoint content database are straightforward and involve the following high-level tasks:

1.	Create the DMK.
2.	Create the TDE Certificate.
3.	Back up the TDE Certificate.
4.	Create the DEK.
5.	Encrypt the database.
6.	Monitor the progress.

Creating the Database Master Key (DMK)

The first step in the TDE process is to create the DMK. None of the TDE steps can be done from the SQL GUI; they must all be run as TSQL scripts. It is recommended to create the scripts in advance and then run them one at a time, such as what is illustrated in Figure 1.

Figure 1. Creating the DMK.

The TSQL script for creating the DMK is as follows. Change the password value to one specific to your organization and store the password in a safe place:

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'CrypticTDEpw4CompanyABC';
GO

After the DMK is created, it can then be used to create the TDE Certificate.

Creating the TDE Certificate

Using TSQL commands again, the TDE Certificate can be generated from the DMK on the server. Replace the certificate name and subject with one relevant to your organization:

USE master;
GO
CREATE CERTIFICATE CompanyABCtdeCert WITH SUBJECT = 'CompanyABC TDE Certificate';
GO

Back Up the TDE Cert

Upon creation of the certificate, you will want to immediately back it up and store it in a safe place, away from the SQL backups for SharePoint. If this key backup is lost, the SQL backups will be worthless. To back up the certificate, use TSQL syntax similar to the following:

USE master;
GO
BACKUP CERTIFICATE CompanyABCtdeCert TO FILE = 'C:\Backup\CompanyABCtdeCERT.cer'
WITH PRIVATE KEY (
FILE = 'C:\Backup\CompanyABCtdeCert.pvk',
ENCRYPTION BY PASSWORD = 'CrypticTDEpw4CompanyABC!');
GO

Caution

It is extremely critical that this key is backed up and stored in a safe, fault-tolerant place. If it is lost, all SharePoint content in encrypted databases could be lost forever.

Note that the TSQL script also specifies that the private key is backed up (CompanyABCtdeCert.pvk). This private key must be stored together with the certificate backup and restored with the TDE Certificate if you want to recover TDE-encrypted databases to another server. Also note that we are encrypting the certificate with a manual password; this will need to be used to restore the private key and certificate, so be sure to write it down and store it in a safe place.

Creating the DEK

The TDE Certificate can then be used to create a DEK that will be used for the individual SharePoint content database that will be encrypted. Use syntax similar to the following, but substitute SharePointContentDB for the name of your SharePoint Content DB. A unique DEK will need to be created for each Content DB encrypted:

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

Encrypt the DB

Finally, use the DEK to encrypt the specific SharePoint content database. Replace the name of the DB here with your own DB. Repeat the previous step and this step for any remaining SharePoint Content DBs:

USE SharePointContentDB
GO
ALTER DATABASE SharePointContentDB
SET ENCRYPTION ON
GO

Monitoring Progress

TDE will immediately begin to encrypt the content DB. It can do this on the fly as the database is being used. Depending on the size of the database, this might take a while. Progress of the encryption can be monitored with another script shown here, which returns all database with encryption state 3. An encryption state of 1 = no encryption, whereas a state of 2 indicates that encryption has begun. A state of 3 means that encryption is complete. Run this command until the results show the database as having a state of 3, as shown in Figure 2. The TDE encryption process will then be complete:

USE SharePointContentDB
GO
SELECT *
FROM sys.dm_database_encryption_keys
WHERE encryption_state = 3;
GO

Figure 2. Monitoring TDE encryption status.

Repeat for any remaining databases.

Restoring the TDE Encrypted DB to Another Server

If a restore attempt of a TDE encrypted backup file is attempted, a failure such as the one shown in Figure 3 will occur. To be able to restore the backup file, the target server will need to have the TDE Certificate restored to it.

Figure 3. Viewing an error generated when attempting to restore a TDE-encrypted database from backup.

The high-level steps for restoring a TDE-encrypted database to another server are as follows:

1.	Create new DMK on target server. (Each DMK is unique, so simply create a new one using the TSQL listed in previous steps. This DMK does not need to match the one from the source server.)
2.	Back up the certificate and private key from the source using the TSQL script shown previously.
3.	Restore the TDE Certificate and the private key onto the target (no need to export the DEK, as it is part of the backup file). Use similar syntax to what is shown here: USE master; GO CREATE CERTIFICATE CompanyABCtdeCert FROM FILE = 'C:\Restore\CompanyABCtdeCert.cer' WITH PRIVATE KEY ( FILE = 'C:\Restore\CompanyABCtdeCert.pvk' , DECRYPTION BY PASSWORD = 'CrypticTDEpw4CompanyABC!' )
4.	Restore the database file from backup.