SQL Develop

Call a WCF Service from SQLCLR

This is a brief How-To about calling a WCF service from a SQLCLR method. The assumptions in this article is that the reader:

  • knows how to create a self-hosting (console-app, winform etc.) WCF service
  • knows how to create a client, consuming that service
  • can deploy an assembly to SQL Server manually through T-SQL scripts
  • can create T-SQL wrapper functions for the methods in the deployed assembly

Create the service

We start with creating the service. In the accompanying code, I have a very simple WCF service, which adds two integer’s together. The service is self-hosted through a console application. Make sure that your service exposes meta data.

Compile your service and run it. Ensure you can view the meta data (WSDL) from a browser.

Create the SQLCLR assembly

When you have built and tested your WCF service for metadata, it is time to create your SQLCLR assembly, i.e. in this case the code that consumes your service.

Normally when you develop SQLCLR assemblies you probably use the built-in project type in Visual Studio for this. In this case you cannot use that project type as your assembly will have dependencies on system assemblies (System.ServiceModel.dll and friends). SQL Server does not allow these particular assemblies  to be loaded from the GAC. The Visual Studio project type only allows you to add references to assemblies that either are allowed to be loaded from the GAC or already are in the database.

So to create your SQLCLR assembly you start with creating a new class library project with a public class and a public static method (SQLCLR -as you probably know – requires the methods to be public static). As mentioned above you also need an assembly reference to System.ServiceModel.dll.

Create the method calling into the WCF service

When consuming a WCF service you have mainly two choices when it comes to proxy generation:

  • you generate the proxy on the fly by using ChannelFactory<T>.CreateChannel
  • you pre-generate the proxy code either by adding a ServiceReference or using svcutil.exe

In SQLCLR you can do it both ways as well. The only caveat when creating a SQLCLR WCF consumer is that in a “normal” client, you probably use a configuration file to define endpoints, bindings, and so on. In SQLCLR, configuration files do not really work, so you have to programaticallly set-up the endpoints etc. Below is an example of this when using ChannelFactory<T>.CreateChannel:
EndpointAddress ep = new EndpointAddress("http://localhost/SqlClrWcf/Service1");
IService1 proxy = ChannelFactory<IService1>.CreateChannel(new WSHttpBinding(), ep);
When you have written the code to consume the WCF service (using either ChannelFactory<T>.CreateChannel or a pre-generated proxy), you build your assembly.

Deploying the assemblies

Having (successfully) built your assembly it is time to deploy. However if you at this stage try to deploy your assembly you would receive an exception saying that your assembly references System.ServiceModel.dll, which is not allowed to be loaded from the GAC, and is not in the database.

Deployment of system assemblies

OK, let us deploy System.ServiceModel.dll then. This assembly is located in two places (at least) in the file system. You can find it under:

  • %ProgramFiles%\Reference Assemblies\Microsoft\Framework\v3.0\System.ServiceModel.dll
  • %SystemRoot%\Microsoft.net\Framework\v3.0\Windows Communication Foundation\System.ServiceModel.dll

However, if you tried to deploy it to your database from either of those two locations, SQL Server would complain that there were other assemblies missing. So what you need to do is:

  • Make sure that the login of the owner of the database are allowed to deploy assemblies under the UNSAFE permission set (all the assemblies we are going to deploy need that permission set):

grant unsafe assembly to ...;

  • As we will be deploying unsafe assemblies we need to make the database trustworty (or use certificates, certificates are to be preferred, but it is more convoluted):

alter database [wcftest]
set trustworthy on;
go

  • Before you can start deploying the needed assemblies, you need to do one more thing (this is not 100% necessary, but it will make things go smoother). You should copy %SystemRoot%\Microsoft.net\Framework\v3.0\Windows Communication Foundation\Microsoft.Transactions.Bridge.dll to %ProgramFiles%\Reference Assemblies\Microsoft\Framework\v3.0\Microsoft.Transactions.Bridge.dll

Now you can start deploying the necessary assemblies. Do not forget to deploy them with UNSAFE permission set:
create assembly [AssemblyName]
from 'path_to_assembly'
with permission_set = unsafe
go
The assemblies you have to deploy are the following (and in this order):

  • %SystemRoot%\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\SMdiagnostics.dll
  • %SystemRoot%\Microsoft.NET\Framework\v2.0.50727\System.Web.dll
  • %SystemRoot%\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll
  • %ProgramFiles%\Reference Assemblies\Microsoft\Framework\v3.0\System.ServiceModel.dll

Deployment of user assembly

When you have deployed the necessary system assemblies, you can deploy your user assembly. If you have created assemblies previously that has called into .asmx web-services, you may remember that you have had to sgen the proxy type. This is as .asmx web-services are using XML Serialization and generates a serialization assembly on the fly. This is not allwed in SQLCLR, hence why you had to run sgen.

Using WCF services, you no longer need to sgen your proxy code (unless of course you do xml serialization somewhere in your code). You can deploy your assembl directly. Note however that when you deploy assemblies using WCF services, you need to deploy the assembly with the UNSAFE permission set:
create assembly CqlClrAsm
from 'path_to\CqlClrAsm.dll'
with permission_set = unsafe
go

Creation of T-SQL method and execution

Having deployed the assembly you can now create a T-SQL wrapper function around your SQLCLR method:
create function WcfAdder(@x int, @y int)
returns int
external name CqlClrAsm.[Nielsb.Samples.SqlClr.WcfClient].WcfAdderClient
go
Now comes the moment of thruth; does it work? Make sure your WCF service is up and running and execute the T-SQL function:
select dbo.WcfAdder(21, 21);
With a little bit of luck you should now get the answer to all questions back from the WCF service: 42.

Exception

However, chances are that you are getting an exception like so:
A .NET Framework error occurred during execution of user-defined routine or aggregate "WcfAdder":
System.Configuration.ConfigurationErrorsException:
The type 'Microsoft.VisualStudio.Diagnostics.ServiceModelSink.Behavior,
Microsoft.VisualStudio.Diagnostics.ServiceModelSink, Version=3.0.0.0,
Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'
registered for extension 'Microsoft.VisualStudio.Diagnostics.ServiceModelSink.Behavior'
could not be loaded.
(C:\Windows\Microsoft.NET\Framework\v2.0.50727\Config\machine.config line 189)
This has to do with debugging of WCF services. There are two ways to work around this issue:

  • Delete the offending line in the machine.config file. If you do this then you need to drop the T-SQL function, drop the assembly, re-deploy the assembly and re-create the function. This is due to that the config file has been parsed into the assembly domain, so you need to make sure that the domain is being torn down, before re-trying.
  • Disable WCF debugging by using the vsdiag_regwcf.exe tool that you can find in %ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE. You disable it by running:

vsdiag_regwcf.exe -u
To re-enable WCF debugging you run the tool with the -i switch.

Code download

As mentioned above, I have code examples for this, the code consists of:

  • a C# WCF service
  • a C# WCF client that can be used to test the service from outside of SQL Server
  • a C# class library project that are to be used as the SQLCLR assembly
  • T-SQL code that does the deployment etc and executes

You can find the code here.

 

SQL Develop