{"id":30914,"date":"2024-02-16T12:48:08","date_gmt":"2024-02-16T11:48:08","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=30914"},"modified":"2024-02-16T13:58:33","modified_gmt":"2024-02-16T12:58:33","slug":"attach-database-error-smb-fileshare","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/attach-database-error-smb-fileshare\/","title":{"rendered":"Attach database error (SMB Fileshare)"},"content":{"rendered":"\n<p>Starting with SQL Server 2012, SQL Server supports databases using the <a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/database-engine\/install-windows\/install-sql-server-with-smb-fileshare-as-a-storage-option?view=sql-server-ver16\">Server Message Block (SMB)<\/a> file server as a storage option. For performance and reliability reasons, SMB 3.0 or later is preferred to use.<br>For architectures like <a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/sql-server\/failover-clusters\/windows\/always-on-failover-cluster-instances-sql-server?view=sql-server-ver16\">Always On Failover Cluster Instance (FCI)<\/a>, it can be a relevant alternative to cluster disks. To avoid a single point of failure (SPOF) scenario, the file shares should be high available.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-issue-description\">Issue description<\/h2>\n\n\n\n<p>In a production context, we set up a FCI infrastructure, in Multi-AZ, on AWS using <a href=\"https:\/\/aws.amazon.com\/fsx\/\">Amazon FSx<\/a> as file share target &#8211; also in multi-AZ deployment to avoid SPOF scenario.<br>Everything run smoothly until a Detach \/ Attach approach was used at some point. SQL Server triggered the following error message 5120 at the Attach phase:<br><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Msg 5120, Level 16, State 101, Line 12\nUnable to open the physical file \"\\fsx-server\\SharePath\\Database.mdf\". Operating system error 5: \"5(Access is denied.)\".\nMsg 1802, Level 16, State 7, Line 12\nCREATE DATABASE failed. Some file names listed could not be created. Check related errors.<\/code><\/pre>\n\n\n\n<p>Even if SQL Server service account has Full Control on the files, it cannot attach back after the detach process.<br>In some documentation, you can find the recommendation to enable <a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/t-sql\/database-console-commands\/dbcc-traceon-trace-flags-transact-sql?view=sql-server-ver16\">Trace Flag 1802<\/a>, which will disable ACLs change and impersonated access verification during attach &amp; detach operations. But another error (5123) appears anyway:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Msg 5123, Level 16, State 20, Line 11\nCREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file '\\fsx-server\\SharePath\\Database.mdf'.<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-detach-process\">Detach Process<\/h2>\n\n\n\n<p>To understand the root-cause of this error, let&#8217;s dig into the <a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/relational-databases\/databases\/attach-a-database?view=sql-server-ver16#Security\">Microsoft documentation<\/a> regarding detaching process.<br>When detaching the database files, here are the following permissions applying on the files:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Without impersonation: the SQL Server service account and members of local Windows Administrator group (i.e. FSx related) are granted FULL control on the database files<\/li>\n\n\n\n<li>With impersonation: only the account performing the operation is granted FULL control on the database files<\/li>\n<\/ul>\n\n\n\n<p>Initially without Trace Flag 1802, we were in the first situation &#8220;without impersonation&#8221; after detaching the database:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>ACLs inheritance is disabled on database files<\/li>\n\n\n\n<li>SQL Server service account has FULL Control on the database files<\/li>\n\n\n\n<li>Local Windows Administrator group of the File Server has FULL control on database files<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-attach-process\">Attach Process<\/h2>\n\n\n\n<p>Now, let&#8217;s now dig into the Attach process still from the <a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/relational-databases\/databases\/attach-a-database?view=sql-server-ver16#Security\">Microsoft documentation<\/a>.<br>When attaching database files to create the database in SQL Server, here are the following permissions which are applying to the files:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Without impersonation: only the SQL Server service account is added with FULL Control permissions<\/li>\n\n\n\n<li>With impersonation: the SQL Server service account and members of the local Windows Administrator group are granted FULL Control permissions<\/li>\n<\/ul>\n\n\n\n<p>The specificity of Attach process, compared to the creation of a database from scratch, it tries to apply NTFS changes on existing files. We will see this slightly difference is not insignificant.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-smb-security-requirements\">SMB Security Requirements<\/h2>\n\n\n\n<p>Here are the requirements in the context the database files are <a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/database-engine\/install-windows\/install-sql-server-with-smb-fileshare-as-a-storage-option?view=sql-server-ver16&amp;redirectedfrom=MSDN#security-considerations\">located on an SMB file share<\/a> for the SQL Server Service (domain) account:<\/p>\n\n\n\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-constrained wp-block-group-is-layout-constrained\">\n<ul class=\"wp-block-list\">\n<li>FULL Control share permissions<\/li>\n\n\n\n<li>FULL Control NTFS permissions on the SMB share folders<\/li>\n\n\n\n<li>SeSecurityPriviledge (Local Security Policy on the File Server) is required to change get\/set ACLs. This is true even if the account has FULL Control NTFS permissions on the database file<\/li>\n<\/ul>\n<\/div><\/div>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-issue-root-cause\">Issue root-cause<\/h2>\n\n\n\n<p>Attach process changes the current permissions of database files before creating the database in SQL Server. To do that, it requires to get\/set ACLs on the files: so SeSecurityPriviledge is required at the File Server side in order to avoid error 5120 &amp; 5123.<br>If you are managing the File server, this is a change you can implement and fix this issue. But in a context where SMB share is a Managed service, we were not able to fix it.<\/p>\n\n\n\n<p>Limitations on Attach process is not blocking because it exists other approaches &#8211; such as Backup &amp; Restore &#8211; but it can be disturbing when you are facing this issue without understanding the root-cause.<br>Similar to PaaS environments, Attach &amp; Detach is not supported as soon as you have a managed service on the Operating System hosting the database files.<br><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Starting with SQL Server 2012, SQL Server supports databases using the Server Message Block (SMB) file server as a storage option. For performance and reliability reasons, SMB 3.0 or later is preferred to use.For architectures like Always On Failover Cluster Instance (FCI), it can be a relevant alternative to cluster disks. To avoid a single [&hellip;]<\/p>\n","protected":false},"author":14,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[99],"tags":[133,3258,51],"type_dbi":[2874],"class_list":["post-30914","post","type-post","status-publish","format-standard","hentry","category-sql-server","tag-aws","tag-fsx","tag-sql-server","type-sql-server"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Attach database error (SMB Fileshare) - dbi Blog<\/title>\n<meta name=\"description\" content=\"When attaching a SQL Server database, you experience error 5120 or 5123 because of a lack of permissions. Let&#039;s explain why!\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/attach-database-error-smb-fileshare\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Attach database error (SMB Fileshare)\" \/>\n<meta property=\"og:description\" content=\"When attaching a SQL Server database, you experience error 5120 or 5123 because of a lack of permissions. Let&#039;s explain why!\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/attach-database-error-smb-fileshare\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2024-02-16T11:48:08+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-02-16T12:58:33+00:00\" \/>\n<meta name=\"author\" content=\"Nathan Courtine\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Nathan Courtine\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/attach-database-error-smb-fileshare\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/attach-database-error-smb-fileshare\/\"},\"author\":{\"name\":\"Nathan Courtine\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/38305b5ebdcdb4fb784fa31d760862d1\"},\"headline\":\"Attach database error (SMB Fileshare)\",\"datePublished\":\"2024-02-16T11:48:08+00:00\",\"dateModified\":\"2024-02-16T12:58:33+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/attach-database-error-smb-fileshare\/\"},\"wordCount\":617,\"commentCount\":0,\"keywords\":[\"AWS\",\"FSX\",\"SQL Server\"],\"articleSection\":[\"SQL Server\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/attach-database-error-smb-fileshare\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/attach-database-error-smb-fileshare\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/attach-database-error-smb-fileshare\/\",\"name\":\"Attach database error (SMB Fileshare) - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2024-02-16T11:48:08+00:00\",\"dateModified\":\"2024-02-16T12:58:33+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/38305b5ebdcdb4fb784fa31d760862d1\"},\"description\":\"When attaching a SQL Server database, you experience error 5120 or 5123 because of a lack of permissions. Let's explain why!\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/attach-database-error-smb-fileshare\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/attach-database-error-smb-fileshare\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/attach-database-error-smb-fileshare\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Attach database error (SMB Fileshare)\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/38305b5ebdcdb4fb784fa31d760862d1\",\"name\":\"Nathan Courtine\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g\",\"caption\":\"Nathan Courtine\"},\"description\":\"Nathan Courtine has more than four years of experience in Microsoft solutions. He is specialized in SQL Server installation, migration, performance analysis, best practices, etc. Moreover, he has a background in Oracle Java and .NET software and web development. Nathan Courtine is Microsoft Certified in Administering SQL Server 2012 Databases. Nathan Courtine holds an Engineer\u2019s Degree in Computer Science from the ENSISA (Ecole Nationale Sup\u00e9rieure d'Ing\u00e9nieurs Sud Alsace) in Mulhouse (F). His branch-related experience covers Public Sector, Automotive, IT, Financial Services \/ Banking, etc.\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/nathan-courtine\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Attach database error (SMB Fileshare) - dbi Blog","description":"When attaching a SQL Server database, you experience error 5120 or 5123 because of a lack of permissions. Let's explain why!","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/attach-database-error-smb-fileshare\/","og_locale":"en_US","og_type":"article","og_title":"Attach database error (SMB Fileshare)","og_description":"When attaching a SQL Server database, you experience error 5120 or 5123 because of a lack of permissions. Let's explain why!","og_url":"https:\/\/www.dbi-services.com\/blog\/attach-database-error-smb-fileshare\/","og_site_name":"dbi Blog","article_published_time":"2024-02-16T11:48:08+00:00","article_modified_time":"2024-02-16T12:58:33+00:00","author":"Nathan Courtine","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Nathan Courtine","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/attach-database-error-smb-fileshare\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/attach-database-error-smb-fileshare\/"},"author":{"name":"Nathan Courtine","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/38305b5ebdcdb4fb784fa31d760862d1"},"headline":"Attach database error (SMB Fileshare)","datePublished":"2024-02-16T11:48:08+00:00","dateModified":"2024-02-16T12:58:33+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/attach-database-error-smb-fileshare\/"},"wordCount":617,"commentCount":0,"keywords":["AWS","FSX","SQL Server"],"articleSection":["SQL Server"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/attach-database-error-smb-fileshare\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/attach-database-error-smb-fileshare\/","url":"https:\/\/www.dbi-services.com\/blog\/attach-database-error-smb-fileshare\/","name":"Attach database error (SMB Fileshare) - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2024-02-16T11:48:08+00:00","dateModified":"2024-02-16T12:58:33+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/38305b5ebdcdb4fb784fa31d760862d1"},"description":"When attaching a SQL Server database, you experience error 5120 or 5123 because of a lack of permissions. Let's explain why!","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/attach-database-error-smb-fileshare\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/attach-database-error-smb-fileshare\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/attach-database-error-smb-fileshare\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Attach database error (SMB Fileshare)"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/38305b5ebdcdb4fb784fa31d760862d1","name":"Nathan Courtine","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g","caption":"Nathan Courtine"},"description":"Nathan Courtine has more than four years of experience in Microsoft solutions. He is specialized in SQL Server installation, migration, performance analysis, best practices, etc. Moreover, he has a background in Oracle Java and .NET software and web development. Nathan Courtine is Microsoft Certified in Administering SQL Server 2012 Databases. Nathan Courtine holds an Engineer\u2019s Degree in Computer Science from the ENSISA (Ecole Nationale Sup\u00e9rieure d'Ing\u00e9nieurs Sud Alsace) in Mulhouse (F). His branch-related experience covers Public Sector, Automotive, IT, Financial Services \/ Banking, etc.","url":"https:\/\/www.dbi-services.com\/blog\/author\/nathan-courtine\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/30914","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/14"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=30914"}],"version-history":[{"count":29,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/30914\/revisions"}],"predecessor-version":[{"id":30996,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/30914\/revisions\/30996"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=30914"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=30914"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=30914"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=30914"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}