Condividi tramite

FILESTREAM issues with SQL Server on Windows 10 creators update

  • Articolo

If you have SQL Server installed on Windows 10 and if you have enabled the Filestream feature at the instance level and created databases that have filestream containers, after applying the Windows 10 creators update [RS2] you will notice that the filestream feature does not work and you encounter unexpected errors.

In Windows 10 creators update, a change was made in the IO Manager code that deals with NtCreateFile to tighten the ACL checks for specific file create disposition. SQL Server engine uses this API to connect to Filestream filesystem filter driver called RsFx driver. The change in NtCreateFile changed the behavior of FILE_OPEN_IF (create a new file if needed) function to prevent users or services using this API to write on the storage if the calling token doesn't have write permissions on the storage. Because of this change, SQL process (which runs as a service with a virtual service account, e.g., NT Service\MSSQL$SQL2016), if it doesn't have administrator permissions, cannot open a handle to the RsFx driver and fails with STATUS_ACCESS_DENIED error. This behavioral change leads to unexpected filestream errors and causes SQL Server filestream database to fail to start if SQL Service account didn't have write permissions on the filestream store.

Following are some of the error messages related to filestream, you may encounter when

  • Windows 10 creators update is applied on an existing installation of SQL Server using filestream feature OR
  • New installation of SQL Server with database using filestream created on Windows 10 creators update build 15048.

You restart SQL Server or attempt to bring the database online. You will notice the database does not come online and end up in [Recovery Pending] state.

SQL Server error log will show the following information:

2017-04-14 10:39:20.69 spid26s [INFO] HkHostDbCtxt::Initialize(): Database ID: [10] 'Archive'. XTP Engine version is 0.0.
2017-04-14 10:39:20.69 spid26s Starting up database 'Archive'.
2017-04-14 10:39:21.25 spid26s [INFO] HkHostDbCtxt::Initialize(): Database ID: [10] 'Archive'. XTP Engine version is 0.0.
2017-04-14 10:39:21.57 spid26s Error: 5591, Severity: 16, State: 5.
2017-04-14 10:39:21.57 spid26s FILESTREAM feature is disabled.
2017-04-14 10:39:21.57 spid26s Error: 5105, Severity: 16, State: 14.
2017-04-14 10:39:21.57 spid26s A file activation error occurred. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\Archive_fs' may be incorrect. Diagnose and correct additional errors, and retry the operation.

When you attempt to create a database with FileStream container, you will encounter the following error:

CREATE DATABASE Archive
ON PRIMARY ( NAME = Arch1, FILENAME = 'archdat1.mdf'),
FILEGROUP FileStreamGroup1 CONTAINS
FILESTREAM( NAME = Arch3,
FILENAME = 'filestream1') LOG ON ( NAME = Archlog1, FILENAME = 'archlog1.ldf') GO

Msg 5591, Level 16, State 1, Line 1
FILESTREAM feature is disabled.

 

Even though the error message indicates the feature is disabled, when you look in the service properties in SQL Server Configuration Manager, you will notice the following:

 

When you attempt to restore a backup that contains filestream containers, you will encounter the following message:

When SQL Server starts you will notice the following messages in the SQL Server errorlog:

2017-04-14 10:25:22.34 Server Microsoft SQL Server 2016 (RTM-GDR) (KB3210111) - 13.0.1728.2 (X64)
Dec 13 2016 04:40:28
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows 10 Enterprise 6.3 <X64> (Build 15063: ) (Hypervisor)
2017-04-14 10:25:22.35 Server The service account is 'NT Service\MSSQL$SQL2016'. This is an informational message; no user action is required.
<{7715B5FC-837B-46C9-A28B-A7867FC86023}>RsFxFt.Dll::RsFxNsoInitialize failed: Error 0x80070005 (-2147024891)
<{C580416B-A13E-4ECD-B61B-AAFAE39E5E35}>Failed to initialize the CFsaShareFilter interface
<{1038F43D-3391-45F7-B1B3-BADF26459429}>Failed to initialize CFsaShareFilter: Error 0x80070005 (-2147024891)
2017-04-14 10:25:23.38 spid4s FILESTREAM: effective level = 0, configured level = 2, file system access share name = 'SQL2016'.

 

UPDATE 5/10/2017

The fix for the issue is released by Windows team as part of May security update KB 4016871. This update will be downloaded and installed automatically from Windows Update. To get the stand-alone package for this update, go to the Microsoft Update Catalog website. After this update is installed, the build number will be either 15063.296 (for all Windows 10 devices except Mobile and IoT) or 15063.297 (for Mobile and IoT).

Workaround

Following are some of the workaround identified which will enable you to overcome the above errors on Windows 10 creators update if you do not apply the Windows 10 Update.

  • Change the SQL Server service startup account to built-in account LocalSystem
  • Change the SQL Server service startup account to a domain user account with local admin privileges on the system
  • If you use virtual account [NT SERVICE\MSSQL$InstanceName] as service startup account, please make this account a member of the local administrators group
  • Uninstall Creators Update and fall back to the previous Windows build

 

Parikshit Savjani
Senior PM, SQL Server Tiger Team Twitter | LinkedIn
Follow us on Twitter: @mssqltiger | Team Blog: Aka.ms/sqlserverteam

Comments

  • Anonymous
    April 14, 2017
    won't this be avoided if two teams sat together and tested this
  • Anonymous
    April 16, 2017
    Another workaround is to repair SQL Server using the setup.
    • Anonymous
      April 17, 2017
      Raditya,Running a repair is not a valid workaround. When you run a repair setup option, the setup just temporarily assigns R/W access to the service account but it is not persistent in the registry. When you restart SQL Server services or OS or disable/re-enable Filestream feature, it will reset the service account permissions back to read only resulting into errors.
  • Anonymous
    April 17, 2017
    So this is KB4015217?
  • Anonymous
    April 18, 2017
    Hit by this today, my databases went into Recovery mode after installing 1703. LocalSystem workaround worked.
    • Anonymous
      April 20, 2017
      Same here. Thanks!!
  • Anonymous
    April 19, 2017
    •Change the SQL Server service startup account to built-in account LocalSystemThis should be the least preferred of the options -- LocalSystem is an extremely privileged account, and services that don't need such extreme privileges should avoid using it, particularly when there are much less extreme workarounds, like adding the SQL account to local administrators.
    • Anonymous
      April 19, 2017
      Can this be handled by altering ACLs on the target drive to give the SQL service account permissions to specific necessary folders?
      • Anonymous
        April 19, 2017
        Mark, We fully understand it is not a recommended or preferred approach but currently that is the only one we have besdies rolling back creators update. We are working with Windows team to expedite the fix and also defering the creators update for Windows 10 machines with SQL Server installed to help minimize the impact and surprises.Further, we are not talking about ACLs on the storage drive here, this is the ACLs required while call NTCreatefile FILE_OPEN_IF API inside the SQL engine code to access RsFx kernel driver. The Windows team is working on the fix to avoid this breaking change caused by this change but since this involves ACLs to kernel drivers, there is no easy or granular scope of workaround for this.
        • Anonymous
          April 25, 2017
          Right -- but the options in the list include "run SQL as 'LocalSystem', and also 'add the service account to the local administrators group' -- the latter would seem to be safer.
  • Anonymous
    April 25, 2017
    The comment has been removed
  • Anonymous
    April 25, 2017
    Hi,unfortunately neither changing the account to LocalSystem nor using a local account with admin rights changed anything on my setup.Really looking forward applying the fix!Thanks,Michael
    • Anonymous
      April 25, 2017
      Michael, Which version of SQL server are you running? Make sure you are not hitting any of the older issues which is fixed in latest release of SQL Server. This issue is specific to Windows 10 Creator update and the workaround to change the service account to LocalSystem or adding the account to admin group should work since we have tested it.https://blogs.msdn.microsoft.com/sql_server_team/filestream-rsfx-driver-fails-to-load-after-installing-sql-server-2016-with-cu2-on-windows-server-2016-with-secureboot-on/
      • Anonymous
        May 03, 2017
        The comment has been removed
  • Anonymous
    May 16, 2017
    I am running into a similar issue after applying updates to a Windows 2012 R2 server. I'm running SQL Server 2012 SP3. All databases that had FILESTREAM enabled came up with the error posted below. Has this been reported / is there a known fix? Restarting SQL Server fixed the issue and the databases did not get stuck in a recovering state.FILESTREAM feature is disabled.A file activation error occurred. The physical file name.....
    • Anonymous
      May 16, 2017
      Rory,This issue and error is specific to Windows 10 creators update. If you are hitting this on Windows 2012 R2, it might be a different issue and you might want to post this question in MSDN forum or reach out to our CSS for troubleshooting and support.