Access Denied error while reading from filestream

Question

I have an intranet application that stores files in SQL filestream.

On my dev machine, everything works like a charm.

I'm able to upload and store files into SQL filestream (AjaxUpload) and able to downlaod them.

On the live server, I'm able to upload files, delete them, but when I try to download the file from filestream, I get the following error:

---

Access is denied

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.ComponentModel.Win32Exception: Access is denied

Source Error: 

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace: 

[Win32Exception (0x80004005): Access is denied]
   System.Data.SqlTypes.SqlFileStream.OpenSqlFileStream(String path, Byte[] transactionContext, FileAccess access, FileOptions options, Int64 allocationSize) +1465594
   System.Data.SqlTypes.SqlFileStream..ctor(String path, Byte[] transactionContext, FileAccess access, FileOptions options, Int64 allocationSize) +398
   System.Data.SqlTypes.SqlFileStream..ctor(String path, Byte[] transactionContext, FileAccess access) +27
   quotes_GetFileStream.quotes_GetFileStream_Load(Object sender, EventArgs e) +740
   System.Web.UI.Control.LoadRecursive() +71
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3048

---

The application pool is set to integrated, V.4.0, and I use a domain user for the identity authentication back to SQL.

I gave that user DB_Owner rights to the SQL Database for that application.

Even tried giving it all the SQL Server Roles, though i still get the above error.

When I change the Identity username to mine (I have Domain Admin rights), everything works flawlessly on the live server.

What rights am I missing to give that user so he can read from SQL filestream properly.

Here is the block of code that gets the file from filestream and pushes it to the browser, maybe I'm missing something here (though once i modify the user it works great).

Dim RecId As Integer = -1

    If Not IsNothing(Request.QueryString("ID")) And IsNumeric(Request.QueryString("ID")) Then
        RecId = CType(Request.QueryString("ID"), Integer)
    End If

    Dim ConString As String = ConfigurationManager.ConnectionStrings("ConnectionString").ToString
    Using Con As SqlConnection = New SqlConnection(ConString)
        Con.Open()
        Dim txn As SqlTransaction = Con.BeginTransaction()
        Dim Sql As String = "SELECT FileData.PathName() , GET_FILESTREAM_TRANSACTION_CONTEXT() as TransactionContext, [FileName], [FileExtension] FROM [QAttach] where RecId = @RecId"
        Dim cmd As SqlCommand = New SqlCommand(Sql, Con, txn)
        cmd.Parameters.Add("@RecID", Data.SqlDbType.Int).Value = RecId
        Dim Rdr As SqlDataReader = cmd.ExecuteReader()

        While Rdr.Read()
            Dim FilePath As String = Rdr(0).ToString()
            Dim objContext As Byte() = DirectCast(Rdr(1), Byte())
            Dim fname As String = Rdr(2).ToString()
            Dim FileExtension As String = Rdr(3).ToString()

            Dim sfs As SqlFileStream = New SqlFileStream(FilePath, objContext, FileAccess.Read)

            Dim Buffer As Byte() = New Byte(CInt(sfs.Length) - 1) {}
            sfs.Read(Buffer, 0, Convert.ToInt32(Buffer.Length))


            Response.Buffer = True
            Response.Charset = ""
            Response.Cache.SetCacheability(HttpCacheability.NoCache)
            Response.ContentType = FileExtension
            Response.AddHeader("content-disposition", "attachment;filename=" & fname)
            Response.BinaryWrite(Buffer)
            Response.Flush()
            Response.End()

            sfs.Close()

        End While
    End Using

Thanks

Oren

Answer 1

FILESTREAM requires integrated security on the connection string. SQL Server Auth is not supported.

See https://social.msdn.microsoft.com/Forums/fr-FR/c2b9e316-f77d-49e4-8433-440afd392d1a/sqlfilestream-opensqlfilestream-access-is-denied-in-c?forum=adodotnetdataproviders