SqlFileStream Access is Denied Error asp.net c#, SQL2008

Hello everyone,

I have read every www post I can get my hands on regarding this topic, scoured every link I can retrieve, and to date, am still empty handed on this one.

Here's the setup:

Db Server:

SQL2008 Server with Filestream fully turned on at Instance and Db levels.
SqlEngineSvc and SqlAgentSvc are both running as domain accounts.
Filestream directory is on the Local SQL Server, MyDomain/Domain Users have full control on the filestream directory.

WebServer:
Windows2003Server running IIS6, AppPool running as Network Service. Impersonation and integrated security is turned on. (This requirement to monitor db activity and permissions by network/windows user was achieved by introducing Kerberos authentication (several years ago). See: Kerberos Double-Hop Scenario)

I have already created my filestream table and uploaded blobs to it through t-sql. I have navigated to the directory and physically seen the files in the newly created directory. I have then queried the table and returned the varbinary data in the recordset. -All while logged in as my windows domain account.

-The only time I have trouble is when I try to open the filestream through my asp.net page.... I get the access is denied error when trying to open the SqlFileStream to read below. THis is a very large site which is tied very closely to SQL Server, utilizing WIndows authentication in order to perform the db transactions.. the only new feature I've introduced here is opening the filestream through the webpage.... i'm able to store/retrieve small blobs directly in the db as the windows user as well as upload and query the filestream blob with t-sql as the windows user...

Here's my relevant code:

webConfig ConnectionString:

      <add name="My_DB" 
            connectionString="Data Source=MyServer; Initial Catalog=DEVL;Integrated Security=true"

Select allOpen in new window

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.IO;
using System.Data.SqlClient;
using System.Data.SqlTypes;

public partial class VidTestForm : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string VideoID;

        try
        {
            VideoID = Request.QueryString["vi"].ToString();
        }
        catch { VideoID = "B119C03D-92DD-E211-AF66-00237D654CCC"; }
         



        SqlConnection cn = new SqlConnection();
        cn.ConnectionString =
            System.Configuration.ConfigurationManager.ConnectionStrings["My_DB"].ToString();
        cn.Open();


        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = "dbo.sspGetVideoPath";
        cmd.Connection = cn;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@FileId", VideoID);

        using (SqlTransaction trn = cn.BeginTransaction("VideoTran"))
        {
            cmd.Transaction = trn;
            string filePath = string.Empty;
            string sysUser = string.Empty;
            byte[] txContext = null;

            try
            {
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    txContext = (reader["txContext"] as byte[]);
                    filePath = reader["filePath"].ToString();
                    sysUser = reader["sysUser"].ToString();
                }
                reader.Close();
                Response.Write("**FileStream: " + filePath.ToString());
                Response.Write("<br />**TrxContext Length: " + txContext.Length.ToString());
                Response.Write("<br />**Connecting to SQL As: " + sysUser.ToString());
                Response.Write("<br />**C# User: " + System.Security.Principal.WindowsIdentity.GetCurrent().Name.ToString());

/*Error On This Line Below */
                SqlFileStream sfs = new SqlFileStream(filePath, txContext, FileAccess.Read);

                Response.BufferOutput = false;
                Response.ContentType = "video/x-ms-wmv";
                BinaryWriter bwr = new BinaryWriter(Response.OutputStream);
                const int bufferSize = 4096;
                byte[] buffer = new byte[bufferSize];

                int byteCount = sfs.Read(buffer, 0, bufferSize);
                while (byteCount == bufferSize)
                {
                    bwr.Write(buffer, 0, byteCount);
                    byteCount = sfs.Read(buffer, 0, bufferSize);
                }

                Response.End();
                bwr.Close();
                sfs.Close();

            }
            catch (Exception ex)
            {
                Response.Write("<br />**Error: " + ex.Message.ToString());
                Response.Write("<br />**Stack Trace: " + ex.StackTrace.ToString());
                Response.Write("<br />**BaseException: " + ex.GetBaseException().ToString());
                try
                {
                    Response.Write("<br />**Inner Exception: " + ex.InnerException.Message.ToString());
                }
                catch { }


            }
            finally
            {
                cmd.Transaction.Commit();
                cn.Close();
            }

        }
    }
}

Select allOpen in new window

Here is the output on the webpage.... you can see that the stored procedure call is successful and it returns the systemUser as MyDomain\MyUserId from SQL Server, which is the same as the c# windowsIdentity.... because of this, I believe I have impersonation as well as integrated security ok.

Here is the webpage output of the embedded response.write:

**FileStream: \\MyServer\MSSQLSERVER\v1\DEVL\dbo\FileMstr\FileData\B119C03D-92DD-E211-AF66-00237D654CCC
**TrxContext Length: 16
**Connecting to SQL As: MyDomain\MyUserId
**C# User: MyDomain\MyUserId
**Error: Access is denied
**Stack Trace: at System.Data.SqlTypes.SqlFileStream.OpenSqlFileStream(String path, Byte[] transactionContext, FileAccess access, FileOptions options, Int64 allocationSize) at System.Data.SqlTypes.SqlFileStream..ctor(String path, Byte[] transactionContext, FileAccess access, FileOptions options, Int64 allocationSize) at System.Data.SqlTypes.SqlFileStream..ctor(String path, Byte[] transactionContext, FileAccess access) at VidTestForm.Page_Load(Object sender, EventArgs e) in c:\Inetpub\wwwroot\DEVL\home\VidTestForm.aspx.cs:line 61
**BaseException: System.ComponentModel.Win32Exception (0x80004005): Access is denied at System.Data.SqlTypes.SqlFileStream.OpenSqlFileStream(String path, Byte[] transactionContext, FileAccess access, FileOptions options, Int64 allocationSize) at System.Data.SqlTypes.SqlFileStream..ctor(String path, Byte[] transactionContext, FileAccess access, FileOptions options, Int64 allocationSize) at System.Data.SqlTypes.SqlFileStream..ctor(String path, Byte[] transactionContext, FileAccess access) at VidTestForm.Page_Load(Object sender, EventArgs e) in c:\Inetpub\wwwroot\DEVL\home\VidTestForm.aspx.cs:line 61


Stored Procedure:

CREATE PROCEDURE [dbo].[sspGetVideoPath]
(@FileId UNIQUEIDENTIFIER = NULL)
AS
BEGIN


      SET NOCOUNT ON;

SELECT FileData.PathName() AS filePath
        ,GET_FILESTREAM_TRANSACTION_CONTEXT() AS txContext
        ,System_User AS [SysUser]
  FROM dbo.FileMstr
 WHERE FileID = @FileId


END
 


Thanks in Advance.