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.”
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:
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.
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“:
Pointing to the Host A record in “TSEDOM.local“:
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:
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
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
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:
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.
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!!
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.