TSQL - CLR Permission Execution

Question

I got the sql procedure from the CLR (.net Assembly), which returns an error when executed

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 that you are trying to access an external resource in an assembly labeled SAFE . To access external resources, you need to set at least EXTERNAL_ACCESS (and in some cases UNSAFE ). However, looking at your code, you are simply trying to connect to a local instance, and in this case it is much easier (and faster) to do this: using "Context Connection = true;" as a ConnectionString.

A contextual connection is a direct connection to the current process / session, and is sometimes called a process connection. Benefits of using Context Connection:

• can be done in assemblies marked as SAFE
• access to local temporary objects (temporary tables and temporary procedures whose names begin with a single # instead of double ## )
• access to SET CONTEXT_INFO and CONTEXT_INFO()
• Unable to start the connection utility

also:

• Whether you use a contextual connection or a regular / external connection in the process, you do not need to formally request permission using SqlClientPermission
• you should always clear external resources by calling their Dispose() method. Not all objects have this, but SqlConnection , SqlCommand and SqlDataReader course. Typically, people wrap disposable objects in a using() block, since it is a compiler macro that extends to a try / finally structure that calls the Dispose() method in finally to ensure that it is called even if an error occurs.
• The Dispose() method for many / most disposable objects automatically handles the Close() call, so you usually don't need to explicitly call Close() .

Your code should look like this:

 [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 point to this. I am doing something very similar and I am getting the same error. Here is what I found, however b / c I do not have this level of access to the database, I can not test it.

The easiest (although not MSDN, it is recommended that you simply run the CLR proc to run) - set the permission level to External_Access ...

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

The permission level is set on the CLR project property pages, the database tab - set the permission level - external, install the Owner-dbo assembly and run tsql 'ALTER DATABASE DataBaseName SET TRUSTWORTHY ON' This will do the work DONE! - and SmtpClient wiill works fine ... Then do it right and sign Assanbly with the strong name Key file ...

Full entry here ...

Answer 3

Have you installed your DB set in Trusrtworth ON and turned on clr?

try it

 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 can help you.