title: SQL filestream authentication best practices
body: <p>I was able to use SQL Filestream fine locally but when I try to upload the files to a remote SQL server which uses SQL authentication, I get a Access Denied exception. Apparently, SQL Filestream only works with Windows Authentication (Integrated Security=true) and not with SQL Authentication which is what we currently have.</p>
  
  <p>No one really uses Windows Authentication in production environment so I just want to know how to overcome this limitation. What's the best practice?</p>
  
  <pre><code>    public static void AddItem(RepositoryFile repository, byte[] data)
  {
      using (var scope = new TransactionScope())
      {
          using (var db = new MyEntities()) // DBContext
          {
              db.RepositoryTable.AddObject(repository);
              db.SaveChanges();
          }
  
          using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
          using (var cmd = new SqlCommand(string.Format("SELECT Data.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT() FROM dbo.RepositoryTable WHERE ID='{0}'", repository.ID), con)) // "Data" is the column name which has the FILESTREAM. Data.PathName() gives me the local path to the file.
          {
              cmd.Connection.Open();
              using (var reader = cmd.ExecuteReader())
              {
                  while (reader.Read())
                  {
                      var path = reader.GetString(0);
                      var transactionContext = reader.GetSqlBytes(1).Buffer;
                      var fileStream = new SqlFileStream(path, transactionContext, FileAccess.Write);
  
                      fileStream.Write(contents, 0, contents.Length); // I get the error at this line.
                      fileStream.Close();
                  }
              }
          }
  
          scope.Complete();
      }
  }
  </code></pre>