SQL CLR Stored Procedure and Web Service

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.

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?

回答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.

回答2:

You should check my blog post

This is Turkish but you can translate with google tool on the page. Just imagine the methods in the Agent class will call your web service. And use your System.Web dependencies instead of System.Management in example.

回答3:

Hope to check this blog entry which will help you to invoke a web service from SQL stored procedure and overcome security issues you encountered.