How It Works: File Streams Requires Integrated Security (Windows Authentication)

SQL Server authentication versus Windows Authentication seems to cause File Stream users confusion.   I thought this e-mail chain would help.

From: Robert Dorr
Subject: RE: OpenSqlFilestream problem

 

It is really not the SQL Server Service account that matters here. SQL Server has to have access to the files in order to handle the file stream file groups.

The problem that external access to the files occur through a UNC like facility. \serverinstance.... Column.PathName This goes through the Windows IOMgr path and to our RsFx kernel components. The security context at this point in time is the user making the call to OpenSqlFileStream that ends up calling NtCreateFile. To validate the user can access the file under the transaction the user is impersonated and checked by SQL Server for proper transaction access. Since the transaction was opened under mixed security the impersonation will fail.

The entire loop has to be done under the same integrated security.

From: Another User
Subject: RE: OpenSqlFilestream problem

 

A SQL login means the FileStream is using the SQL Server service account and not the user's service account. It's quite possible the service account may have limited rights. e.g. if it's a local account, it will not be able to access files on a network share, etc.

 


From: Robert Dorr
Subject: OpenSqlFilestream problem

File Streams only support integrated security access. The Windows HANDLE model does not understand SQL Server logins.

From: User
Subject: OpenSqlFilestream problem

 

  I make the call to OpenSqlFileStream I getting an “Invalid Handle” error. I see in sqlserver error log following message.

The current user cannot use this FILESTREAM transaction context. To obtain a valid FILESTREAM transaction context, use GET_FILESTREAM_TRANSACTION_CONTEXT.

<fsautils.cpp, 0347, NtStatusFromSqlError > SqlServer error code 3934 is converted to 0xc0000022 < fsfsql.cpp, 0287, CFsaFstrSqlSession::OnFileOpen > CFsaFstrSqlSession::OnFileOpen () failed: Error 0xc0000022 (-1073741790)

 

From sql profiler I captured the same error also.

Error: 3934, Severity: 14, State: 1.

The current user cannot use this FILESTREAM transaction context. To obtain a valid FILESTREAM transaction context, use GET_FILESTREAM_TRANSACTION_CONTEXT.

 

Switching the application connection string from SQL Server login account , which is sysadmin, to Integrated Security resolves the problem. But using sqllogin is mandatory in this case. If anyone can help , I appreciate.

          //SqlConnection cxn = new SqlConnection("Data Source=(local);Initial Catalog=Northwind;Integrated Security=True;"); //It works

          SqlConnection cxn = new SqlConnection("Data Source=(local);Initial Catalog=Northwind;User ID=sa;Password=111;");

    cxn.Open();

          SqlTransaction txn = cxn.BeginTransaction();

    Guid DocId = Guid.NewGuid();

                               ……

                      //It works with Integrated Security but not sa

    SafeFileHandle handle = SqlNativeClient.OpenSqlFilestream(

                    sqlFilePath.Value,

                    SqlNativeClient.DESIRED_ACCESS_WRITE,

                    0,

                    transactionToken.Value,

                    (UInt32)transactionToken.Value.Length,

                    new SqlNativeClient.LARGE_INTEGER_SQL(0));

                 

                  //Invalid handle. Parameter name: handle

                  .......

 

Bob Dorr
SQL Server Senior Escalation Engineer