The use of SQL TDE – Transparent Data Encryption is a great way to secure your Microsoft Dynamics CRM 2011 or 4.0 systems, especially when they contain sensitive data like credit cards or social security numbers. This ensures that even if someone gets a copy of your database, it cannot be used or restored on another system without a key.

Additionally because it’s TRANSPARENT, your CRM server or applications that access SQL server are NOT effected. That’s right no code changes etc. It is still best practice to test this out in your development environment first.

Please note SQL Server 2008 Enterprise is required in order to enabled SQL Transparent Data Encryption.

First, let me stress the importance of saving the encrypted backup key and password, and not just a copy on the SQL server. Please take the time to store these keys in TFS, One-Note, Sharepoint, Outlook etc something that you can retrieve these items should they be needed. A customer had this stored on the local C drive of the SQL server and lost the entire server. They could not recover their databases as they did not know the password.

Setting UP TDE – Super Fast Only 10 minutes!

Step 1: From SQL Management Studio, on the MASTER DB
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘PASSWORDHERE’

Step 2: Create the TDE Certificate
CREATE CERTIFICATE tdeCert WITH SUBJECT = ‘TDE Certificate’

Step 3: Backup the Certificate
BACKUP CERTIFICATE tdeCert
TO FILE=’D:\Scripts\tdeCert.certbak’
WITH PRIVATE KEY (
FILE=’D:\Scripts\tdeCert.pkbak’,
ENCRYPTION BY PASSWORD=’PasswordHere’)

NOTE: Make sure you save the password and certificate OFF the SQL server. The D:\drive selected is a drive on the SQL Server directly.

Step 4: Select the CRM Database you want to use
USE CRM_MSCRM
GO

Step 5: Create the Database Encryption Key
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE tdeCert

Step 6: Alter the DB
ALTER DATABASE CRM_MSCRM SET ENCRYPTION ON

Congratulations! You just enabled TDE! on your CRM Database!

How to Check for TDE Encryption:
SELECT DB_NAME (DATABASE_ID), encryption_state
FROM SYS.dm_database_encryption_keys

You may also read the Microsoft document covering SQL Server 2008 TDE right here:
http://msdn.microsoft.com/en-us/library/cc278098.aspx