Accessing Sql FILESTREAM from within a CLR stored procedure

Question

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 read from the filestream using a simple console app. Here's some example code for the proc that fails:

[SqlProcedure]
public static void GetDataFromFileStream(string path, out int data)
{
    using (var connection = new SqlConnection("context connection=true"))
    {
        connection.Open();

        var transaction = connection.BeginTransaction();

        var transactionContext = GetTransactionContext(connection, transaction);

        // the following line throws an exception
        var sqlFileStream = new SqlFileStream(path, transactionContext, FileAccess.Read);

        var buffer = new byte[4];
        sqlFileStream.Read(buffer, 0, 4);

        data = BitConverter.ToInt32(buffer, 0);
    }
}

private static byte[] GetTransactionContext(SqlConnection connection, SqlTransaction transaction)
{
    using (var cmd = connection.CreateCommand())
    {
        const string myGetTxContextQuery = "SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()";
        cmd.CommandText = myGetTxContextQuery;
        cmd.CommandTimeout = 60;
        cmd.CommandType = CommandType.Text;
        cmd.Transaction = transaction;
        return (byte[])cmd.ExecuteScalar();
    }
}

An exception is thrown when trying to construct the SqlFileStream instance:

System.ComponentModel.Win32Exception occurred Message="The request is not supported" Source="System.Data" ErrorCode=-2147467259 NativeErrorCode=50

Anyone know what I'm doing wrong?

Response to answer 1: Can't use "context connection=true" with impersonation (http://blogs.msdn.com/dataaccess/archive/2006/01/25/517495.aspx). It failed with InvalidOperationException when opening connection ("Data access is not allowed in this context. Either the context is a function or method not marked with DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain data from FillRow method of a Table Valued Function, or is a UDT validation method"). If I change the connection string to specify data source with integrated security I get the same error I had before. — pete757, Sep 02 '09 at 10:42

score 0 · Answer 1 · answered Sep 01 '09 at 17:18

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

score 0 · Answer 2 · answered Nov 30 '09 at 00:40

0

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.

answered Nov 30 '09 at 00:40

Pawel Marciniak

2,208
14
17

Yes, the assembly is using the EXTERNAL_ACCESS permission. Unfortunately I've had no time to investigate further, but plan to do so sometime soon. I will report back with any progress I make. – pete757 Dec 08 '09 at 11:30

score 0 · Accepted Answer · answered Feb 25 '10 at 11:25

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,
        @pOffsetIntoData    INT,
        @pLengthOfData      INT,
        @pData              VARBINARY(MAX) OUTPUT
    )
    AS
    BEGIN;
        SELECT @pData  = SUBSTRING(ValueData, @pOffsetIntoData, @pLengthOfData)
          FROM [MESL].DataStream
         WHERE DataStreamGUID = @pfilestreamGUID;
    END;

Accessing Sql FILESTREAM from within a CLR stored procedure

3 Answers3

Linked