locked
CRM2011 Installation and MSCRMSqlClrKey creation in replicated databases RRS feed

  • Question

  • 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?

    Thanks,


    CRM Consultant


    Wednesday, October 10, 2012 8:27 PM

Answers

  • Hi David,

    Just confirming, as per advice from MS support we recreated the asymmetric key and login on the second node using the

    CREATE ASYMMETRIC KEY 

    CREATE LOGIN

      commands.

    Regards,

    Rocky.


    CRM Consultant

    Thursday, October 25, 2012 12:15 AM

All replies

  • 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:

        1. 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)
        2. 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
        3. 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)


    Microsoft CRM MVP - http://mscrmuk.blogspot.com  http://www.excitation.co.uk


    Thursday, October 11, 2012 9:53 AM
    Moderator
  • 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?

    Thanks,

    Rocky.


    CRM Consultant

    Thursday, October 11, 2012 11:57 AM
  • Rocky

    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

    David


    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    Friday, October 12, 2012 11:18 AM
    Moderator
  • Thanks David. We have loaded a call with Microsoft. I will post what they advise.

    Regards,

    rocky.


    CRM Consultant

    Saturday, October 13, 2012 11:43 PM
  • Hi David,

    Just confirming, as per advice from MS support we recreated the asymmetric key and login on the second node using the

    CREATE ASYMMETRIC KEY 

    CREATE LOGIN

      commands.

    Regards,

    Rocky.


    CRM Consultant

    Thursday, October 25, 2012 12:15 AM
  • Rocky,

    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?

    Thanks, Joe

    Friday, December 7, 2012 8:17 PM
  • Hi Joe,

    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.

    Regards,

    Rocky.


    CRM Consultant

    Sunday, December 9, 2012 1:24 AM
  • Thanks for the quick response!
    Monday, December 10, 2012 8:22 PM
  • Can you give me an example I have tried over 100 variations of this I am not able to get it working!!!   See here http://social.msdn.microsoft.com/Forums/sqlserver/en-US/27afcfec-2657-43f3-9f23-9dc4a2ba3fdf/sql-2012-always-on-availability-create-key-for-dynamics-crm-syntax?forum=sqldisasterrecovery
    Thursday, July 3, 2014 7:34 PM