Tribridge Connections

A Technology, Cloud Solutions & Industry Expertise Blog


SQL Transparent Data Encryption Setup in Dynamics CRM 2011 or 4

SQL Transparent Data Encryption in Microsoft Dynamics CRM 2011 or 4.0

The use of SQL Transparent Data Encryption (TDE) 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 TDE are not affected. That’s right, no code changes etc. However, 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 enable SQL Transparent Data Encryption.

 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 SQL Server 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

Click here to learn more on how you can enable and use SQL Transparent Data Encryption in server 2008.

If you have any feedback or questions on the SQL TDE details in this blog post, please feel free to comment below.

Share Your Thoughts With Us

Load more comments
Thank you for the comment! Your comment must be approved first