Geek Answers Handbook

Stored Procedure and SQL CLR Web Service

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

A bit of background:

Basically, I have a task requiring multiple crunching. If this is done strictly in SQL, it takes about 30-45 minutes to process. If I endure the same process in the code, I can get it in seconds because of the possibility to optimize processing more efficiently. The only problem is that I have to set this process as an automatic task in SQL Server.

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

Problem:

I read several different topics on how to use the web service in CLR Sproc and did it spectacularly. Here is an example of what I followed.

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

I can get this example to work without any problems. However, whenever I connect this process to a Web service method that involves calling the database, I get the following exceptions (depending on whether I complete the attempt / catch):

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

or

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

.NET Framework error while executing a user-defined routine or "MyStoredProc" collection:

System.Security.SecurityException: A permission request of type 'System.Security.Permissions.EnvironmentPermission, mscorlib, Version = 2.0.0.0, Culture = Neutral, PublicKeyToken = b77a5c561934e089' failed.

System.Security.SecurityException:

in System.Security.CodeAccessSecurityEngine.Check (object request, StackCrawlMark & ​​stackMark, Boolean isPermSet)

in System.Security.CodeAccessPermission.Demand ()

in System.Net.CredentialCache.get_DefaultCredentials ()

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

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

in MyStoredProclocalhost.MPWebService..ctor ()

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

I am sure that this is a problem with resolution, but I cannot, because life makes me work. I tried using impersonation in CLR sproc and a few other things. Any suggestions? What am I missing?

+4
source share
3 answers

I just understand this, but this is what I got:

You need to set the resolution when creating the assembly in SQL for UNSAFE.

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

If you are using the VS SQL Database Project for deployment, you can also do this by setting UNSAFE permission to the Database tab of the properties for the database project.

+2
source

You must check my blog post.

It is Turkish, but you can translate it using the google tool on the page. Imagine the methods in the Agent class invoking your web service. And use your System.Web dependencies instead of System.Management in the example.

0
source

We hope to check out this blog post to help you call the web service from the SQL stored procedure and fix the security issues you are having.

-1
source

More articles:


All Articles