locked
SQL Server 2008 Filestream Impersonation Access Denied error RRS feed

  • Question

  • 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;
       }
    }
    
    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)
    

    Following is the Connection String I use in my application.

    <add name="ConnectionString" connectionString="Data Source=Server_Name;Initial Catalog=Dev_DB;User ID=username;Password=password"
       providerName="System.Data.SqlClient" />
    
    I'm not permitted to use integrated security. Can someone help me with this issue. 

    Thanks

    Monday, January 17, 2011 9:48 PM

Answers

  • AFAIK, you can't do impersonation by setting up an impersonation context of your own; you can only impersonate the context login using SqlContext.WindowsIdentity. In addition, as far as I've proved with experiments, you can't use filestream in streaming mode from a SQLCLR procedure or function. And, as you likely know, you can't use filestream in streaming mode with a SQL Login. You'll need to find some other way to do this, and given you're restrictions, I don't see any way to use filestream in streaming mode. You'll need to use T-SQL (either through the Context connection in SQLCLR or from the client) to access your filestream column.

    Hope this helps, Bob Beauchemin, SQLskills

    • Marked as answer by gvar369 Saturday, January 22, 2011 12:08 AM
    Wednesday, January 19, 2011 12:58 AM

All replies

  • Looks like you are acccesing System data. Use SQL admin or provide admin access to the current user.
    Tuesday, January 18, 2011 1:09 AM
  • AFAIK, you can't do impersonation by setting up an impersonation context of your own; you can only impersonate the context login using SqlContext.WindowsIdentity. In addition, as far as I've proved with experiments, you can't use filestream in streaming mode from a SQLCLR procedure or function. And, as you likely know, you can't use filestream in streaming mode with a SQL Login. You'll need to find some other way to do this, and given you're restrictions, I don't see any way to use filestream in streaming mode. You'll need to use T-SQL (either through the Context connection in SQLCLR or from the client) to access your filestream column.

    Hope this helps, Bob Beauchemin, SQLskills

    • Marked as answer by gvar369 Saturday, January 22, 2011 12:08 AM
    Wednesday, January 19, 2011 12:58 AM
  • Thanks for the reply. I made few changes to the code and also used integrated security for streaming based on your reply.

    Following is the code changes i've made.

    NetworkSecurity ns = new NetworkSecurity();
    WindowsImpersonationContext wImpersonationCtx = ns.ImpersonateUser(IMP_Domain, IMP_Username, IMP_Password, LogonType.LOGON32_LOGON_INTERACTIVE, LogonProvider.LOGON32_PROVIDER_DEFAULT);
    
    //created an integrated connection string in the getIntegratedConnectionString method
    DAManager PDAM = new DAManager(DAManager.getIntegratedConnectionString());
    
    using (SqlConnection connection = (SqlConnection)PDAM.CreateConnection())
    {
    // code to insert file
    }
    

    P.S: It was difficult to get my PM to agree with the integrated security, but in the end all's well... :)

    Ref: http://blogs.msdn.com/b/dataaccess/archive/2006/01/25/517495.aspx

    Thanks

    --

    Vijay

    Saturday, January 22, 2011 12:21 AM