TSQL - Executing CLR Permission

Question

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();
        }
    }
}

Answer 1

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 least EXTERNAL_ACCESS (and in some cases UNSAFE). 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:

• can be done in Assemblies marked as SAFE
• access to local temporary objects (temp tables and temp procedures, both with names starting with a single # instead of double ##)
• access to SET CONTEXT_INFO and CONTEXT_INFO()
• no connection startup overhead

Also:

• whether you use the in-process, Context Connection or a regular / external connection, you do not need to formally request permission using SqlClientPermission
• you should always clean up external resources by calling their Dispose() method. Not all objects have this, but SqlConnection, SqlCommand, and SqlDataReader certainly do. It is typical for people to wrap disposable objects in a using() block as it is a compiler macro that expands to a try / finally structure that calls the Dispose() method in the finally to ensure that it is called, even if an error occurs.
• The Dispose() method of many / most disposable objects automatically handles the call to Close() so you usually do not need to call Close() explicitly.

Your code should look as follows:

[Microsoft.SqlServer.Server.SqlProcedure]
public static void HelloWorld()
{
  using (SqlConnection sqlcon = new SqlConnection("Context Connection = true;")
  {
    using (SqlCommand sqlcmd = new SqlCommand("SELECT Top 1 * FROM ItemData.dbo.Item",
               sqlcon))
    {
      sqlcon.Open();

      using (SqlDataReader reader = sqlcmd.ExecuteReader())
      {
        SqlContext.Pipe.Send(reader);
      }
    }
  }
}

Answer 2

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.

Easiest( although not MSDN recommended just to jet a CLR proc to run) is to set the permission level to External_Access...

SQL Server Host Policy Level Permission Sets The set of code access security permissions granted to assemblies by the SQL Server host policy level is determined by the permission set specified when creating the assembly. There are three permission sets: SAFE, EXTERNAL_ACCESS and UNSAFE.

The permision level is set on the properties pages of the CLR project , database tab - set Permission Level-External, set Aassembly Owner-dbo, and run tsql 'ALTER DATABASE DataBaseName SET TRUSTWORTHY ON' This will get the job DONE! - and the SmtpClient wiill work ok... Then do it right and Sign the Assenbly with a Strong name Key file...

Full Post Here...

Answer 3

Have you set your DB set to Trusrtworth ON and enabled clr?

Try this

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

ALTER DATABASE [YourDatabase] SET TRUSTWORTHY ON
GO

I have a guide here on how to use CLR Stored Procedures that might help.