ovule
asked on
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.SecurityEx ception: Request for the permission of type 'System.Net.WebPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c56193 4e089' failed.
System.Security.SecurityEx ception:
at System.Security.CodeAccess SecurityEn gine.Check (Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.CodeAccess Permission .Demand()
at System.Net.HttpWebRequest. .ctor(Uri uri, ServicePoint servicePoint)
at System.Net.HttpRequestCrea tor.Create (Uri Uri)
at System.Net.WebRequest.Crea te(Uri requestUri, Boolean useUriBase)
at System.Net.WebRequest.Crea te(Uri requestUri)
at System.Xml.XmlDownloadMana ger.GetNon FileStream (Uri uri, ICredentials credentials)
at System.Xml.XmlDownloadMana ger.GetStr eam(Uri uri, ICredentials credentials)
at System.Xml.XmlUrlResolver. GetEntity( Uri absoluteUri, String role, Type ofObjectToReturn)
at System.Xml.XmlTextReaderIm pl.OpenUrl Delegate(O bject xmlResolver)
at System.Threading.Compresse dStack.run TryCode(Ob ject userData)
at System.Runtime.CompilerSer vices.Runt imeHelpers .ExecuteCo deWithGuar anteedClea nup(TryCod e code, CleanupCode backoutCode, Object userData)
at System.Threading.Compresse dStack.Run (Compresse dStack compressedStack, ContextCallback callback, Object state)
at System.Xml.XmlTextReaderIm pl.OpenUrl ()
at System.Xml.XmlTextReaderIm pl.Read()
at System.Xml.XmlLoader.Load( XmlDocumen t doc, XmlReader reader, Boolean preserveWhitespace)
at System.Xml.XmlDocument.Loa d(XmlReade r reader)
at System.Xml.XmlDocument.Loa d(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.
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.SecurityEx
System.Security.SecurityEx
at System.Security.CodeAccess
at System.Security.CodeAccess
at System.Net.HttpWebRequest.
at System.Net.HttpRequestCrea
at System.Net.WebRequest.Crea
at System.Net.WebRequest.Crea
at System.Xml.XmlDownloadMana
at System.Xml.XmlDownloadMana
at System.Xml.XmlUrlResolver.
at System.Xml.XmlTextReaderIm
at System.Threading.Compresse
at System.Runtime.CompilerSer
at System.Threading.Compresse
at System.Xml.XmlTextReaderIm
at System.Xml.XmlTextReaderIm
at System.Xml.XmlLoader.Load(
at System.Xml.XmlDocument.Loa
at System.Xml.XmlDocument.Loa
...
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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