Attach database error (SMB Fileshare)

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 point of failure (SPOF) scenario, the file shares should be high available.

Issue description

In a production context, we set up a FCI infrastructure, in Multi-AZ, on AWS using Amazon FSx as file share target – also in multi-AZ deployment to avoid SPOF scenario.
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:

Msg 5120, Level 16, State 101, Line 12
Unable to open the physical file "\fsx-server\SharePath\Database.mdf". Operating system error 5: "5(Access is denied.)".
Msg 1802, Level 16, State 7, Line 12
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

Even if SQL Server service account has Full Control on the files, it cannot attach back after the detach process.
In some documentation, you can find the recommendation to enable Trace Flag 1802, which will disable ACLs change and impersonated access verification during attach & detach operations. But another error (5123) appears anyway:

Msg 5123, Level 16, State 20, Line 11
CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file '\fsx-server\SharePath\Database.mdf'.

Detach Process

To understand the root-cause of this error, let’s dig into the Microsoft documentation regarding detaching process.
When detaching the database files, here are the following permissions applying on the files:

• 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
• With impersonation: only the account performing the operation is granted FULL control on the database files

Initially without Trace Flag 1802, we were in the first situation “without impersonation” after detaching the database:

• ACLs inheritance is disabled on database files
• SQL Server service account has FULL Control on the database files
• Local Windows Administrator group of the File Server has FULL control on database files

Attach Process

Now, let’s now dig into the Attach process still from the Microsoft documentation.
When attaching database files to create the database in SQL Server, here are the following permissions which are applying to the files:

• Without impersonation: only the SQL Server service account is added with FULL Control permissions
• With impersonation: the SQL Server service account and members of the local Windows Administrator group are granted FULL Control permissions

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.

SMB Security Requirements

Here are the requirements in the context the database files are located on an SMB file share for the SQL Server Service (domain) account:

Issue root-cause

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 & 5123.
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.

Limitations on Attach process is not blocking because it exists other approaches – such as Backup & Restore – but it can be disturbing when you are facing this issue without understanding the root-cause.
Similar to PaaS environments, Attach & Detach is not supported as soon as you have a managed service on the Operating System hosting the database files.