CODEWITHSUNDEEP
c#sql-servert-sqlsqlclr
shmnff
shmnff

Reputation: 739

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

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?

Upvotes: 0

Views: 128

Answers (1)

Solomon Rutzky
Solomon Rutzky

Reputation: 48826

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.

Upvotes: 1

Related Questions