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.

20 Responses to “Call a WCF Service from SQLCLR”

  1. How-To Article about Calling a WCF Service from SQLCLR « managed data Says:

    [...] to call into a WCF service from SQLCLR. So without any further ado, the How-To article can be found here. I do not have anywhere to host the code yet (my usual hosting place disappeared – don’t [...]

  2. Reed Me : If you absolutely must call a WCF service from inside your database, here’s how you do it... Says:

    [...] Call a WCF Service from SQLCLR (by Niels Berglund) [...]

  3. Adam Says:

    I was able to get a WCF service up and running with http, but when we tried to move to net.tcp some confusing reference issues seem to be occuring in the IdentityModel when you create a UpnEndpointIdentity (see bellow). Have you tried to use wcf with net.tcp on a 64-bit box?

    System.IO.FileLoadException: Could not load file or assembly ‘System.IdentityModel, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′ or one of its dependencies. Assembly in host store has a different signature than assembly in GAC. (Exception from HRESULT: 0×80131050)
    System.IO.FileLoadException:
    at System.ServiceModel.UpnEndpointIdentity..ctor(String upnName)
    at System.ServiceModel.EndpointIdentity.CreateUpnIdentity(String upnName)

  4. Shailendra Says:

    Hi

    Cool stuff!
    Trying out to call WCF Pub service (WCF Pub Sum Callback to update UI about data changes uisng CLR Trigger on table)

    Using NetTcpBinding and getting error.
    A .NET Framework error occurred during execution of user-defined routine or aggregate “ProductsTrigger”:
    System.ServiceModel.CommunicationException: The socket connection was aborted. This could be caused by an error processing your message or a receive timeout being exceeded by the remote host, or an underlying network resource issue. Local socket timeout was ’00:00:09.9882162′. —> System.Net.Sockets.SocketException: An existing connection was forcibly closed by the remote host
    System.ServiceModel.CommunicationException:

    Same code when called from from Winform app works.

    Here is the code CLR Trigger code

    [SqlTrigger(Name = @"ProductsTrigger", Target = "[dbo].[Products]“, Event = “FOR INSERT, UPDATE, DELETE”)]
    public static void ProductsTrigger()
    {
    EndpointAddress endpointAddress = new EndpointAddress(“net.tcp://localhost:8001/EventPublishService”);
    Binding netTCPBinding = new NetTcpBinding(SecurityMode.None, true);
    PublishServiceProxy publishServiceProxy = new PublishServiceProxy(netTCPBinding, endpointAddress);

    publishServiceProxy.OnProductDataChangedEvent();
    publishServiceProxy.Close();
    }

    Please help

    Shailendra

  5. How-To Article about Calling a WCF Service from SQLCLR « managed data Says:

    [...] to call into a WCF service from SQLCLR. So without any further ado, the How-To article can be found here. I do not have anywhere to host the code yet (my usual hosting place disappeared – [...]

  6. Margret Puca Says:

    I’ve glanced at most of your current posts and I was wondering if you wanted to swap website links? I am always searching to trade links with personal blogs on similar topics! I look forward to hearing back from you shortly.

  7. Leonid Ganeline Says:

    Great article!!! Timesaver! Thanks a lot.
    On my Win 7 x64 I have to do this staff with some differences:
    CREATE ASSEMBLY SMdiagnostics AUTHORIZATION dbo FROM ‘C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\SMdiagnostics.dll’ WITH permission_set = unsafe
    CREATE ASSEMBLY [System.Web] AUTHORIZATION dbo FROM ‘C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Web.dll’ WITH permission_set = unsafe
    CREATE ASSEMBLY [System.Messaging] AUTHORIZATION dbo FROM ‘C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll’ WITH permission_set = unsafe
    CREATE ASSEMBLY [System.ServiceModel] AUTHORIZATION dbo FROM ‘C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\v3.0\System.ServiceModel.dll’ WITH permission_set = unsafe
    (see, Web.dll is created from ..\Framework64\… folder but only this assembly I’ve tried to change all 4 to Framework64 and C:\Program Files\ – it didn’t work!

    Leonid Ganeline http://geekswithblogs.net/leonidganeline/ [Biztalkien blog]

  8. Hosting of Code Samples | managed data Says:

    [...] other day when I posted about the sample how to call a WCF Service from a SQLCLR method, I mentioned that I had no place to host my demo-code. I did not want to create a project on [...]

  9. How-To Article about Calling a WCF Service from SQLCLR | managed data Says:

    [...] to call into a WCF service from SQLCLR. So without any further ado, the How-To article can be found here. I do not have anywhere to host the code yet (my usual hosting place disappeared – [...]

  10. Marios Philippopoulos Says:

    Any way we can deploy this an an EXTERNAL_ACCESS assembly?

    As a DBA, I don’t like having UNSAFE assemblies running in a prod environment because of the potential of memory leaks etc.

  11. Sam Shiles Says:

    You can find another example of this at the following:

    http://www.codeproject.com/KB/database/WCFFromSQL.aspx?msg=3713879#xx3713879xx

  12. BizTalkien : BizTalk and SQL: Alternatives to the SQL receive adapter. Using Msmq to receive SQL data Says:

    [...] the issue for the production SQL Server! 2.       For more information, please, see the link http://nielsb.wordpress.com/sqlclrwcf/ 3.       Copy files: >copy “WindowsMicrosoft.netFrameworkv3.0Windows Communication [...]

  13. http://tinyurl.com Says:

    This blog, “Call a WCF Service from SQLCLR
    | managed data” tomfury.com was in fact outstanding.
    I am printing out a replica to show my personal colleagues.

    Many thanks,Calvin

  14. business continuity Says:

    Great goods from you, man. I have consider your stuff prior to and you
    are simply too magnificent. I actually like what you have received
    right here, really like what you’re saying and the best way by which you say it. You are making it enjoyable and you continue to take care of to keep it wise. I can’t wait to read far more from you.
    That is really a wonderful website.

  15. clothing Says:

    Very nice post. I just stumbled upon your blog and wanted to say that I have truly loved surfing around your weblog posts.
    In any case I’ll be subscribing on your feed and I am hoping you write again soon!

  16. iphonerepairmalaysia.jigsy.com Says:

    Its such as you learn my thoughts! You seem to understand so much about this, such
    as you wrote the ebook in it or something. I think that
    you can do with a few % to force the message home a
    bit, but other than that, this is wonderful blog. A fantastic read.
    I will definitely be back.

  17. Alexandria Says:

    If you desire to grow your knowledge only
    keep visiting this web site and be updated with the newest
    news update posted here.

  18. wiredtree dedicated server review Says:

    My brother suggested I might like this web site. He was
    once entirely right. This post actually made my day. You
    cann’t consider just how so much time I had spent for this info! Thanks!

  19. Food and Beverage Management Says:

    After I originally commented I appear to have clicked the -Notify me when new comments are added- checkbox and from now on whenever a comment is added I get 4 emails with the exact same comment.
    Perhaps there is an easy method you are able to remove me from that service?
    Thank you!

  20. red sex Says:

    It’s a pity you don’t have a donate button! I’d definitely donate to this brilliant blog! I suppose for now i’ll settle for book-marking and adding your RSS feed
    to my Google account. I look forward to fresh updates
    and will share this site with my Facebook group.
    Chat soon!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Follow

Get every new post delivered to your Inbox.

%d bloggers like this: