SQL Server – Permission Error Executing CLR Stored Procedure

csql serversql-server-2016stored-procedurest-sql

I've created a stored procedure using SQL CLR c# stored procedure in Visual Studio 2017 and I've published it in MSSQL Server. Now I can see my stored procedure, but I face this error when I want to execute the procedure.
The error is:

Msg 6522, Level 16, State 1, Procedure cust_diff, Line 0 [Batch Start
Line 2] A .NET Framework error occurred during execution of
user-defined routine or aggregate "cust_diff":
System.Security.SecurityException:
System.Security.Permissions.SecurityPermission
System.Security.SecurityException: at LevenshteinDistance.cust_diff()

I've added these two at the beginning of my code in C#

using System.Security;
using System.Security.Permissions;

and also this is part of my code that I use to create the procedure:

 [Microsoft.SqlServer.Server.SqlProcedure]
 [PermissionSet(SecurityAction.Assert, Unrestricted = true)]
 public static void cust_diff()
 {

    // Put your code here
    LevenshteinDistance ld = new LevenshteinDistance();
    ld.Method1();
 }

but despite using these two, I cannot execute my procedure in SSMS.

Best Answer

This setting must be enabled on the database in order to allow CLR procedures to run. Query this setting which should show a value of 1

SELECT name,
CAST(value as int) as value_configured,
CAST(value_in_use as int) as value_in_use
FROM sys.configurations
WHERE name ='clr enabled';

If it is not then using a login with dba privileges run this

EXECUTE sp_configure 'clr enabled',1;
RECONFIGURE;

It would be advisable when creating the assembly to mark what access it needs. From the documentation an example

CREATE ASSEMBLY SQLCLRTest  
FROM 'C:\MyDBApp\SQLCLRTest.dll'  
WITH PERMISSION_SET = SAFE;

The choices of access are SAFE (most restrictive), EXTERNAL_ACCESS and UNSAFE (least restrictive). What to mark the assembly depends on what it accesses and where the resource is located.

If your assembly requires EXTERNAL or UNSAFE access such as the ability to access local data or native code then you are also required to do either of these:

  • the assembly is signed with a certificate and has a login with EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY permission. This is preferred as it restrict permissions to the lowest level: one user
  • database owner has EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY

From a security audit perspective using CIS standards CLR assemblies are always flagged for further review. This does not mean you should not do this, just that there should be no other way of accomplishing what is required.

Related Question