CRM 2013: CRM And SQL Encryption

CRM 2013 now uses SQL encryption. So how do I find this encryption key for a new CRM on premise deployment?

CRM 2013 and SQL Encryption – What’s the Fuss?

One new feature used in CRM 2013 is SQL encryption to encrypt sensitive information such as Server-Side Sync email password and user credentials.

The CRM 2013 Implementation Guide (as of writing, I was reading v6.0.1. You can download it here), repeatedly warned users that once a CRM environment is setup (regardless of whether it be on premise or online), the system administrator should back up the Encryption Key randomly generated in CRM and store it in a safe place. This is because the encryption key is needed if you ever had to recovery from a failure and you migrate your CRM database if a new SQL instance and reinstall CRM on another machine.

HTTP CRM Deployment – How to Retrieve the Encryption Key?

Many posts point to the fact that if you are a CRM system administrator, you can access the randomly generated encryption key by going to “CRM > Settings > Data Management > Data Encryption”. This is certainly true for CRM 2013 Online. The Implementation Guide also says that if you have just installed a new CRM 2013 environment on premise, data encryption is by default disabled.

Great! I navigate to my newly installed CRM 2013 vm (without using SSL), tried to access the Data Encryption section, and got this:

The HTTPS protocol is required for this type of request. Enable the HTTPS protocol and try again. For more information, see the Post-Installation and Configuration instructions.

SQLEncryption-01

OMG I Need the Encryption Key To Do A CRM 2013 Migration!

This is what made me almost run screaming in panic: I tried to do a migration of the source CRM organization to another CRM deployment by backing up the organization database, restoring the DB to another SQL instance and importing the source organization to a separate CRM deployment.

Everything seemed fine with the destination CRM deployment after the migration. I then navigate to a user record and tried to update an email address and BINGO! I got the following error:

Cannot open Sql Encryption Symmetric Key because Symmetric Key password does not exist in Config DB.”

What the hell? I haven’t turned on encryption in my source CRM deployment! What do I do now?

Turns out you still need that Encryption Key from the source CRM even though you haven’t explicitly activated encryption. Now I really REALLY need to retrieve that encryption key!

Grab The Encryption Key Out Of CRM 2013

Umm…. Before you all run screaming for the hills, you must retrieve the encryption key out of CRM 2013 (the source deployment). We already know that without HTTPS, you get that horrible error (see previous section) when you tried to open up “Data Encryption” section in CRM even as a CRM system administrator. So I decided to bite the bullet and set up the source CRM deployment using HTTPS.

Sort Out Your SSL Certificate

There are many articles, for example here on how to generate your own self-signed certificate for a development environment. Note that this is obviously not recommended if you have a production environment! As mine is an isolated development environment, I decided to create my own certificate authority, then create a wildcard client certificate and a private key, combine the private key and client certificate into a PFX file.

Once I have the Certifidate Authority and wildcard certificates, I install them on the CRM server. Make sure that when you create the wildcard certificate, the name matches the domain name you intend to use for your HTTPS CRM deployment. For example, my test domain is “TSEDOM.com” so my wildcard certificate is issued to “*.tsedom.com“.

Certificate Authority goes to “Trusted Root Certification Authorities” and the wildcard certificate goes into the “Personal” store:

SQLEncryption-02

SQLEncryption-03

Don’t forget to grant the account that runs the “CRMAppPool” in your source CRM deployment the READ permission to this wildcard certificate in the “Personal” store: Right click on the wildcard certificate > Manage Private Key. Add the account to this security group and grant it READ permission.

SQLEncryption-04

Sort Out Your DNS Host Records

Now is the time to sort out your DNS host records on your domain controller so the HTTPS URL to be used for the CRM website (and name of the wildcard certificate) can be resolved.

Obviously my domain here is a test domain, it already has 2 forward lookup zones from my IFD testing earlier: “TSEDOM.local” and “TSEDOM.com“, of which neither talks to the outside world. For my purpose here, I wanted to use the FQDN “<servername>.tsedom.com” as my new HTTPS address so I created a “CNAME” record for the CRM server in “TSEDOM.com” forward lookup zone to point to the Host A record for the same CRM server in “TSEDOM.local” forward lookup zone.

CNAME record in “TSEDOM.com“:

SQLEncryption-05

Pointing to the Host A record in “TSEDOM.local“:

SQLEncryption-06

Edit CRM Host Binding To Use HTTPS

On the CRM server, go to Internet Information Service Manager and find the CRM website. When I installed CRM I had the installation wizard create a new website for CRM with the default port 5555 so the original binding was: http://<servername>:5555. Now I need to add a new HTTPS binding with default port 443 and use the wildcard certificate installed earlier on this vm:

SQLEncryption-07

Change CRM Access Points To HTTPS

The final step here is to start up CRM Deployment Manager and check the Web Address to use HTTPS. To do this, go to “Properties” in the Deployment Manager

SQLEncryption-08

If the on premise CRM deployment is set up with new default website, you will most likely see the following in “Web Address” tab: e.g. <servername>:5555

SQLEncryption-09

To enable SSL we want to change this to use HTTPS with the new FQDN of your choice. For my environment here, I want https://<servername>.tsedom.com so I need to modify the web addresses like so:

SQLEncryption-10

Do an IISRESET on the machine, and try to browse to CRM with this new URL. For my setup I outlined in this post, this would be https://crm2013rtm.tsedom.com.

SQLEncryption-11

Grab That SQL Encryption Key Now!

Now that you have enabled SSL for your on premise source CRM deployment, it’s time to revisit that Data Management section in CRM and grab that encryption key! Now that HTTPS is working, when I browse as a CRM system administrator to “Settings > Data Management > Data Encryption”, I see the encryption key!

Copy this out and store it in a safe place!!

SQLEncryption-12

What Happens To The Destination CRM Deployment After Migration?

Remember I said earlier in this post that you might encounter errors with “symmetric encryption key” if you have done a CRM 2013 migration to another server? That is, you take your CRM 2013 organization database,  migrate to another SQL instance and reimport the organization to another CRM deployment?

If that is the case, the only way I know how to resolve this error is to also have the destination CRM deployment enabled for SSL (exactly as outlined above) so you can access the Data Encryption page from within CRM. This would allow you to (1) see the “Data Encryption” page in the destination CRM deployment, and (2) allow you to paste the encryption key from your source CRM deployment to this destination CRM deployment. Once activated, you should now be able to access all the records in this new destination CRM deployment as usual.

Good luck!

Advertisements

22 thoughts on “CRM 2013: CRM And SQL Encryption

  1. I beleive you can also disable the SSL check:

    “Note: If the Microsoft Dynamics CRM website is not configured for HTTPS/SSL, the Data Encryption dialog box will not be displayed. For a more secure deployment, we recommend that you configure the website for HTTPS/SSL. However, if the website is not configured for HTTP/SSL, use a tool that can be used to modify CRM database tables, such as Microsoft SQL Server Management Studio or the Deployment Web Service, open the configuration database (MSCRM_CONFIG), and in the DeploymentProperties table, set DisableSSLCheckForEncryption to 1.”

    See http://msdynamicscrmblog.wordpress.com/2013/11/09/data-encryption-in-dynamics-crm-2013/

    1. Hi Simon,
      Thanks for your comment. Yes I did tried to change the “DisableSSLCheckForEncryption” value in the db in an earlier attempt and even rebooted all the boxes involved, but I can’t get pass the UI error unfortunately. So I had to rollback and instead went down the SSL certificate path to try to get the encryption key. Please can you post more details of how you managed this?
      Regards,
      Priscilla.

      1. Hi Mondy, can you tell how you’ve done it. I don’t know where i can change the value in the SQL Studio. Thx.

  2. Hi All,
    First, very nice blog article you have written-up here. Second, two points of interest: (1) the updated Implementation Guide indicates that new installations of CRM 2013 on-premise will have data encryption enabled by default; (2) I have included details for the work-around on how to DisableSSLCheckForEncryption — please see my blog link below.
    Thanks,
    Ian

  3. hi,my data Encryption status is inactive and current encryption key is null. i want to know what is the active encryption key ?
    Can i key anything ?

    please help me. U can contact me at MSN:zq_0234@hotmail.com.

  4. Thanks for posting this but I wish I had seen it BEFORE doing a migration just before then end of our online service term. Unfortunately the Migration whitepaper that support directs you to doesn’t warn you to record the encryption key. We are in the process of reactivating the instance just to get the key.

    I don’t know if it is related to the missing key but one problem we have after restoring the data is that another user, with the system admin role, doesn’t seem to have permission to anything. I’ve tried toggling the sys admin role and even added them as a deployment administrator without any change in their rights.

    Any suggestions would be appreciated.

  5. Just wanted to leave this here in case anyone else runs into it.

    If you have a backup of your MSCRM_CONFIG database, you can retrieve the Encryption Key. Just run the following query:

    SELECT ColumnName, VarBinaryColumn FROM OrganizationProperties
    WHERE Id IN (SELECT Id FROM Organization WHERE UniqueName = ”)
    AND (ColumnName = ‘SymmetricKeyPassword’ OR ColumnName = ‘SymmetricKeySource’)

  6. Hey there friend, just roaming through the world wide web search data and discovered your site. I’m engraved with the blogposts that you own within this web page. This displays how good you grasp this specific issue. Bookmarked this web site, will certainly come back to get more detail. You, my buddy, Rock!!!

  7. I must show thanks to the writer for rescuing me from this incident. After looking through the world wide web and coming across advice that were not productive, I figured my entire life was well over. Living without the approaches to the problems you’ve fixed by way of this review is a critical case, as well as ones which might have negatively affected my career if I had not come across your blog. Your actual competence and kindness in taking care of every item was priceless. I am not sure what I would have done if I hadn’t come across such a stuff like this. It’s possible to now look forward to my future. Thanks so much for your skilled and result oriented help. I will not be reluctant to refer the sites to any person who would like counselling about this topic.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s