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