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.
February 8, 2009 at 3:31 pm |
[…] 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 […]
February 11, 2009 at 11:02 pm |
[…] Call a WCF Service from SQLCLR (by Niels Berglund) […]
April 3, 2009 at 2:38 pm |
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: 0x80131050)
System.IO.FileLoadException:
at System.ServiceModel.UpnEndpointIdentity..ctor(String upnName)
at System.ServiceModel.EndpointIdentity.CreateUpnIdentity(String upnName)
July 17, 2009 at 6:10 am |
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
August 14, 2009 at 7:49 am |
[…] 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 – […]
September 5, 2010 at 9:00 pm |
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.
October 28, 2010 at 3:29 pm |
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]
November 2, 2010 at 6:40 pm |
[…] 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 […]
November 2, 2010 at 6:42 pm |
[…] 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 – […]
December 6, 2010 at 5:29 pm |
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.
December 29, 2010 at 11:00 pm |
You can find another example of this at the following:
http://www.codeproject.com/KB/database/WCFFromSQL.aspx?msg=3713879#xx3713879xx
March 13, 2011 at 10:20 pm |
[…] the issue for the production SQL Server! 2. For more information, please, see the link https://nielsb.wordpress.com/sqlclrwcf/ 3. Copy files: >copy “WindowsMicrosoft.netFrameworkv3.0Windows Communication […]
March 1, 2013 at 1:38 am |
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
March 24, 2013 at 12:24 pm |
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.
April 30, 2013 at 8:28 am |
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!
May 8, 2013 at 10:11 pm |
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.
May 10, 2013 at 3:10 am |
If you desire to grow your knowledge only
keep visiting this web site and be updated with the newest
news update posted here.
May 11, 2013 at 5:54 am |
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!
July 3, 2013 at 5:42 am |
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!
August 18, 2013 at 3:27 pm |
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!
May 24, 2016 at 9:14 pm |
Great Article; very helpful.
Thanks!
March 30, 2021 at 4:02 am |
You are a stickler then. I used to be one. Now I deliberately pace the movie and book apart. For instance Call Me By Your Name worked because of that. And there are books I have discovered via movies so can’t complain. I can feel sleep coming so good night 🙂