SQL Transparent Data Encryption (TDE) with Project Server

Note: This article is based around SQL 2008/2008 R2 with Project Server 2010 databases used in the Example.

For SQL 2008/2008 R2 – Developer or Enterprise Editions are required.

Recently during requirements gathering for a new Project Server 2010 deployment, a discussion was had regarding such a situation where physical backup media could potentially be stolen and therefore data could simply be restored elsewhere, potentially exposing confidential data to the malicious party.

The answer to this was SQL Transparent Data Encryption. This option performs real time encryption and decryption of the physical files rather than data, so basically MDF, LDF and BAK files.

TDE uses a database encryption key (DEK) which is stored in the database boot record so it’s available during recovery purposes. DEK is secured by using a certificate which is stored in the Master Database of the database server itself.

 

Creating the Master DEK and Certificate (SQL self-assigned in this case) and encrypting the 5 project server databases is nice and simple, you have to run a query similar to which I made below (which ties with the above diagram from MSDN);

/* TDE Encryption for 5 Main Project Server Databases */

/* Configures Master Database Encryption Key and Certificate */

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘Str0ngPA$$w0rd’;
GO
CREATE CERTIFICATE ProjectServerCertificate WITH SUBJECT = ‘Project Server Certificate’;
GO

/* Encrypts PWA_Archive Database */

USE PWA_Archive
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE ProjectServerCertificate;
GO
ALTER DATABASE PWA_Archive
SET ENCRYPTION ON;
GO

/* Encrypts PWA_Content Database */

USE PWA_Content
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE ProjectServerCertificate;
GO
ALTER DATABASE PWA_Content
SET ENCRYPTION ON;
GO

/* Encrypts PWA_Draft Database */

USE PWA_Draft
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE ProjectServerCertificate;
GO
ALTER DATABASE PWA_Draft
SET ENCRYPTION ON;
GO

/* Encrypts PWA_Published Database */

USE PWA_Published
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE ProjectServerCertificate;
GO
ALTER DATABASE PWA_Published
SET ENCRYPTION ON;
GO

/* Encrypts PWA_Reporting Database */

USE PWA_Reporting
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE ProjectServerCertificate;
GO
ALTER DATABASE PWA_Reporting
SET ENCRYPTION ON;
GO

There is a warning post execution which states (5x in this case, once for each DB encrypted);

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.

This is expected behaviour, and needs to be actioned. To do this we run the following command;

BACKUP CERTIFICATE ProjectServerCertificate
TO FILE = ‘C:\Users\TestServerAdmin\Desktop\Certificate’
WITH PRIVATE KEY ( FILE = ‘C:\Users\TestServerAdmin\Desktop\Private Key’ ,
ENCRYPTION BY PASSWORD = ‘Str0ngPA$$w0rd’ );
GO

These backup files should be kept on a completely separate location to the database files for obvious reasons.

With these steps now carried out on my server, restoring the encrypted database to another test machine (well actually I just rolled a snapshot back on this VM to non-encrypted state) displays this error;

TITLE: Microsoft SQL Server Management Studio
——————————
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
ADDITIONAL INFORMATION:
Cannot find server certificate with thumbprint ‘0x56CCBEEBDF818539F42490658E0FA091D8F64A45’.
RESTORE FILELIST is terminating abnormally. (Microsoft SQL Server, Error: 33111)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=33111&LinkId=20476
——————————
BUTTONS:
OK
——————————

This is proving the database is TDE Protected, which is good at this stage as its proving things are working.

To get this working on my other test server I first run the following command to just create a master key on this server (one didn’t exist);

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘Str0ngPA$$w0rd’

With this done, the ProjectServerCertificate can be restored to the different test server (make sure the files quoted in the query have been copied over!)

USE master;
GO
CREATE CERTIFICATE ProjectServerCertificate
FROM FILE = ‘C:\Users\TestServerAdmin\Desktop\Certificate’
WITH PRIVATE KEY (FILE = ‘C:\Users\TestServerAdmin\Desktop\Private Key’,
DECRYPTION BY PASSWORD = ‘Str0ngPA$$w0rd’);

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s