SQL 2008 R2: Transparent Data Encryption (TDE) Example

One of the new features in SQL Server 2008/R2 is Transparent Database Encryption, or TDE. TDE lets you encrypt any database, without having to change your application. This means you can fully encrypt databases and log files for SharePoint, RMS, or anything else you wish. For the ultimate in security you can use Microsoft Exensible Key Management (EKM) to use a hardware security module (HSM) to store the private keys off-host.

The Thales nShield Connect is an excellent example of a FIPS 140-2 Level 3 and Common Criteria EAL4+ certified HSM that is compatible with SQL 2008 EKM and TDE. (Try saying that ten times fast!) Their SQL 2008 EKM brochure is here.

But for mere mortals that want to encrypt a database or two and don’t want to spend $50,000 or more for a HSM, the script below shows you how to encrypt the three Active Directory Rights Mangement Services (AD RMS) databases. Just change the database names, and you are good to go. There’s nothing unique here about RMS; just an example I worked on today. The certificate used in this example is only a self-signed, so for better security I’d recommend you use a trusted certificate. I’ll save how to do that for a future post here!

Just run the script in SQL Studio, and you should be good to go. Of course database encryption adds additional overhead, and you can kiss database compression good bye as well. So be careful what you encrypt and monitor database performance.

—-
/* Configures SQL 2008/R2 transparent database encryption.
Version 1.1, 7 July 2010 */

/* Configures master database encryption key and certificate */

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘Str0ngPA$$w0rd’;
GO
CREATE CERTIFICATE RMSServerCert WITH SUBJECT = ‘My TDE Certificate’;
GO

/* Encrypts RMS Configuration Database */

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

/* Encrypts RMS Directory Services Database */

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

/* Encrypts RMS Logging Database */

USE DRMS_Logging_RMS_Root_contoso_net_443
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE RMSServerCert;
GO
ALTER DATABASE DRMS_Logging_RMS_Root_contoso_net_443
SET ENCRYPTION ON;
GO
—-

Print Friendly, PDF & Email

Related Posts

Subscribe
Notify of
2 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Anonymous
August 28, 2012 5:56 am

Hi.

I’m looking for solutions about Extensible Key Management. Do you know if there is any software solution? In my searching for that solution I can found only hardware modules. How can I get a .dll library (like KeyProvFile.dll) to be used in SQL Server configuration (http://technet.microsoft.com/en-us/library/cc645957.aspx)?

Thanks in advance.