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

Solomon Rutzky · Accepted Answer · 2018-09-12 20:55:17Z

0

[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:

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

SQLCLR.org

edited Sep 12, 2018 at 20:55

answered Sep 12, 2018 at 20:39

Solomon Rutzky

49.3k11 gold badges141 silver badges184 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Coreysan Over a year ago

Brilliant. Thanks so much for the guidance!

Coreysan Over a year ago

Solomon - I wanted to give you an upvote, but apparently I'm too new to StackOverflow to allow it. I hope the comment serves to help. You're one of the few who actually answered the question instead of asking for more useless details!

Solomon Rutzky Over a year ago

@Coreysan Thanks, is ok. Yeah, I think you need 20 pts in order to vote. And again, you are welcome. Of course, I don't see that anyone has asked for any details on the question. Either way, glad the info helped out :)

Collectives™ on Stack Overflow

SecurityException error when calling CLR function "Revisited"

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related