Accessing Sql Filestream From Within A Clr Stored Procedure

I'm trying to access a Sql filestream from a CLR stored procedure. I've set up a very simple database with a single table which includes a filestream column. I can successfully rea

Solution 1:

You need to elevate permissions by impersonating the person who's executing the query.

Here is a good example of doing the impersonation:

http://drowningintechnicaldebt.com/blogs/shawnweisfeld/archive/2009/06/11/sql-clr-query-the-file-system-to-get-a-list-of-folders.aspx

Solution 2:

Did you use WITH PERMISSION_SET = EXTERNAL_ACCESS when creating the assembly in SQL Server? By default CREATE ASSEMBLY uses SAFE permission set, which doesn't include FileIOPermissions, required by SqlFileStream constructor.

Solution 3:

I was unable to use SqlFileStream to access the filestreams directly from within the CLR (because of the problems identified above). The solution I eventually adopted was to use a SQL stored procedure to get hold of the subset of filestream data I needed. Although this is not particularly efficient in some scenarios, it sufficed for my application

CREATE PROC ReadFromFilestream
    (
        @pfilestreamGUID    UNIQUEIDENTIFIER,
        @pOffsetIntoDataINT,
        @pLengthOfDataINT,
        @pDataVARBINARY(MAX) OUTPUT
    )
    ASBEGIN;
        SELECT@pData=SUBSTRING(ValueData, @pOffsetIntoData, @pLengthOfData)
          FROM [MESL].DataStream
         WHERE DataStreamGUID =@pfilestreamGUID;
    END;