title: SqlFileStream -- Win32Exception: "Access is denied" -- Impersonation
body: <p>My goal here is to have an attachments manager that (1) effectively uses <a href="https://technet.microsoft.com/en-us/library/bb933993(v=sql.105).aspx" rel="nofollow noreferrer">SQL FILESTREAM</a> as it was designed, (2) restricts access to the SQL FileTable and its underlying FILESTREAM Windows directory to one of two Active Directory accounts (creds provided by a web/app.config file), and (3) effectively disregards the current Windows Auth user for whatever site or app in which this library is included.</p>
  <p>I've seen a number of posts about this issue already, and I believe I've tried nearly every suggestion I've found across them all, still with little-to-no success.</p>
  <p>I have:</p>
  <ol>
  <li>Activated integrated security in my connection string: <code>&quot;Server={server};Database=AttachmentsGlobal;User ID={domain}\\{user};Password={password};Trusted_Connection=False;Integrated Security=true;&quot;</code></li>
  <li>Given that same user RW access to the FileTable directory on the Windows Server 2012 where SQL Server 2012 is running by modifying folder permissions.</li>
  <li>Granted the user Select, Alter, Update, Insert, and Delete rights to the file table and <code>filestream</code> column.</li>
  <li>Implemented a custom impersonator class I found online to impersonate that same user when calling the <code>SqlFileStream</code> object (see below).</li>
  <li>Tried finding errors on the SQL Server side. All I can find in the Event Viewer is that the impersonation I attempt below is successful in logging on the user.</li>
  <li>Ensured that &quot;allow remote connections&quot; is enabled in the FILESTREAM settings in SQL Server.</li>
  </ol>
  <p>It works whenever I disable the <code>using(Impersonator){}</code> block in <code>SaveFile</code> below, but it fails otherwise. It also seems to ignore the connection string when I debug locally: my user account has access to the attachments folder because I'm an admin, but when I change the connection string to a user that explicitly does NOT have access, it still uploads fine. I'm assuming this is the &quot;Integrated Security&quot; setting that's passing the current application user's creds to the server and ignoring the provided user. I'm not sure how to test this well, short of running Visual Studio as another user (which I have tried, and it fails).</p>
  <p>Please tell me that I'm doing something silly and dumb, and that I'm reasonably close to a solution. This incredibly vague &quot;access is denied&quot; error is driving me nuts! Thanks.</p>
  <h2>C#:</h2>
  <pre class="lang-cs prettyprint-override"><code>// SqlFileItem is just a simple model mapping all the columns of the file 
  // table (except the filestream itself)
  public SqlFileItem UploadFile(Stream fileStream, string fileName,
      SqlFileItem parent, AttachmentType restricted) {
      const string uploadSproc = &quot;InsertAttachment&quot;;
      const string metadataSproc = &quot;GetMetadata&quot;;
      SqlFileItem rt;
      using (TransactionScope ts = new TransactionScope()) {
          using (SqlConnection conn = GetConnection(restricted)) {
              conn.Open();
              string serverPath;
              byte[] serverXfer;
              Guid streamIdOfUploadedFile;
              using (SqlCommand cmd = conn.CreateCommand()) {
                  cmd.CommandType = CommandType.StoredProcedure;
                  cmd.CommandText = uploadSproc;
                  cmd.Parameters.Add(&quot;@fName&quot;, SqlDbType.VarChar).Value =
                      fileName;
                  cmd.Parameters.Add(&quot;@parent&quot;, SqlDbType.VarChar).Value =
                      parent.path_locator.ToString();
                  using (SqlDataReader rdr = cmd.ExecuteReader()) {
                      rdr.Read();
                      serverPath = rdr.GetSqlString(0).Value;
                      serverXfer = rdr.GetSqlBinary(1).Value;
                      streamIdOfUploadedFile = rdr.GetGuid(2);
                      rdr.Close();
                  }
              }
              // See below for SaveFile
              SaveFile(fileStream, serverPath, serverXfer, exportRestriction);
              using (SqlCommand cmd = conn.CreateCommand()) {
                  cmd.CommandType = CommandType.StoredProcedure;
                  cmd.CommandText = metadataSproc;
                  cmd.Parameters.Add(&quot;@streamId&quot;,
                      SqlDbType.UniqueIdentifier).Value =
                      streamIdOfUploadedFile;
                  using (SqlDataReader rdr = cmd.ExecuteReader()) {
                      rt =
                          DataReaderToList&lt;SqlFileItem&gt;(rdr)
                              .FirstOrDefault();
                  }
              }
          }
          ts.Complete();
      }
      return rt;
  }
  
  // Performs the actual streaming/saving operation.
  private static void SaveFile(Stream src, string serverPath,
      byte[] serverXfer, AttachmentType restricted) {
      const int blockSize = 1024*512;
      // My attempt at impersonating
      Impersonator imp = restricted == AttachmentType.Open
          ? new Impersonator(@&quot;{openActiveDirectoryUser}&quot;, @&quot;{domain}&quot;, 
              @&quot;{password}&quot;)
          : new Impersonator(@&quot;{restrictedActiveDirectoryUser}&quot;, @&quot;{domain}&quot;,
              @&quot;{password}&quot;);
  
      using (imp) {
          using (src) {
              using (
                  // The line below is where it breaks with a Win32Exception.
                  // The full text of it from VS2015 is provided below.
                  SqlFileStream dest = new SqlFileStream(serverPath,
                      serverXfer, FileAccess.Write)) {
                  byte[] buffer = new byte[blockSize];
                  int bytesRead;
                  while ((bytesRead = src.Read(buffer, 0, buffer.Length)) &gt;
                         0) {
                      dest.Write(buffer, 0, bytesRead);
                      dest.Flush();
                  }
                  dest.Close();
              }
              src.Close();
          }
      }
  }
  </code></pre>
  <p><a href="https://stackoverflow.com/questions/9909784/impersonating-a-windows-user">Impersonator class found here</a></p>
  <h2>SQL:</h2>
  <pre class="lang-sql prettyprint-override"><code>ALTER PROCEDURE [dbo].[InsertAttachment] 
      @fileName varchar(255),
      @parent hierarchyid
  AS
  BEGIN
      SET NOCOUNT ON;
      DECLARE @returns TABLE (stream_id UNIQUEIDENTIFIER)
      INSERT INTO Attachments(file_stream, name, is_directory, path_locator)
      OUTPUT inserted.stream_id INTO @returns
      VALUES (0x, @fileName, 0, dbo.GetCustomPathLocator(@parent.ToString()))
  
      SELECT file_stream.PathName()
          ,GET_FILESTREAM_TRANSACTION_CONTEXT()
          ,aft.stream_id
      FROM Attachments att
      INNER JOIN @returns rt on att.stream_id = rt.stream_id
  END
  
  ALTER PROCEDURE [dbo].[GetMetadata]
      @streamId uniqueidentifier
  AS
  BEGIN
      SELECT stream_id
          -- omit file_stream varbinary(max)
          ,name
          ,path_locator
          ,parent_path_locator
          ,file_type
          ,cached_file_size
          ,creation_time
          ,last_write_time
          ,last_access_time
          ,is_directory 
          -- omit remaining is_* flags
      FROM Attachments
      WHERE stream_id = @streamId
  END
  </code></pre>
  <h2>The exception:</h2>
  <pre class="lang-cs prettyprint-override"><code>System.ComponentModel.Win32Exception was unhandled
    ErrorCode=-2147467259
    HResult=-2147467259
    Message=Access is denied
    NativeErrorCode=5
    Source=System.Data
    StackTrace:
         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 FileTableAttachmentLibrary.AttachmentManager.SaveFile(Stream src, String serverPath, Byte[] serverXfer, AttachmentScope exportRestricted) in C:\_projects\FileTableAttachmentLibrary\FileTableAttachmentLibrary\FileTableAttachmentLibrary\AttachmentManager.cs:line 498
         at FileTableAttachmentLibrary.AttachmentManager.UploadFile(Stream fileStream, String fileName, SqlFileItem parent, AttachmentScope exportRestriction) in C:\_projects\FileTableAttachmentLibrary\FileTableAttachmentLibrary\FileTableAttachmentLibrary\AttachmentManager.cs:line 210
         at TestAttachments.Program.AttachmentManagerTest() in C:\_projects\FileTableAttachmentLibrary\FileTableAttachmentLibrary\TestAttachments\Program.cs:line 69
         at TestAttachments.Program.Main(String[] args) in C:\_projects\FileTableAttachmentLibrary\FileTableAttachmentLibrary\TestAttachments\Program.cs:line 26
    InnerException: null
  </code></pre>