SQL Server FILESTREAM and Windows 10
May 8, 2017
Database Administration, SQL / Databases
I recently had the Windows 10 Creators Updated applied to my PC via Microsoft Update.
Amazingly, the upgrade seemed to go quite smoothly!
However, as time goes on I am noticing small things that cause a lot of grief. The focus of this article is SQL Server FILESTREAM access.
I have a database that requires FILESTREAM access. I went to use that database today only to find out that it was inaccessible. After looking through my SQL Server configuration, I discovered that the FILESTREAM feature was disabled. It said it was running in the Configured Values, but when clicking on the Running Values of the server was informed differently.
So, turning to the Internet I found several articles discussing how to enable FILESTREAM. Since I had done this once before, I wasn't sure this was my issue, but I went through the whole reconfiguration process again. After rebooting, I had the same result - go figure!
So I finally pulled up the SQL Server logs (see the screenshot below for how to access them from SSMS):
and found an error that read:
"Configuration option 'filestream access level' changed from 2 to 2. Run the RECONFIGURE statement to install.
<{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)"
Hmmm....
Now that I had more detailed information, an Internet search quickly turned up a solution: due to a change in the Windows 10 file subsystem, I needed to make the SQL Server service run under a different account (see below):
The default installation of SQL Server creates a custom account for the service to run as and apparently that account no longer has sufficient permissions in Windows 10 Creators Update. So rather than mess with permissions, I selected the "Local System" account and now everything works like a charm!
Click here for the complete article.