• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1454
  • Last Modified:

SQL Server 2005 Assembly WebPermission Issue

I have an assembly which reads an XML file from the Internet, parses it and updates a MS SQL Server 2005 database.  This works perfectly if run standalone.

My problems arise after I register this assembly with SQL Server and then execute a call to one of its methods through some CLR stored procedure.  The exception I am receiving is:

Msg 6522, Level 16, State 1, Procedure UpdateStarter, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'UpdateStarter':
System.Security.SecurityException: Request for the permission of type 'System.Net.WebPermission, System, 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.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.Xml.XmlDownloadManager.GetNonFileStream(Uri uri, ICredentials credentials)
   at System.Xml.XmlDownloadManager.GetStream(Uri uri, ICredentials credentials)
   at System.Xml.XmlUrlResolver.GetEntity(Uri absoluteUri, String role, Type ofObjectToReturn)
   at System.Xml.XmlTextReaderImpl.OpenUrlDelegate(Object xmlResolver)
   at System.Threading.CompressedStack.runTryCode(Object userData)
   at System.Runtime.CompilerServices.RuntimeHelpers.ExecuteCodeWithGuaranteedCleanup(TryCode code, CleanupCode backoutCode, Object userData)
   at System.Threading.CompressedStack.Run(CompressedStack compressedStack, ContextCallback callback, Object state)
   at System.Xml.XmlTextReaderImpl.OpenUrl()
   at System.Xml.XmlTextReaderImpl.Read()
   at System.Xml.XmlLoader.Load(XmlDocument doc, XmlReader reader, Boolean preserveWhitespace)
   at System.Xml.XmlDocument.Load(XmlReader reader)
   at System.Xml.XmlDocument.Load(String filename)
  ...

I realise this is due to lack of permissions for connecting to the Internet, however do not know how to grant SQL Server permissions to do this, or do something else with the assembly to make it appear safe.

Your help is very appreciated.
0
ovule
Asked:
ovule
1 Solution
 
adrpoCommented:

From here:
http://searchsqlserver.techtarget.com/generic/0,295582,sid87_gci1251402,00.html

Setting the stored procedure security:
At this point the code is finished for the stored procedure, but because of security concerns, it still can't execute. By default SQL Server CLR objects can only access database resources, and they cannot access external resources. In the case of the usp_ImportFile example, the stored procedure needs to access the file system, so the default security settings need to be changed. To enable external access, you need to open the project's properties and click the Database tab. Then in the Permissions Level drop-down you need to change the value from Safe to External.


Cheers,
za-k/
0
 
ovuleAuthor Commented:
Thanks adrpo!

Before posting this I tried the following, with both EXTERNAL_ACCESS and UNSAFE permissions through Management Studio - I didn't realise you had to do it through VS

CREATE ASSEMBLY MyAssembly
from 'M:\MyPath\MyAssembly.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now