2

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?

5

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.

  • Even when creating the Instance, this rule stands true. Plus, by using folders you ensure the directories are clear. Sql_dat here, sql_log there. I personally would use the scheme DISK1/sql_dat/mydata.ndf as this allows for scalability. – clifton_h Nov 17 '18 at 20:04
  • Thanks for the answer, but this doesn't resolve the underlying issue. Adding a folder is a workaround for sure but not what I'm trying to accomplish. – Craig Efrein Nov 19 '18 at 10:03
  • @CraigEfrein I've added some more details. Adding a directly really is the right answer. Anything you have to do in order to put the files into the root of the directory is a work-around. SQL Server simply isn't designed to work that way. – AMtwo Nov 19 '18 at 15:16
  • @AMtwo, Hi, I don't agree with your answer. SQL Server in my experience is perfectly capable of saving directly to the root of a drive. If you can provide clear proof from Microsoft that database files can't be saved to the root of a disk, please add that information to your answer. – Craig Efrein Nov 20 '18 at 8:17
  • @CraigEfrein I didn't say it wasn't possible. I said Microsoft recommends against it. In the world of database administration there are lots of things that you can do, but should not. This is one of them. – AMtwo Nov 20 '18 at 11:59
3

Be aware that SQL Server 2016 reports Mounted Drives as not having enough space when seeding an AlwaysOn database - but works ok, if you carry on !

  • Does this happen only in version 2016 or in other versions, too? – ypercubeᵀᴹ Feb 15 at 13:24
  • Thanks for the warning, didn't really notice since I used backup/restore to prep the replica – Craig Efrein Feb 15 at 13:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.