{"id":242,"date":"2017-05-13T08:26:35","date_gmt":"2017-05-13T08:26:35","guid":{"rendered":"http:\/\/www.web-workers.ch\/?p=242"},"modified":"2017-05-13T08:38:35","modified_gmt":"2017-05-13T08:38:35","slug":"filestream-issues-with-sql-server-on-windows-10-creators-update","status":"publish","type":"post","link":"https:\/\/www.web-workers.ch\/index.php\/2017\/05\/13\/filestream-issues-with-sql-server-on-windows-10-creators-update\/","title":{"rendered":"FILESTREAM issues with SQL Server on Windows 10 creators update"},"content":{"rendered":"<p>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 <a href=\"https:\/\/blogs.windows.com\/windowsexperience\/2017\/04\/11\/how-to-get-the-windows-10-creators-update\">Windows 10 creators update [RS2<\/a>] you will notice that the filestream feature does not work and you encounter unexpected errors.<\/p>\n<p>In Windows 10 creators update, a change was made in the IO Manager code that deals with <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/bb432380(v=vs.85).aspx\">NtCreateFile<\/a> 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 <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/bb432380(v=vs.85).aspx\">NtCreateFile<\/a> changed the behavior of <strong>FILE_OPEN_IF <\/strong>(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\u2019t have write permissions on the storage. Because of this change, SQL process (which runs as a service with a virtual service account, e.g., <strong><em>NT Service\\MSSQL$SQL2016<\/em><\/strong>), if it doesn\u2019t have administrator permissions, cannot open a handle to the\u00a0RsFx driver\u00a0and fails with <strong><em>STATUS_ACCESS_DENIED<\/em><\/strong> error. This behavioral change leads to unexpected filestream errors and causes SQL Server filestream database to fail to start if SQL Service account didn\u2019t have write permissions on the filestream store.<\/p>\n<p>Following are some of the error messages related to filestream, you may encounter when<\/p>\n<ul>\n<li>Windows 10 creators update is applied on an existing installation of SQL Server using filestream feature OR<\/li>\n<li>New installation of SQL Server with database using filestream created on Windows 10 creators update <strong>build 15048<\/strong>.<\/li>\n<\/ul>\n<p>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.<\/p>\n<p><img data-recalc-dims=\"1\" decoding=\"async\" src=\"https:\/\/i0.wp.com\/msdnshared.blob.core.windows.net\/media\/2017\/04\/041417_2334_FILESTREAMi1.png?ssl=1\" alt=\"\" \/><\/p>\n<p>SQL Server error log will show the following information:<\/p>\n<p>2017-04-14 10:39:20.69 spid26s [INFO] HkHostDbCtxt::Initialize(): Database ID: [10] \u2018Archive\u2019. XTP Engine version is 0.0.<br \/>\n2017-04-14 10:39:20.69 spid26s Starting up database \u2018Archive\u2019.<br \/>\n2017-04-14 10:39:21.25 spid26s [INFO] HkHostDbCtxt::Initialize(): Database ID: [10] \u2018Archive\u2019. XTP Engine version is 0.0.<br \/>\n2017-04-14 10:39:21.57 spid26s Error: 5591, Severity: 16, State: 5.<br \/>\n2017-04-14 10:39:21.57 spid26s <strong>FILESTREAM feature is disabled<\/strong>.<br \/>\n2017-04-14 10:39:21.57 spid26s Error: 5105, Severity: 16, State: 14.<br \/>\n2017-04-14 10:39:21.57 spid26s <strong>A file activation error occurred<\/strong>. The physical file name \u2018C:\\Program Files\\Microsoft SQL Server\\MSSQL13.SQL2016\\MSSQL\\DATA\\Archive_fs\u2019 may be incorrect. Diagnose and correct additional errors, and retry the operation.<\/p>\n<p>When you attempt to create a database with FileStream container, you will encounter the following error:<\/p>\n<p>CREATE DATABASE Archive<br \/>\nON PRIMARY ( NAME = Arch1,<br \/>\nFILENAME = \u2018archdat1.mdf\u2019),<br \/>\nFILEGROUP FileStreamGroup1 CONTAINS<br \/>\nFILESTREAM( NAME = Arch3,<br \/>\nFILENAME = \u2018filestream1\u2019)<br \/>\nLOG ON ( NAME = Archlog1,<br \/>\nFILENAME = \u2018archlog1.ldf\u2019)<br \/>\nGO<\/p>\n<p>Msg 5591, Level 16, State 1, Line 1<br \/>\nFILESTREAM feature is disabled.<\/p>\n<p>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:<\/p>\n<p><img data-recalc-dims=\"1\" decoding=\"async\" src=\"https:\/\/i0.wp.com\/msdnshared.blob.core.windows.net\/media\/2017\/04\/041417_2334_FILESTREAMi2.png?ssl=1\" alt=\"\" \/><\/p>\n<p>When you attempt to restore a backup that contains filestream containers, you will encounter the following message:<\/p>\n<p><img data-recalc-dims=\"1\" decoding=\"async\" src=\"https:\/\/i0.wp.com\/msdnshared.blob.core.windows.net\/media\/2017\/04\/041417_2334_FILESTREAMi3.png?ssl=1\" alt=\"\" \/><\/p>\n<p><img data-recalc-dims=\"1\" decoding=\"async\" src=\"https:\/\/i0.wp.com\/msdnshared.blob.core.windows.net\/media\/2017\/04\/041417_2334_FILESTREAMi4.png?ssl=1\" alt=\"\" \/><br \/>\nWhen SQL Server starts you will notice the following messages in the SQL Server errorlog:<\/p>\n<p>2017-04-14 10:25:22.34 Server Microsoft SQL Server 2016 (RTM-GDR) (KB3210111) \u2013 13.0.1728.2 (X64)<br \/>\nDec 13 2016 04:40:28<br \/>\nCopyright (c) Microsoft Corporation<br \/>\nDeveloper Edition (64-bit) on Windows 10 Enterprise 6.3 &lt;X64&gt; (Build 15063: ) (Hypervisor)<br \/>\n2017-04-14 10:25:22.35 Server The service account is \u2018NT Service\\MSSQL$SQL2016\u2019. This is an informational message; no user action is required.<br \/>\n&lt;{7715B5FC-837B-46C9-A28B-A7867FC86023}&gt;RsFxFt.Dll::RsFxNsoInitialize failed: Error 0x80070005 (-2147024891)<br \/>\n&lt;{C580416B-A13E-4ECD-B61B-AAFAE39E5E35}&gt;Failed to initialize the CFsaShareFilter interface<br \/>\n&lt;{1038F43D-3391-45F7-B1B3-BADF26459429}&gt;Failed to initialize CFsaShareFilter: Error 0x80070005 (-2147024891)<br \/>\n2017-04-14 10:25:23.38 spid4s FILESTREAM: effective level = 0, configured level = 2, file system access share name = \u2018SQL2016\u2019.<\/p>\n<p><strong>UPDATE<\/strong> <strong>5\/10\/2017<br \/>\n<\/strong>The fix for the issue is released by Windows team as part of May security update <a href=\"https:\/\/support.microsoft.com\/en-us\/help\/4016871\">KB 4016871<\/a>. This update will be downloaded and installed automatically from Windows Update. To get the stand-alone package for this update, go to the\u00a0<a href=\"http:\/\/catalog.update.microsoft.com\/v7\/site\/Search.aspx?q=KB4016871\">Microsoft Update Catalog<\/a>\u00a0website. 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).<\/p>\n<p><strong>Workaround<br \/>\n<\/strong>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 <a href=\"https:\/\/support.microsoft.com\/en-us\/help\/4016871\">Windows 10 Update.<\/a><\/p>\n<ul>\n<li>Change the SQL Server service startup account to built-in account LocalSystem<\/li>\n<li>Change the SQL Server service startup account to a domain user account with local admin privileges on the system<\/li>\n<li>If you use virtual account [NT SERVICE\\MSSQL$InstanceName] as service startup account, please make this account a member of the local administrators group<\/li>\n<li>Uninstall Creators Update and fall back to the previous Windows build<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>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 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":244,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[3,6],"tags":[54,63,64,60,46,43,49,71,75,52,9,29,79,102,28,58],"class_list":["post-242","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mswin","category-security","tag-access","tag-ad","tag-dns","tag-domain","tag-file","tag-http","tag-https","tag-hypervisor","tag-ie","tag-log","tag-microsoft","tag-server","tag-sql","tag-update","tag-windows","tag-windows-10"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/www.web-workers.ch\/wp-content\/uploads\/2017\/05\/W10-Redstone1.jpg?fit=1200%2C496&ssl=1","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p8sxjX-3U","jetpack-related-posts":[],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.web-workers.ch\/index.php\/wp-json\/wp\/v2\/posts\/242","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.web-workers.ch\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.web-workers.ch\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.web-workers.ch\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.web-workers.ch\/index.php\/wp-json\/wp\/v2\/comments?post=242"}],"version-history":[{"count":2,"href":"https:\/\/www.web-workers.ch\/index.php\/wp-json\/wp\/v2\/posts\/242\/revisions"}],"predecessor-version":[{"id":248,"href":"https:\/\/www.web-workers.ch\/index.php\/wp-json\/wp\/v2\/posts\/242\/revisions\/248"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.web-workers.ch\/index.php\/wp-json\/wp\/v2\/media\/244"}],"wp:attachment":[{"href":"https:\/\/www.web-workers.ch\/index.php\/wp-json\/wp\/v2\/media?parent=242"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.web-workers.ch\/index.php\/wp-json\/wp\/v2\/categories?post=242"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.web-workers.ch\/index.php\/wp-json\/wp\/v2\/tags?post=242"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}