SQL Server 2008 Filestream Impersonation Access Denied error
- by Adi
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 = new 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
