SQL Server 2008 Filestream Impersonation Access Denied error

I've been trying to upload a file to the database using SQL SERVER 2008 Filestream and Impersonation technique to save the file in the file system, but i keep getting Access Denied error; even though i've set the permissions for the impersonating user to the Filestream folder(C:\SQLFILESTREAM\Dev_DB). when i debugged the code, i found the server return a unc path(\Server_Name\MSSQLSERVER\v1\Dev_LMDB\dbo\FileData\File_Data\13C39AB1-8B91-4F5A-81A1-940B58504C17), which was not accessible through windows explorer. I've my web application hosted on local maching(Windows 7). SQL Server is located on a remote server(Windows Server 2008 R2). Sql authentication was used to call the stored procedure.

Following is the code i've used to do the above operations.

SqlCommand sqlCmd = new SqlCommand("AddFile");
sqlCmd.CommandType = CommandType.StoredProcedure;

sqlCmd.Parameters.Add("@File_Name", SqlDbType.VarChar, 512).Value = filename;
sqlCmd.Parameters.Add("@File_Type", SqlDbType.VarChar, 5).Value = Path.GetExtension(filename);
sqlCmd.Parameters.Add("@Username", SqlDbType.VarChar, 20).Value = username;
sqlCmd.Parameters.Add("@Output_File_Path", SqlDbType.VarChar, -1).Direction = ParameterDirection.Output;

DAManager PDAM = new DAManager(DAManager.getConnectionString());
using (SqlConnection connection = (SqlConnection)PDAM.CreateConnection())
{
     connection.Open();
     SqlTransaction transaction = connection.BeginTransaction();

     WindowsImpersonationContext wImpersonationCtx;
     NetworkSecurity ns = null;
     try
     {
          PDAM.ExecuteNonQuery(sqlCmd, transaction);
          string filepath = sqlCmd.Parameters["@Output_File_Path"].Value.ToString();
          sqlCmd = new SqlCommand("SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()");
          sqlCmd.CommandType = CommandType.Text;

          byte[] Context = (byte[])PDAM.ExecuteScalar(sqlCmd, transaction);
          byte[] buffer = new byte[4096];
          int bytedRead;

          ns = ne开发者_开发技巧w NetworkSecurity();
          wImpersonationCtx = ns.ImpersonateUser(IMP_Domain, IMP_Username, IMP_Password, LogonType.LOGON32_LOGON_INTERACTIVE, LogonProvider.LOGON32_PROVIDER_DEFAULT);

          SqlFileStream sfs = new SqlFileStream(filepath, Context, System.IO.FileAccess.Write);
          while ((bytedRead = inFS.Read(buffer, 0, buffer.Length)) != 0)
          {
               sfs.Write(buffer, 0, bytedRead);
          }
          sfs.Close();

          transaction.Commit();
     }
     catch (Exception ex)
     {
          transaction.Rollback();
     }
     finally
     {
          sqlCmd.Dispose();
          connection.Close();
          connection.Dispose();
          ns.undoImpersonation();
          wImpersonationCtx = null;
          ns = null;
     }
}

Can someone help me with this issue. Reference

Exception:
Type : System.ComponentModel.Win32Exception, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 Message : Access is denied Source : System.Data Help link : 
NativeErrorCode : 5
ErrorCode : -2147467259
Data : System.Collections.ListDictionaryInternal
TargetSite : Void OpenSqlFileStream(System.String, Byte[], System.IO.FileAccess, System.IO.FileOptions, Int64)
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)

Thanks

---

Sql authentication is not supported when accessing Filestream data

FILESTREAM Storage in SQL Server 2008

---

Try using LOGON32_LOGON_NETWORK rather than LOGON32_LOGON_INTERACTIVE. I encountered this error many years ago using API calls for UNC impersonation, so maybe it still holds.

---

I had the same problem. The solution was to check all checkboxes on the "FileStream" tab of the SQL Server Configuration Manager.

Here are the instructions on MSDN: http://msdn.microsoft.com/en-us/library/cc645923(v=sql.100).aspx

Note points 7 and 8:

1. If you want to read and write FILESTREAM data from Windows, click Enable FILESTREAM for file I/O streaming access. Enter the name of the Windows share in the Windows Share Name box.
2. If remote clients must access the FILESTREAM data that is stored on this share, select Allow remote clients to have streaming access to FILESTREAM data.

We had remote clients accessing the file stream thus we needed all check boxes enabled, and originally we only checked the first three, which caused the error.