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.
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
WITH PRIVATE KEY (
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
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
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.