TSQL: execution of CLR stored function throws System.Security.SecurityException error

Question

I need to pack files into rar archive inside TSQL script. So I compiled dll from following C#-script function:

using System;
using System.Diagnostics;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction()]
    static public SqlInt32 RarFiles(SqlString WORK, SqlString TARGET, SqlString SRC, SqlString SIZE)
    {
        ProcessStartInfo p = new ProcessStartInfo();
        p.FileName = @"C:\Program Files\WinRAR\rar.exe";
        p.Arguments = String.Format
        (
            "a -cfg- -ep1 -idcdp -m5 -r -s -v{0} {1} {2}",
            SIZE.ToString(),
            TARGET.ToString(),
            SRC.ToString()
        );
        p.WorkingDirectory = WORK.ToString();
        Process x = Process.Start(p);
        return x.ExitCode;
    }
}

with the command:

%SYSTEMROOT%\Microsoft.NET\Framework64\v2.0.50727\csc.exe /target:library c:\test\rarfiles.cs

Then in TSQL I created assembly with code:

ALTER AUTHORIZATION ON DATABASE::[Test] TO [sa]; 
GO
ALTER DATABASE [Test] SET TRUSTWORTHY ON
GO
CREATE ASSEMBLY [CLRFunctions]
FROM 'C:\test\RarFiles.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO
CREATE FUNCTION [dbo].RarFilesCLR
(
    @work [nvarchar](max),
    @target [nvarchar](max),
    @source [nvarchar](max),
    @size [nvarchar](max)
)
RETURNS INT
AS EXTERNAL NAME CLRFunctions.UserDefinedFunctions.RarFiles;
GO

and finally, I'm trying to execute function:

DECLARE @work nvarchar(max) = 'c:\test';
DECLARE @target nvarchar(max) = 'c:\test\res.rar';
DECLARE @source nvarchar(max) = 'c:\test\source';
DECLARE @size nvarchar(max) = '20M';
SELECT [dbo].RarFilesCLR(@work, @target, @source, @size);

that throws an error

System.Security.SecurityException: 
in UserDefinedFunctions.RarFiles(SqlString WORK, SqlString TARGET, SqlString SRC, SqlString SIZE)`...

Can somebody explain to me what's wrong here?

Have your (sql server service) account rights for path where should be located WinRAR.exe, source file and rights for destination? — Deadsheep39
– Deadsheep39, Commented Nov 18, 2017 at 13:02
@Deadsheep39 I'm not sure. But I'm testing code on my own laptop, so I hope that all I'm doing is under admin permissions... — shmnff
– shmnff, Commented Nov 18, 2017 at 13:24
Please update with the full error message as it will help others when searching. Thanks. — Solomon Rutzky
– Solomon Rutzky, Commented Nov 18, 2017 at 13:43

Solomon Rutzky · Accepted Answer · 2017-11-18 13:30:07Z

1

You need to use UNSAFE for the Permission Set when creating the Assembly. This is required by you starting a new Process.

Also, you should post the entire error message as it often has clues for what is going on and / or what to do. Only posting the first tiny piece of the error message makes it harder to get the help you are requesting.

answered Nov 18, 2017 at 13:30

Solomon Rutzky

49.3k11 gold badges141 silver badges184 bronze badges

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

1 Comment

Solomon Rutzky Over a year ago

Correct. Starting a new process is multithreading, which is why it is UNSAFE instead of just EXTERNAL_ACCESS.

Collectives™ on Stack Overflow

TSQL: execution of CLR stored function throws System.Security.SecurityException error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related