SQL CLR Stored Procedure and Web Service

Question

I am current working on a task in which I am needing to call a method in a web service from a CLR stored procedure.

A bit of background:

Basically, I have a task that requires ALOT of crunching. If done strictly in SQL, it takes somewhere around 30-45 mins to process. If I pull the same process into code, I can get it complete in seconds due to being able to optimize the processing so much more efficiently. The only problem is that I have to have this process set as an automated task in SQL Server.

In that vein, I have exposed the process as a web service (I use it for other things as well) and want the SQL CLR sproc to consume the service and execute the code. This allows me to have my automated task.

The problem:

I have read quite a few different topics regarding how to consume a web service in a CLR Sproc and have done so effectivly. Here is an example of what I have followed.

http://blog.hoegaerden.be/2008/11/11/calling-a-web-service-from-sql-server-2005/

I can get this example working without any issues. However, whenever I pair this process w/ a Web Service method that involves a database call, I get the following exceptions (depending upon whether or not I wrap in a try / catch):

Msg 10312, Level 16, State 49, Procedure usp_CLRRunDirectSimulationAndWriteResults, Line 0 .NET Framework execution was aborted. The UDP/UDF/UDT did not revert thread token.

or

Msg 6522, Level 16, State 1, Procedure MyStoredProc , Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'MyStoredProc':

System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.EnvironmentPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

System.Security.SecurityException:

at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)

at System.Security.CodeAccessPermission.Demand()

at System.Net.CredentialCache.get_DefaultCredentials()

at System.Web.Services.Protocols.WebClientProtocol.set_UseDefaultCredentials(Boolean value)

at MyStoredProc.localhost.MPWebService.set_UseDefaultCredentials(Boolean Value)

at MyStoredProclocalhost.MPWebService..ctor()

at MyStoredProc.StoredProcedures.MyStoredProc(String FromPostCode, String ToPostCode)

I am sure this is a permission issue, but I can't, for the life of me get it working. I have attempted using impersonation in the CLR sproc and a few other things. Any suggestions? What am I missing?

Answer 1

I am just figuring this out, but this is what I got:

You need to set the permission_set when you create the assembly in SQL to UNSAFE.

CREATE ASSEMBLY [<assemblyname>] FROM '<path>/<assemblyname>.dll'
WITH PERMISSION_SET = UNSAFE
GO

If you are using a VS SQL Database Project to do your deployment, this can also be done by setting the Permission to UNSAFE on the Database Tab of Properties for the Database Project.

Answer 2

I have my CLR procedure consuming my web service just fine but what if i'm on a network that requires the webservice to ask for a certificate? How would I handle that?