CSS SQL Server Engineers

This is the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support. Posts are provided by the CSS SQL Escalation Services

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

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

  • Comments 5

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.  \\server\instance\....    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

Leave a Comment
  • Please add 5 and 5 and type the answer here:
  • Post
  • PingBack from http://www.travel-hilarity.com/travel-airline-tickets/?p=903

  • Настоящим постом предполагается завершить тему "Введение в FILESTREAM" – см. " Введение в FILESTREAM

  • Is there any way to get around this limitation for applications unable to authenticate using Integrated Security?

  • I am using sqlserver 2008 FileStream Feature on Web Farm scenario. My Web Server and Database server does not reside on same domain. On database server port 1433 and port 445 is enabled for web server. In order to connect to databse via Windows Authentication for filestream, I am using mirroing account method. A admin user is created on web server and a same user with same password is created on database server. the user on the database server is added as sysadmin on database server

    Also my filestream is map to a SAN drive (iSCSI)

    I am able to connect to database. When I am trying to use SQLFileStream this line is throwing an error

    sqlFileStream = new SqlFileStream(dbFilePath, transactionContext, FileAccess.Write, FileOptions.SequentialScan, 0);

    Log

    _______

    Message: Object reference not set to an instance of an object

    Exception    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.NullReferenceException, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089  Message : Object reference not set to an instance of an object.  Source :   at SyncInvokeAddGenericDocument(Object , Object[] , Object[] )     at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs)     at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc& rpc)     at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage5(MessageRpc& rpc)     at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage4(MessageRpc& rpc)     at System.ServiceModel.Dispatcher.MessageRpc.Process(Boolean isOperationContextSet)    Additional Info:    MachineName :  TimeStamp : 27/05/2010 06:38:35 FullName :

    I have already spend lot of time, but no luck. Please help!!

  • May 11 2011 Update

    1. The FileOption.WriteThrough is helpful but further testing shows it is not a complete workaround.  It is helpful but you can still encounter the problem.  A fix to the RsFx driver is required to avoid the problem.

    2.  Windows Auth is a cornerstone of the File Stream feature.  You can't use standard auth.

Page 1 of 1 (5 items)