Security Issues in SQLCLR integration with Visual C#

Question

i am working on SQLCLR integration with Visual C# 2010, what i am trying to do is, i have a folder, which contains some images, my function, iterates thru the file in folder, and get the basic file info like its height width etc..

i created a dll from Visual C# 2010 and added as an assembly in Sql Server 2008, and also created function, but when i try to select function i gets the below error..

A .NET Framework error occurred during execution of user-defined routine or aggregate "fn_GetFiles":   
System.Security.SecurityException: Request failed.  
System.Security.SecurityException:   
   at UserDefinedFunctions.GetFileList(String FolderPath)  
   at UserDefinedFunctions.GetFileInfo(String folderPath)

below is my .net function..

[System.Security.Permissions.PermissionSet(System.Security.Permissions.SecurityAction.Demand, Name = "FullTrust")]  
public partial class UserDefinedFunctions  
{  
    [SqlFunction(FillRowMethodName = "FillRow")]  
    public static IEnumerable GetFileInfo(string folderPath)  
    {  
        // Put your code here
        return GetFileList(folderPath);
    }

    public static ArrayList GetFileList(string FolderPath)
    {
        ArrayList li = new ArrayList();
        foreach (String s in Directory.GetFiles(FolderPath, "*.jpg"))
        {
            FileInfo info = new FileInfo(s);
            object[] column = new object[3];
            column[0] = Path.GetFileName(s);
            column[1] = info.Length;
            column[2] = s;

            li.Add(column);
        }
        return li;
    }

    private static void FillRow(Object obj, out string filename, out string fileSize, out string filePath)
    {
        object[] row = (object[])obj;
        filename = (string)row[0];
        fileSize = (string)row[1];
        filePath = (string)row[2];
    }

};

here is how i created assembly in sql server..

CREATE assembly GetFileList from 'E:\NBM Sites\DontDelete\SampleCLRIntegration.dll' with permission_set = safe

and created a sql function like below.

ALTER FUNCTION fn_GetFiles   
(  
    @folderPath nvarchar(max)  
)  
RETURNS TABLE   
(  
    [filename] nvarchar(max),  
    fileSize nvarchar(max),  
    filePath nvarchar(max)  
)  
AS EXTERNAL NAME GetFileList.UserDefinedFunctions.GetFileInfo;  

and calling function like below.

select * from dbo.fn_GetFiles('E:\NBM Sites\DontDelete')

How can I fix this error?

Answer 1

You need "External Access" and not "SAFE" permission set. See:

http://msdn.microsoft.com/en-us/library/ms345106.aspx

Try:

 with permission_set = EXTERNAL_ACCESS