In my previous article I outlined some of the steps and methods needed on the Visual Studio side to create an integration to Microsoft CRM. In particular, the article showed you how to create the SQL CLR methods and how these methods interact with the Microsoft CRM Web services to create or update an item in the Product entity.
Following along, once you have created the assemblies (integration assembly and XML serialization assembly), you must proceed to install these in the Framework directory — the assemblies were created targeting the .NET Framework 3.5, so they were installed there — and also register these in the Global Assembly Cache (GAC) under the %windir%\assembly folder.
Once the assemblies are GAC’ed you can now begin the process of registering these with Microsoft SQL Server 2005, 2008, or 2008 R2. To begin registering the assemblies with SQL Server, we must first define an Asymmetric Key from the signed assembly created in our previous project.
An asymmetric key is a securable entity at the database level. In its default form, this entity contains both a public key and a private key. When executed with the FROM clause, CREATE ASYMMETRIC KEY imports a key pair from a file or imports a public key from an assembly. For additional information on asymmetric keys click here.
Next, you must define a SQL Server login that’s associated to the asymmetric key for code signing purposes. One of the characteristics of the .NET Framework is that all external resources being accessed will require a certain level of trust. SQL Server accomplishes this by using a login for code signing with specific permissions to the outside world.
For more information on granting permissions to assemblies click here.
Once we have created the asymmetric key, it’s now time to create the assemblies in your company database.
For more information on creating assemblies, click here.
With the assemblies created, it’s now time to expose our CLR stored procedure to SQL Server. In order to register our CLR method, we use the standard CREATE PROCEDURE statement with a twist:
Note that the stored procedure must be created with the same number of parameters as the CLR method.
Finally, we can create a trigger on the IV00101 table to call the stored procedure and pass in the parameters required.
Here are some final notes from and things I had to implement at the SQL Server configuration level to make all this work:
1. First, you must enable CLR integration on SQL Server to allow it to execute assemblies. To enable CLR integration, you must change the ‘CLR Enabled’ option in SQL Server configuration.
2. In order to recreate all the above objects, you must first drop the stored procedure, then drop the assemblies, then login, and finally the asymmetric key, this is, objects need to be dropped in reverse order to avoid dependency errors.
I hope you had a good time reading this series. A lot of what you read here I had to learn on the fly, so a lot of reading and research went into building this integration approach. I am sure there are things that could be improved, but this is the down and dirty version.
Until next post!
Mariano Gomez, MVP
Maximum Global Business, LLC