SecurityException error when calling CLR function "Revisited"

Question

I am running SQL Server locally only. I am building a project with VS 2017 community. I created a CLR procedure, and uploaded it as an assembly successfully. The class and the method are public. I created a SQL standard proc to call the clr proc with permissions_set = external_access. Trustworthy is on. Alter authorization is set to sa.

All it does is execute a bat file: cmd.exe c:\temp\test.bat

But I'm still getting an error 6522 Security Exception.

When I run the method as a console app, it runs perfect. But in SQL Server, no go.

I'd be very grateful for any advice - this is my first attempt with CLR.

Code:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.Diagnostics;
using System.Text;


public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void RunTestBat()
    {
        ProcessStartInfo pPStartInfo = new ProcessStartInfo("cmd.exe");
        pPStartInfo.WorkingDirectory = @"C:\Temp";

        pPStartInfo.UseShellExecute = true;
        pPStartInfo.Arguments = string.Format(@" /C test.bat");

        Process p = new Process();
        p.StartInfo = pPStartInfo;        
        p.Start();
        p.WaitForExit();
    }
};

Answer 1

[SQLCLR] proc with permissions_set = external_access

That is the problem. You are using threading, which requires the PERMISSION_SET to be UNSAFE.

Also, while Visual Studio offers little help in this area, you generally should not set your database's to TRUSTWORTHY ON. Yes, it is the simple fix for this annoying problem, but it isn't a good / long-term fix, especially for production. There is a way (two ways, actually) to set things up correctly to use an Asymmetric Key / Strong Name Key or Certificate to handle the security. Both approaches can be made to work within the structure of Visual Studio / SQL Server Data Tools (SSDT). Some info here (an answer of mine here on S.O.):

CLR Strict Security on SQL Server 2017

And two blog posts of mine, each dealing with a different approach:

• SQLCLR vs. SQL Server 2017, Part 2: “CLR strict security” – Solution 1
• SQLCLR vs. SQL Server 2017, Part 3: “CLR strict security” – Solution 2

For more info on working with SQLCLR in general, please visit:

SQLCLR.org