I got a sql procedure from a CLR (.net Assembly) that when executed returns an error
Msg 6522, Level 16, State 1, Procedure sp_HelloWorld, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'sp_HelloWorld':
System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, 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.PermissionSet.Demand()
at System.Data.Common.DbConnectionOptions.DemandPermission()
at System.Data.SqlClient.SqlConnection.PermissionDemand()
at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at HelloWorld.SQLCLR.HelloWorld()
This is my SQL script
go
drop procedure HelloWorld
drop assembly HelloWorld
GO
create assembly HelloWorld from 'F:\HelloWorld.dll'
with permission_set = safe
Go
create procedure sp_HelloWorld
as external name HelloWorld.[HelloWorld.SQLCLR].HelloWorld
go
exec sp_HelloWorld
and this is my Class (Assembly)
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.Security.Permissions;
using System.Data;
namespace HelloWorld
{
public class SQLCLR
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void HelloWorld()
{
string connectString1 = @"Data Source=localhost;Initial Catalog=ItemData;Integrated Security=True";
SqlClientPermission permission = new SqlClientPermission(PermissionState.None);
permission.Add(connectString1, "", KeyRestrictionBehavior.AllowOnly);
permission.PermitOnly();
SqlConnection sqlcon = new SqlConnection(connectString1);
sqlcon.Open();
SqlCommand sqlcmd = new SqlCommand("SELECT Top 1 * FROM ItemData.dbo.Item", sqlcon);
SqlDataReader reader = sqlcmd.ExecuteReader();
SqlContext.Pipe.Send(reader);
sqlcon.Close();
}
}
}
I just wanted to add my two sense to this. I'm doing something very similiar and I'm getting the same error. Here is what I found, however b/c I don't have this level of access to the DB I can't test it.
Full Post Here...
Have you set your DB set to Trusrtworth ON and enabled clr?
Try this
I have a guide here on how to use CLR Stored Procedures that might help.
The problem is simply that you are attempting to access an external resource in an Assembly that is marked as
SAFE
. Accessing external resources requires setting the Assembly to at leastEXTERNAL_ACCESS
(and in some casesUNSAFE
). However, looking at your code, you are simply trying to connect to the local instance, and in that case there is a far easier (and faster) means of doing this: using"Context Connection = true;"
as the ConnectionString.The Context Connection is a direct connection to the current process / session, and it is sometimes referred to as the in-process connection. The benefits of using the Context Connection are:
SAFE
#
instead of double##
)SET CONTEXT_INFO
andCONTEXT_INFO()
Also:
SqlClientPermission
Dispose()
method. Not all objects have this, butSqlConnection
,SqlCommand
, andSqlDataReader
certainly do. It is typical for people to wrap disposable objects in ausing()
block as it is a compiler macro that expands to a try / finally structure that calls theDispose()
method in thefinally
to ensure that it is called, even if an error occurs.Dispose()
method of many / most disposable objects automatically handles the call toClose()
so you usually do not need to callClose()
explicitly.Your code should look as follows: