Wednesday, October 10, 2012 8:27 PM
When CRM2011 Installs, it creates the CRM group logins and a user called 'MSCRMSqlClrLogin'.
This user is mapped to an asymmetric key called MSCRMSqlClrKey.
If you are setting up replicated databases (eg. using 'Always On' in CRM2012 or any other replication methods),
how do you create this key in the second node?
- Edited by George DoubinskiMVP, Moderator Thursday, October 11, 2012 12:11 AM Fixed spelling in title
Thursday, October 11, 2012 9:53 AMModerator
I've not experienced this directly, but my understanding is:
- The purpose of the login and the key is probably to allow the CRM CLR assembly (Microsoft.Crm.SqlClr.Helper) the UNSAFE permission set, without setting the TRUSTWORTHY database property
- If so, the asymmetric key should be based on the key pair used to sign the CMR CLR assembly, and there shouldn't be a way for you to access this directly, so I don't think you can manually create the key in the second node
I can think of a few options - note I've not tried any of these, so I don't know which would work:
- This only relates to CLR procedures, which may be disabled at the server level. If they are disabled at the server level, then I would expect that the key and login are unused, and you shouldn't have any problems. However, there is a reason for the CLR procedures, and that is improve performance in some queries and some batch functions (I think duplicate checking rules is one example)
- The CRM setup program will create the key and login. So, you could run the CRM setup program twice, once pointing to each node in turn
- Set the TRUSTWORTHY database property to true in the organisation database(s). This should avoid the need for the assembly to be signed within SQL. An argument could be made that this is not best security practice, but if you're happy that the database is well enough secured, then this is a trade-off against the potential performance overhead of not using CLR (option 1)
- Edited by DavidJennawayMVP, Moderator Thursday, October 11, 2012 9:53 AM
Thursday, October 11, 2012 11:57 AM
Thank you very much for replying David.
With the second option, if I run the setup pointing to Node B, the database is being replicated from Node A on an ongoing Basis. If we ever switch CRM to point to Node B as the primary, will the key work given thr database is replicated from Node A?
Friday, October 12, 2012 11:18 AMModerator
My understanding is that the key should work if switched - if it is created as part of the install process, then it will relate to the same assembly on each node.
However, I've never implemented this configuration, so my answer is only theoretical. It would need testing to be certain
Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk
Saturday, October 13, 2012 11:43 PM
Thanks David. We have loaded a call with Microsoft. I will post what they advise.
Thursday, October 25, 2012 12:15 AM
Just confirming, as per advice from MS support we recreated the asymmetric key and login on the second node using the
CREATE ASYMMETRIC KEY
- Marked As Answer by DavidJennawayMVP, Moderator Thursday, October 25, 2012 11:15 AM
Friday, December 07, 2012 8:17 PM
As David mentioned the asymmetric key should be based on information not available (key pair, etc.). Can you provide details on how you were able to create this key?
Sunday, December 09, 2012 1:24 AM
The following command created the key:
CREATE ASYMMETRIC KEY MSCRMSqlClrKey FROM EXECUTABLE FILE =''' + '<path>\Microsoft.Crm.SqlClr.Helper.dll' + '''
Where path is where the dll is located.
Monday, December 10, 2012 8:22 PMThanks for the quick response!