none
Register CRM assemblies in SQL Server 2005/2008

    Question

  • I have a CRM server with a custom entity created.

    I have another SQL Server with this same entity as a table. The non-CRM server is the master for the rows in this table.

    I want to write a CLR trigger for SQL Server to use the web interface to CRM to sync the CRM data when changes occur to the non-CRM table.

    I've written a test C# console utility to access both the SQL Server and the CRM web interface. This works well.

    I have no problems writing a CLR trigger in general. My problem comes with registering the CRM assemblies with the non-CRM SQL Server.

    When I register my trigger assembly that references the CRM assemblies the error returns that the CRM assembly was not found in the SQL catalog.

    When I try to register the CRM assembly with the SQL Server I get an error that System.Workflow.ComponentModel is not in the SQL catalog.

    When I try to register that assembly I get an error for Create Assembly saying the assembly failed verification. Check if the referenced assemblies are  up-to-date and trusted.

    I found these two articles discussing this:

    http://community.dynamics.com/product/gp/gptechnical/b/gpmarianogomez/archive/2010/08/17/using-sql-clr-stored-procedures-to-integrate-microsoft-dynamics-gp-and-microsoft-crm-58-creating-a-clr-assembly-and-working-with-crm-web-methods.aspx

    https://community.dynamics.com/product/gp/gptechnical/b/gpmarianogomez/archive/2010/08/20/using-sql-server-clr-stored-procedures-to-integrate-microsoft-dynamics-gp-and-microsoft-crm-58-configuring-sql-server-and-creating-table-triggers.aspx

    The second article discusses creating asymmetric keys for the CLR trigger assembly but it kind of glosses over exactly the part I'm interested in.

    Two questions:

    Can anyone help me get the CRM assemblies registered in SQL Server so my trigger can access them?

    Am I going about this task in the right way? Is there something better for keeping these tables in sync with the non-CRM table driving the updates to the CRM system?

    I'm using Visual Studio 2010 and C# for the CLR trigger.

    My test servers are all running SQL 2008 the production servers are running SQL 2005.

    I'm using Dynamics CRM 4.0.

    TIA,

    Rick

    Tuesday, December 14, 2010 8:17 PM

Answers

  •  

    Thanks to the help from Pat and a lot of digging I have the CLR SQL triggers interfacing with Dynamics CRM very well now.

    I thought I'd summarize what I did and how to deploy the assemblies for the next developer.

     

    Inside your project/solution:

    Because of the restrictions on SQL regarding outside assemblies I used the Web Reference for CRM SDK in my project rather than the assemblies themselves. As both Pat and I discovered trying to load the CRM SDK assemblies into the SQL server required registering many other assemblies which had security issues of their own, etc.

    In your solution add a web reference to your CRM WSDL.

    • Do this by clicking Add Service Reference in your project and pressing the Advanced button.
    • In the Advanced window press Add Web Reference.
    • Enter the URL of your CRM web reference: http://<CRM Servername>:<port num>/MSCRMServices/2007/CrmServiceWsdl.aspx
    • Give the web reference a meaningful name. I used CrmSdk

    Inside your namespace/project add this code to add support for accessing the web reference.

    [assembly: AllowPartiallyTrustedCallers]
    namespace CSEMigrateCLR
    {

        // Import the Microsoft Dynamics CRM namespaces.
        using CrmSdk;


        public partial class Triggers
        {

    In this case the name of my namespace/assembly is CSEMigrateCLR.

    I added the AllowPartiallyTrustedCallers section to allow partially trusted assemblies to call my new assembly.

    Adding "using CrmSdk" adds the web reference to the module.

    Open your Project Settings:

    • on the Build tab set "Generate serialization assembly" to On.
    • on the database tab set the Permission Level to External.
    • on the Signing tab check Sign the Assembly and for the strong name key file either add the credentials for your certificate or create a <New> one.

     

    At this point you can add code to use the CRM interface and build the assembly but we need to do more to deploy it to the SQL server.

    Build your solution if you haven't already.


    To Deploy your new CLR trigger assembly to SQL.

    You need to set your SQL Server to allow CLR based triggers:

    USE master;
    GO
    EXEC sp_configure 'CLR Enabled', 1;
    go
    RECONFIGURE
    GO

    The next thing you need to do is have your SQL Server trust your assembly because your assembly requires External access. There are two ways to change your database to trust your assembly.

    The easy way is to execute this:

    use <your database>

    go

    ALTER DATABASE <your database> SET TRUSTWORTHY ON
    go

     

    While easy this is not the preferred method because it opens up any assembly to be trusted in your database.

    Instead you should create an asymmetric key for your assembly and then just trust that.

    CREATE ASYMMETRIC KEY CrmKey
    FROM EXECUTABLE FILE = '<full path to your assembly>\<assemblyname>.dll'
    GO

    Now create a login associated with the CrmKey and grant it External Access rights.

    CREATE LOGIN [crmlogin] FROM ASYMMETRIC KEY [CrmKey];
    GO

    GRANT EXTERNAL ACCESS ASSEMBLY TO [crmlogin];
    GO

    finally add a user for your login in your database:

    use <your database>
    go
    create user [crmlogin] for login [crmlogin];
    go

     

    Add a new Item to your Solution called postdeployscript.sql

    Put inside (don't forget to change the assembly name from CSEMigrateCLR to your assembly name):

    use <your database>
    go

    CREATE ASSEMBLY [CSEMigrateCLR.XmlSerializers]
        AUTHORIZATION [crmlogin]
        FROM
    '<path to your bin folder>\CSEMigrateCLR.XmlSerializers.dll'
    WITH permission_set = SAFE
    go

     

    Back in your Project Properties go to the Database Tab and change the assembly owner to crmlogin


    You are now ready to Deploy your solution.

    Good luck and I hope all of this help another developer.

    Rick

     

     

    Monday, December 27, 2010 11:31 PM

All replies

  • I started going down the track that I think you may be.

    I wanted to use the XRM libraries to implement by managed assembly.  So I wrote my SQL CLR assembly and started trying to create the assembly in SQL Server and experienced much the same thing.  I had to pre-register what ended up feeling like an endless recursive list of dependent assemblies, as SQL Server can by default only access assemblies that are either:

    • Pre-registered in SQL Server already OR
    • On the "approved" list, which is some kind of internally registered set of assemblies

    So I gave the XRM idea away and decided to use the web service instead.  NB: I did NOT register and use the CRM SDK libraries in my project, as I would then have had to go through the pre-registration nightmare all over again.

    Merely register the CRM (and Metadata if you need it) web services in your project and use them directly.

    Then you merely need to register you CLR assembly as it uses only "approved" dependent assemblies.

    NB again: I had to pre-generate serialisation code and register the serialisiation assembly also.  I'm guessing that SQL Server is unable to generate and/or use the serialisation code on the fly.

    So, all in all, I had to register only two assemblies; my Web Service client SQL CLR assembly and its corresponding XmlSerializer assembly.


    --pogo (pat)
    Wednesday, December 15, 2010 12:14 AM
  • Hi Pat,

    Thanks for the response.

    I'm not quite sure I'm following what you mean by "Not registering the CRM SDK but instead registering the CRM web services"?

     

    Thanks,

    Rick

    Wednesday, December 15, 2010 12:32 AM
  • When you connect to the CRM, you can do so either via the CRM SDK assemblies (Microsoft.Crm.Sdk.dll and Microsoft.Crm.SdkProxy.dll) or directly to the web service via the WSDL.

    I am suggesting that you do the latter; that is:

    • Do NOT register the Microsoft.Crm.Sdk.dll and Microsoft.Crm.SdkProxy.dll assemblies within your application
    • Register the CRM Web Service; point to the CRM WSDL and use the web service directly

    CRM SDK: Choosing Between the WSDL and Assemblies

    CRM SDK: How to: Add the CrmService Web Reference


    --pogo (pat)
    Wednesday, December 15, 2010 12:39 AM
  • Thanks, Pat.

     

    I'll check it out in the morning. Looks interesting.

     

    Rick

    Wednesday, December 15, 2010 1:24 AM
  • It sounds like you added a 'Service Reference', which utilises WCF (System.ServiceModel namespace).  You need to add an "old school" Web Reference instead.  In Visual Studio (I'm working in VS 2010 - may be a little different in other versions):

    1. Right-click on the References folder 
    2. Click 'Add Service Reference'
    3. Click on 'Advanced'
    4. Click on 'Add Web Reference'
    5. Type in the URL to the WSDL
    6. Click the green arrow
    7. Name the reference
    8. Click 'Add Reference'

    This will add a .NET v2.0 style web reference that should work without any additional dependencies having to be added to SQL Server.

    Caveats:

    • I was targeting SQL Server 2008; I don't know what changes might need to be made to work with 2005 (actually, I just registered my assembly in a 2005 DB, so it appears to work just fine)
    • I had to create my assemblies in SQL Server in UNSAFE mode
    • I had to set TRUSTWORTHY ON in the target DB (ALTER DATABASE <DBNAME> SET TRUSTWORTHY ON;)

     

     


    --pogo (pat)
    Wednesday, December 15, 2010 11:31 PM
  • Hi Pat,

     

    I figured that out about the Web Reference a few minutes after I posted. That's why I deleted that post.

    I actually ended up hacking the csproj file directly and adding the Web References.

    The Advanced button will be much easier in the future.

    What XML Serializer did you register. I'm getting an error now related to that.

    Sorry to be dense but I'm a T-SQL guy most of the time.

    Rick

    Thursday, December 16, 2010 12:25 AM
  • What XML Serializer did you register. I'm getting an error now related to that.

    You can, in theory, have your serialisation code generated automatically via the Visual Studio GUI.  In Visual Studio:

    1. Right-click the project name
    2. Select 'Properties'
    3. Go to the 'Build' tab
    4. Change 'Generate serialization assembly' to 'On'

    For reasons that I didn't care to hunt down, this took a *very* long time and started consume a good deal of memory on my machine, so I decided to build my serialization code with a Post-build step batch file; the content of the batch file is as follows:

    "C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\sgen.exe" /a:"<drive>:\<full path to project folder>\bin\Debug\<sql server assembly name>.dll" /p /c:/keyfile:..\..\<name of your assembly's code signing key file>.snk /force

    sgen.exe will generate an assembly named as follows:

    <sql server assembly name>.XmlSerializers.dll

    You need to register this via a CREATE ASSEMBLY statement (or SQL Server GUI equivalent) just like you did with your CRM assembly.


    --pogo (pat)
    Thursday, December 16, 2010 12:36 AM
  • I found a reference to sgen and am running the build now.

    I set it up as a post build event.

    It is taking a very long time, so hopefully everything is going OK.

    Thanks again for all your help. I think I'm getting very close to actually doing something in CRM via a CLR trigger!!

    Rick

    Thursday, December 16, 2010 12:59 AM
  •  

    Thanks to the help from Pat and a lot of digging I have the CLR SQL triggers interfacing with Dynamics CRM very well now.

    I thought I'd summarize what I did and how to deploy the assemblies for the next developer.

     

    Inside your project/solution:

    Because of the restrictions on SQL regarding outside assemblies I used the Web Reference for CRM SDK in my project rather than the assemblies themselves. As both Pat and I discovered trying to load the CRM SDK assemblies into the SQL server required registering many other assemblies which had security issues of their own, etc.

    In your solution add a web reference to your CRM WSDL.

    • Do this by clicking Add Service Reference in your project and pressing the Advanced button.
    • In the Advanced window press Add Web Reference.
    • Enter the URL of your CRM web reference: http://<CRM Servername>:<port num>/MSCRMServices/2007/CrmServiceWsdl.aspx
    • Give the web reference a meaningful name. I used CrmSdk

    Inside your namespace/project add this code to add support for accessing the web reference.

    [assembly: AllowPartiallyTrustedCallers]
    namespace CSEMigrateCLR
    {

        // Import the Microsoft Dynamics CRM namespaces.
        using CrmSdk;


        public partial class Triggers
        {

    In this case the name of my namespace/assembly is CSEMigrateCLR.

    I added the AllowPartiallyTrustedCallers section to allow partially trusted assemblies to call my new assembly.

    Adding "using CrmSdk" adds the web reference to the module.

    Open your Project Settings:

    • on the Build tab set "Generate serialization assembly" to On.
    • on the database tab set the Permission Level to External.
    • on the Signing tab check Sign the Assembly and for the strong name key file either add the credentials for your certificate or create a <New> one.

     

    At this point you can add code to use the CRM interface and build the assembly but we need to do more to deploy it to the SQL server.

    Build your solution if you haven't already.


    To Deploy your new CLR trigger assembly to SQL.

    You need to set your SQL Server to allow CLR based triggers:

    USE master;
    GO
    EXEC sp_configure 'CLR Enabled', 1;
    go
    RECONFIGURE
    GO

    The next thing you need to do is have your SQL Server trust your assembly because your assembly requires External access. There are two ways to change your database to trust your assembly.

    The easy way is to execute this:

    use <your database>

    go

    ALTER DATABASE <your database> SET TRUSTWORTHY ON
    go

     

    While easy this is not the preferred method because it opens up any assembly to be trusted in your database.

    Instead you should create an asymmetric key for your assembly and then just trust that.

    CREATE ASYMMETRIC KEY CrmKey
    FROM EXECUTABLE FILE = '<full path to your assembly>\<assemblyname>.dll'
    GO

    Now create a login associated with the CrmKey and grant it External Access rights.

    CREATE LOGIN [crmlogin] FROM ASYMMETRIC KEY [CrmKey];
    GO

    GRANT EXTERNAL ACCESS ASSEMBLY TO [crmlogin];
    GO

    finally add a user for your login in your database:

    use <your database>
    go
    create user [crmlogin] for login [crmlogin];
    go

     

    Add a new Item to your Solution called postdeployscript.sql

    Put inside (don't forget to change the assembly name from CSEMigrateCLR to your assembly name):

    use <your database>
    go

    CREATE ASSEMBLY [CSEMigrateCLR.XmlSerializers]
        AUTHORIZATION [crmlogin]
        FROM
    '<path to your bin folder>\CSEMigrateCLR.XmlSerializers.dll'
    WITH permission_set = SAFE
    go

     

    Back in your Project Properties go to the Database Tab and change the assembly owner to crmlogin


    You are now ready to Deploy your solution.

    Good luck and I hope all of this help another developer.

    Rick

     

     

    Monday, December 27, 2010 11:31 PM
  • If you don't already have a CRM blog, this would be an excellent first post.
    Dave Berry - MVP Dynamics CRM - http:\\crmentropy.blogspot.com Please follow the forum guidelines when inquiring of the dedicated CRM community for assistance.
    Tuesday, December 28, 2010 6:17 AM
    Moderator