C# CLR User Defined Function SqlBytes - InvalidOperationException

I have been writing a sql clr udf that is called from a stored procedure to save an unknown file type (jpg, doc, pdf, etc) to the file system. The UFD accepts a param of type SqlBytes that is getting passed a varbinary(max) from the calling stored proc (this is the file blob). The problem is that in the context of the CLR UDF I cannot access the value property or even the read method of the SqlBytes file param as it is returning the below invalid exception error.

I have shortened my UDF to just highlight the problem mentioned. Any help would be much appreciated.

Thanks in Advance.

ERROR

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.

Stack Trace

at System.Data.SqlServer.Internal.ClrLevelContext.CheckSqlAccessReturnCode(SqlAccessApiReturnCode eRc)
   at System.Data.SqlServer.Internal.ClrLevelContext.XvarProxyRead(CClrXvarProxy* pXvarProxy, UInt64 iPosition, Byte* pbBuffer, UInt32 cbCount)
   at System.Data.SqlServer.Internal.ClrLevelContext.System.Data.SqlServer.Internal.IXvarProxyAccessor.XvarProxyRead(CClrXvarProxy* , UInt64 , Byte* , UInt32 )
   at System.Data.SqlServer.Internal.StreamOnBlobHandle.Read(Byte* pbBuffer, UInt64 offset, UInt32 count)
   at System.Data.SqlServer.Internal.XvarBlobStream.Read(Byte[] buffer, Int32 offset, Int32 count)
   at System.IO.BufferedStream.Read(Byte[] array, Int32 offset, Int32 count)
   at System.Data.SqlTypes.SqlBytes.Read(Int64 offset, Byte[] buffer, Int32 offsetInBuffer, Int32 count)
   at UserDefinedFunctions.SaveFileToFS(Sql开发者_StackOverflow社区Bytes file, String fileName, String fileExtension, String path)

CLR CODE

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.IO;
using System.Security.Permissions;
using System.Security.Principal;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{ 

    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read)]
    public static SqlString SaveFileToFS(SqlBytes file)
    {
        WindowsImpersonationContext newContext = null;
        WindowsIdentity newIdentity = SqlContext.WindowsIdentity;
        try
        {           
            if (newIdentity != null) newContext = newIdentity.Impersonate();

            byte[] buffer = new byte[8040 * 4];
            long offset = 0;
            long read = 0;

            //This file.Read will throw an error
            read = file.Read(offset, buffer, 0, buffer.Length);

            //this line will throw the same error
            buffer = (byte[])file.Value;

        catch (System.Exception ex1)
        {
            throw ex1;

        }
        finally
        {
            if (newContext != null) newContext.Undo();
        }
        return new SqlString("Success");
    }
};

So just to complete this thread - here is the basic POC code for a CLR UDF that accepts a varbinary(max) file blob, the file name, file extension and path to write to and then saves it to the defined file system location (providing it has the appropriate filesystem permissions). Hope it helps someone :-)

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.IO;
using System.Security.Permissions;
using System.Security.Principal;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{


    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read , SystemDataAccess = SystemDataAccessKind.Read )]
    public static SqlString SaveFileToFS(SqlBytes file, string fileName, string fileExtension, string path)
    {

        WindowsImpersonationContext newContext = null;
        WindowsIdentity newIdentity = SqlContext.WindowsIdentity;
        try
        {

            long length = file.Length ;
            byte[] buffer = file.Value;
            long offset = 0;
            long read = 0;
            int times = 0;

            if (newIdentity != null) newContext = newIdentity.Impersonate();

            FileStream fs = new FileStream(path + fileName + fileExtension, System.IO.FileMode.Create, System.IO.FileAccess.Write);
            while (length > 1000)
            {
                fs.Write(buffer, 1000 * times, 1000);          

                length -= 1000;
                times++;
            }
            fs.Write(buffer, 1000 * times, (int)length);

            fs.Close();

        }
        catch (System.Exception ex1)
        {
            throw ex1;

        }
        finally
        {
            if (newContext != null) newContext.Undo();
        }
        return new SqlString(string.Format("Saved file: {0}{1} to path: {2}", fileName, fileExtension, path));
    }



};

I have found that it was the impersonation statements that I had in there that were left over from writing to file system tests that I was doing.

If I remove the below lines of code then all works as expected.

WindowsImpersonationContext newContext = null;

WindowsIdentity newIdentity = SqlContext.WindowsIdentity;

and

if (newIdentity != null) newContext = newIdentity.Impersonate();