Wednesday, November 7, 2012

Protect CRM 2011 data with Transparent Data Encryption


In this post I will provide you with a little example on how to protect your Microsoft Dynamics CRM Organization database with SQL Server Transparent Data Encryption (TDE).

If you want to protect your database even the backup files so that no one will be able to take one of your backup files or disks and just move it to another SQL server and be able to get the information from your system you should use TDE to encrypt your database.
TDE will not encrypt data sent over the network!
Do not use this in production without reading more about it first!
Make sure you know enough to maintain it and know all about TDE before you use it in production.

In SQL Server 2012 Enterprise Edition you can try this out.

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SuperPassword';
GO
CREATE CERTIFICATE ServerCertificate WITH SUBJECT = 'Server Certificate DEK';
GO
USE CRMORG_MSCRM;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE ServerCertificate;
GO
ALTER DATABASE CRMORG_MSCRM
SET ENCRYPTION ON;
GO

Encryptions you may choose from are
DES, Triple DES, TRIPLE_DES_3KEY, RC2, RC4, 128-bit RC4, DESX, 128-bit AES, 192-bit AES, and 256-bit AES

It’s very IMPORTANT to backup you certificate ServerCertificate.
Without the Certificate backup you will not be able to use your database files / backup files if the server crash and burns, or if you need to recover your data.

After you run the following SQL commands the backup files will be placed in your MSSQL\Data folder. Make sure to back these up to a secure location.

USE master
GO
BACKUP CERTIFICATE ServerCertificate
TO FILE = 'ServerCertExport'
WITH PRIVATE KEY
(
FILE ='PrivateKeyFile',
ENCRYPTION BY PASSWORD ='YourOtherStrongPassword'
);
GO

Note* if you use TDE on any database in you SQL instance the TempDB will also be encrypted. This might have an impact on performance.

If you lite to read more about TDE you can start with these pages on MSDN.
About TDE.
http://msdn.microsoft.com/en-us/library/bb934049.aspx

About Encryption keys.
http://msdn.microsoft.com/en-us/library/ms345262.aspx

Be safe!

//Hans

2 comments:

  1. The post is written in very a good manner and it contains many useful information for me. Cara Menggunakan Folder Lock PC - Avoid Hang Around Looking, Read through About Cellular Desktop computers In this article protect folders with password

    ReplyDelete
  2. What's up to everybody, it's my first pay a quick visit this web page; this webpage carries remarkable and really good stuff for readers. Visit homepage: How To Lock Files In 10 Minutes And Still Look Your Best

    ReplyDelete