My diary of software development

Posts tagged ‘UDF’

Calling a WSE web service from a SQL Server CLR UDF

My current project is to document and lay the foundation for communicating with web services from several different clients. Well, that’s pretty much a common activity for most developers today but the twist here is that the web services are implemented with SAS Analytics Server v9.2 and one of the clients will be SQL Server 2005. 
 
I set about putting up a lab to work in consisting of a SQL 05 virtual machine, a SAS 9.2 virtual machine, and my development workstation. Writing a web service client in SQL Server is fairly straightforward: just write a CLR user defined function and install it. Here are some good articles on that:
http://www.databasejournal.com/features/mssql/article.php/3821271/article.htm
http://www.simple-talk.com/sql/t-sql-programming/practical-sql-server-2005-clr-assemblies/.
 
 
Implementing web services in SAS is a challenge and I’ve not gotten completely through it yet but I do know that SAS web services are secured using WSE 3.0.  So, while waiting on SAS support to help me get the SAS server up and going, I figured I would write a WSE secured service in .Net and work through the issues of calling in from a SQL UDF. Wahoo! I thought, as I’ve pretty much forgotten how to work with WSE since I picked up WCF. But 1/2 day later I found that web services are like remembering how to ride bikes and learning to ride that multi-wheeled, multi-geared contraption of spokes, levers, arms, and seats out of a Dr. Suess book called WCF didn’t make me forget how to ride my WSE road bike I keep in the garage.
 
I quickly wrote and deployed an ASMX web service secured with WCF and wrote a simple .Net console client which communicated with it. Great, I thought, now all I have to do is lift my code from the console app and drop it into a SQL UDF and I’ll be done. Alas, I found that a SQL UDF cannot have an associated WSE policy configuration file. You see, normally a WSE client has the luxury of a policy configuration file (by default named wse3PolicyCache.config) to maintain the necessary policies and their assertions for calling the service. However a SQL UDF comes packaged as an assembly .dll so there can be no configuration files associated with it.
 
When I first began to work through this problem of no configuration file, I thought there would be 2 paths I could take: creating the policies found in the configuration file in code or using the ConfigurationManager class to build a placebo of my console app’s physical configuration file in memory. I tried the second path first and quickly realized that it just wasn’t possible. So next I began to research the first route and stumbled across an MSDN article that did exactly what I wanted – http://msdn.microsoft.com/en-us/library/aa528807.aspx. After the heavanly light dimmed back down to normal and the choirs of angels stopped singing, I began to implement it and now I have a CLR UDF in my SQL Server which can call a WSE secured web service.
Advertisements