.Net Function call in SQL 2005

Question

I am getting the following error when I execute a .Net Function in SQL 2005, any ideas?

Msg 6522, Level 16, State 2, Line 1 A .NET Framework error occurred during execution of user defined routine or aggregate 'Function1': 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.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 SqlServerProject1.navision.NavisionLink.GetPONumber(String Database) at UserDefinedFunctions.Function1()

Answer 1

it seem you don't have enough security access?

but would be easier if you could post the code related to that error

Answer 2

using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions { [Microsoft.SqlServer.Server.SqlFunction] public static SqlString Function1() { SqlServerProject1.navision.NavisionLink po = new SqlServerProject1.navision.NavisionLink();

    string test = Convert.ToString(po.GetPONumber("NavisionDev"));
    // Put your code here
    return new SqlString(test);
}

};

Answer 3

You need to set some security Informations for running you .NET code on the SQLServer.

Pls refer to CodeProject to find the infos you will need.

Answer 4

You are not creating the assembly with the appropriate permission set. Your CREATE ASSEMBLY statement should look something like this:

CREATE ASSEMBLY ... WITH PERMISSION_SET = EXTERNAL_ACCESS

This should allow you to make the web service call. If that doesn't work, then you will have to set PERMISSION_SET to UNSAFE.