Calling a WCF Service from a SQL Server 2005 CLR User-Defined Function

On my current project I developed a Membership Service that consolidates all user identities into a single authority, allowing for things like enterprise single sign-on, impersonation, and centralized auditing.  Now that we're ready to convert our users over from the previous system, we need to register them all through the service interface.  Since the existing user data will be staged to a SQL Server, it was appropriate to give that import access to the service.

Enter a user-defined function.  Adding a SQL Server Project to my Membership Service solution, I quickly wrapped the call to the RegisterUser web method in a SqlFunction, i.e. a method tagged with the Microsoft.SqlServer.Server.SqlFunction attribute.  David Hayden has a good post on the steps necessary to get this right.  Here's an even more detailed discussion to get you going.   In addition to these two posts, this article covers how to impersonate the principal in calling your function; it also discusses how you can modify the proxy class to not require the UNSAFE permission set.  The SQLCLR team also has a great post on doing this.

All of these authors used wsdl.exe to generate the proxy class for the web service.  This is a fine approach, but I prefer the ease of adding a Web Service to my project.

Both approaches also used the sgen utility to generate an XML Serialization Assembly for the classes in the web service proxy that was created with wsdl.exe or adding a Web Reference.  This knowledge base article explains why this is absolutely necessary when hosting your proxy in SQLCLR.

There is an addition consideration with using wsdl.exe and/or Web Reference generated proxies against a WCF Service that uses the wsHttpBinding, highlighted here.  These proxies only support the basicHttpBinding, that is they don't support WS-Addressing as required by the wsHttpBinding.  So, if you got your sql clr function or stored procedure calling your WCF web service, but you are getting a web service timeout, check to make sure it is using a compatible binding.  You may also notice the System.ServiceModel.ActionMismatchAddressingException in the service log.