FILESTREAM issues with SQL Server on Windows 10 creators update


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 (17)

  1. john stevenson says:

    won’t this be avoided if two teams sat together and tested this

    1. Yes, John we do have test and sign off for the builds before it is released but in this case, the Windows team performed a last minute change as it was related to security but wasn’t tested by us which is why if fall through our cracks.

  2. Raditya Juanda says:

    Another workaround is to repair SQL Server using the setup.

    1. 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.

  3. Zennon Damant says:

    So this is KB4015217?

  4. pk says:

    Hit by this today, my databases went into Recovery mode after installing 1703. LocalSystem workaround worked.

    1. Jamie Schmidt says:

      Same here. Thanks!!

  5. Mark Sowul says:

    •Change the SQL Server service startup account to built-in account LocalSystem

    This 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.

    1. Mark Sowul says:

      Can this be handled by altering ACLs on the target drive to give the SQL service account permissions to specific necessary folders?

      1. 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.

        1. Mark Sowul says:

          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.

  6. Exotic Hadron says:

    Thanks to Erland Sommarskog for showing this article!

    I have found lately that I am the only one in the team who has the FILESTREAMS section disabled on the Filegroups page in the database’s properties. At the same time I’ve been receiving

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

    when trying to add FILESTREAM tables to the database.

    And yes, exactly the issue with RsFx filter not being able to initialize:

    RsFxFt.Dll::RsFxNsoInitialize failed: Error 0x80070005 (-2147024891)
    Failed to initialize the CFsaShareFilter interface
    Failed to initialize CFsaShareFilter: Error 0x80070005 (-2147024891)
    2017-04-25 13:33:10.96 spid4s FILESTREAM: effective level = 0, configured level = 2, file system access share name = ‘TMSQL’.

  7. michaelk says:

    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

    1. 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/

      1. michaelk says:

        Hello!

        Thanks for your feedback and apologies for my rather late response.
        My setup is as follows:
        – SQL Server and SQL Agent are running with a user account that is part of the local Administrator group.

        – This is the output of select @@VERSION:
        Microsoft SQL Server 2016 (SP1-GDR) (KB3210089) – 13.0.4202.2 (X64) Dec 13 2016 05:22:44 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows 10 Enterprise 6.3 (Build 15063: ) (Hypervisor)

        – The Windows 10 “System” screen (Settings > About) shows
        Windows 10 Enterprise
        Version: 1703
        OS Build: 15063.138

        Related to the article and the problem you referenced, SQL Server 2016 SP1 should have fixed it already as stated, so I assume it’s not related to the my current issue.
        I’m running Windows on a Macbook Pro via Bootcamp, however this has not caused any kind of different issues or required different fixes for problems I had so far.

        If you have any other idea, I’d be happy to give it a try.
        Thanks,
        Michael

  8. Rory says:

    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…..

    1. 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.

Skip to main content