SQL Server – Can’t Create Database on Volume Mounted as Folder (Error Msg 5123 & 1802)

permissionssql serverwindows-server

SQL Server 2016 SP1 + CU8 Installed on Windows Server 2016 Standard Edition.

Everything was working fine for a while and I was able to create databases until for some reason unbeknownst to me, a permissions issue popped up on my mounted volumes.

Msg 5123, Level 16, State 1, Line 1
CREATE FILE encountered operating system error 5(Access is denied.) 
while attempting to open or create the physical file 
'D:\MSSQL13.MSSQLSERVER\MSSQL\Data\UserDB\test.mdf'.
Msg 1802, Level 16, State 4, Line 1
CREATE DATABASE failed. Some file names listed could not be created. 
Check related errors.

The folder D:\MSSQL13.MSSQLSERVER\MSSQL\Data\UserDB is actually a volume mounted as a folder within the D:\MSSQL13.MSSQLSERVER\MSSQL\Data directory.

enter image description here

I googled around for hours and every link kept babbling on about ownership and icacls, and takecontrol, but that isn't the problem here. I want to be clear that the service account running SQL Server on this instance has FULL CONTROL over this mounted volume and what's above it too.

I verified Effective Access for the service account and it has explicitly granted FULL control.

My suspicion, is that it has something to do with granting permissions to a folder mounted as a volume instead of a disk. This i.s because in my tests, I can create a database with its files in the root of D:\ without any problems.
D:\ is also a volume using the same type of storage.

Also, my own user has full control, but when I try to create a file, I get this warning.

enter image description here

Strangely enough, I can add a folder to the UserDB folder/volume without difficulty.

How do I make this volume, mounted as a folder in the D:\ drive writeable for the MSSQL account?

Best Answer

Creating SQL Server Data files at the root of a volume is problematic, due to permissions issues. I always recommend creating a folder on the root of the volume, then placing data files inside that folder (this is also Microsoft's recommendation). Setting & verifying permissions at the root of the drive does not always work the same as setting permissions at the folder level.

When mounting the volume as a lettered drive, this means your data files would never go directly in D:\ but instead in D:\SomeFolder\. In your case, you are using a mount point where UserDb is the root of the volume. I'd suggest creating an additional folder inside your mount point: D:\...\Data\UserDb\SomeFolder\. Then, set permissions to make sure the SQL Server service account has full control for the SomeFolder directory.

Note that this guidance from Microsoft is still true:

Do not install SQL Server to the root directory of a mount point, always specify a subdirectory for all files. This has to do with how permissions are granted. If you must put files in the root of the mount point you must manually manage the ACLs/permissions.

And also this more explicit advice for database files specifically:

Warning: SQL Server does not support use of mount volume / mount point root directories for SQL Server databases.

Placing files directly in the root of the mount point means you'll have to manage permissions directly on every file to ensure they are correct. In the case of creating a new database (either from backup or from model), this can be problematic. As a result, it is best practice to never put files in the root of the mount point or drive. You can make it work, but it's not supported and not a good idea.

Related Question