Last post Dec 08, 2009 10:10 AM by french_duke
Dec 07, 2009 01:48 PM|french_duke|LINK
I have created a CLR UDF that calls a web service (a postcode lookup service, but that's besides the point), and when I try to run the function I get the error in the thread title.
The database that I'm deploying the CLR UDF to is a dedicated server, to which I have RDP access as the administrator.
To write and deploy the UDF, I followed the following tutorial post:
I'm no CAS expert, but I think that the problem is the account that the SQL process uses does not have rights to call the web service method.
Can anyone help? Any advice would be greatly appreciated!
Dec 08, 2009 03:02 AM|thuhue|LINK
Could you post a exact copy of the error message?
Dec 08, 2009 08:00 AM|french_duke|LINK
Here is the full stack trace. I hope it helps...
Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user defined routine or aggregate 'GetRegionByPostcode':
System.Security.SecurityException: Request for the permission of type 'System.Net.WebPermission, System, Version=184.108.40.206, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
at System.Net.HttpWebRequest..ctor(Uri uri, ServicePoint servicePoint)
at System.Net.HttpRequestCreator.Create(Uri Uri)
at System.Net.WebRequest.Create(Uri requestUri, Boolean useUriBase)
at System.Net.WebRequest.Create(Uri requestUri)
at System.Web.Services.Protocols.WebClientProtocol.GetWebRequest(Uri uri)
at System.Web.Services.Protocols.HttpWebClientProtocol.GetWebRequest(Uri uri)
at System.Web.Services.Protocols.SoapHttpClientProtocol.GetWebRequest(Uri uri)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object parameters)
at MyCLRUDFProject.PostcodeAnywhere.LookupUK.ByPostcode(String Postcode, String AccountCode, String LicenseKey, String MachineId)
at UserDefinedFunctions.GetRegionByPostcode(String postcode)
As you can see, my UDF (GetRegionByPostcode()) is calling a web service from PostcodeAnywhere.com (the specific method is ByPostcode()). The aim is to fill in some missing region information for legacy users and have a method of populating the same info
for new users.
Incidentally, when I take the code in my compiled UDF and write a normal method in the code-behind on one of my website's pages, it does work - so I know the problem is not with the method itself.
Dec 08, 2009 08:29 AM|french_duke|LINK
Okay guys - quick update.
It appears I have created the problem by changing the 'Permission Level' of the CLR UDF project to SAFE. The reason I changed this was because this was the only way it would deploy. It wasn't until reading more
that I realised assemblies with SAFE permission levels cannot access resources outside of the server.
So, I'm guessing I have a new problem now. When I try to deploy using the EXTERNAL permission level, I get the following error:
EXTERNAL ACCESS ASSEMBLY permission denied on object 'server', database 'master'.
Dec 08, 2009 10:10 AM|french_duke|LINK
Okay - I'm in world of pain with this but the problem is different to the one I originally posted so I'm going to open a new thread.