Using SQL Server CLR stored procedures to integrate Microsoft Dynamics GP and Microsoft CRM: Configuring SQL Server and creating table triggers

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.

USE master;
GO

CREATE ASYMMETRIC KEY CrmKey
FROM EXECUTABLE FILE = 'C:\Windows\Microsoft.NET\Framework\v3.5\Crm.Integration.dll'
GO

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.

USE master;
GO

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

GRANT UNSAFE ASSEMBLY TO crmlogin;
GO

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.

USE [CompanyDB];
GO

CREATE ASSEMBLY [Crm.Integration]
FROM 'C:\Windows\Microsoft.NET\Framework\v3.5\Crm.Integration.dll'
WITH PERMISSION_SET = UNSAFE;
GO

CREATE ASSEMBLY [Crm.Integration.XmlSerializers]
FROM 'C:\Windows\Microsoft.NET\Framework\v3.5\Crm.Integration.XmlSerializers.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

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:

SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[crmInsertProduct]
  @itemNumber       NVARCHAR(31),
  @itemDescription  NVARCHAR(100),
  @VendorName     NVARCHAR(65),
  @VendorItem       NVARCHAR(31),
  @ItemShipWeight   NUMERIC(19,5),
  @defaultUofM     NVARCHAR(20),
  @defaultUofMSched NVARCHAR(20),
  @defaultPriceList NVARCHAR(20),
  @currencyID       NVARCHAR(15),
  @decimals     INT,
  @quantityOnHand   NUMERIC(19,5),
  @listPrice        NUMERIC(19,5),
  @priceListPrice   NUMERIC(19,5),
  @standardcost     NUMERIC(19,5),
  @currentCost     NUMERIC(19,5),
  @productTypeCode  INT
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [Crm].[Integration].[clrProcedures].[CreateProduct]
GO
SET ANSI_NULLS OFF
GO
SET ANSI_WARNINGS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

GRANT EXECUTE ON [dbo].[crmInsertProduct] to [DYNGRP]
GO

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.

USE master;
GO
EXEC sp_configure 'show advanced option', '1';
GO
RECONFIGURE;
GO
EXEC sp_configure 'CLR Enabled', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'show advanced option', '0';
GO
RECONFIGURE;
GO

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.

USE [CompanyDB]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[crmInsertProduct]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[crmInsertProduct]
GO

IF  EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'Crm.Integration.XmlSerializers' and is_user_defined = 1)
DROP ASSEMBLY [Crm.Integration.XmlSerializers]

GO

IF  EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'Crm.Integration' and is_user_defined = 1)
DROP ASSEMBLY [Crm.Integration]
GO

USE master;
GO

IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'crmlogin')
DROP LOGIN [crmlogin]
GO

DROP ASYMMETRIC KEY CrmKey;
GO

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!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/