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

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.