Guidelines for Setting SQL Permissions on Mount Point Folders

You can use Windows Disk Management to assign a mount-point folder path (rather than a drive letter) to a volume. In this manner you can access a new volume via a child folder path of a different volume’s drive letter. For example, say you have three 100GB volumes Vol1, Vol2, Vol3. We can initially mount Vol1 using a drive letter, such as D. We can then mount the other two drives as child folders of the D: drive:

• D:  -- this is Vol1, max size=100GB
• D:\FolderForVol2   - this is Vol2, max size=100GB
• D:\FolderForVol3   - this is Vol3, max size=100GB

Note, the maximum size for Vol1 has not increased to 300GB. These are 3 separate volumes.

What is not so well known is how permissions should be assigned. Although the mount-point root folders may look like regular folders and are accessed in the same way folders are accessed, they are not regular folders. As a result, when you set permissions on a mount-point root folder, permissions are not inherited from the “parent volume” the same way as regular folders. In fact, they are not inherited at all. This is because although it appears the mounted volume is a child of the “parent volume”, it is not. You are simply accessing the mounted volume via a path from the “parent volume”. Looking at the example above, while it appears that “D:\FolderForVol3” is a child of “D:”, it is not. You are simply accessing Vol3 via a named path called “D:\FolderForVol3”.  The volumes referenced by “D:” and “D:\FolderForVol3” are completely separate and have their own individual permissions.  They can even have different file systems (NTFS, FAT, etc). A mounted folder is simply an association between a volume and a folder on another volume.  Think of it as a kind of shortcut or alias.

• Assign a mount point folder path to a drive  https://technet.microsoft.com/en-us/library/cc753321.aspx
• Mounted Folders https://msdn.microsoft.com/en-us/library/windows/desktop/aa365733(v=vs.85).aspx

Gotchas

Unfortunately, it is still possible to set/view permissions on the mount-point root folder via Windows Explorer, which can lead to unexpected results because the permissions of the mount-point root folder may seem valid and you can see “proper” inherited permissions, however these are not the permissions applied to the mounted volume. 

• https://connect.microsoft.com/SQLServer/feedback/details/569895/installation-of-sql-server-2008-r2-on-mount-points-fails

 

Guidelines

1. It is recommended that you do not place any files directly in the mount-point root folder. This will make permissions management much simpler, because the tendency is to always check the folder permissions, which in this case is misleading. Instead, create a subfolder under the mount-point root folder, and set the proper permissions to that subfolder.   Since the subfolder is a regular folder, the folder permissions you observe and set are indeed the permissions being applied. So using the previous example, you would want to create a new folder: D:\FolderForVol3\SubfolderXYZ. Now, set your folder permissions against that new SubfolderXYZ folder as you normally would.
2. If you absolutely must place items directly in the mount-point root folder (Not the recommend approach), then you will need to set volume permissions, not folder permissions.  Recall, that this is because the mount-point root folder permissions are not the permissions which will actually get set on the mounted volume (because the mount-point root folder is not a real folder). You can set volume permissions as follows:
   • Start->run->diskmgmt.msc (View the properties of a volume  https://technet.microsoft.com/en-us/library/cc740097.aspx )
   • Select the volume->Properties->Security tab
3. If you are adding a new folder for SQL to use, be aware of the required permissions for SQL access:
   • Beginning with SQL Server 2012 permissions are assigned to the per-service SID for each of its services. https://msdn.microsoft.com/en-us/library/jj219062.aspx
   • Configure Windows Service Accounts and Permissions  https://msdn.microsoft.com/en-us/library/ms143504.aspx

 

References

• https://blogs.msdn.com/b/pamitt/archive/2011/10/29/considerations-with-using-mount-points-in-sql-server-2008-r2-failover-clusters.aspx
• https://blogs.msdn.com/b/cindygross/archive/2011/07/05/using-mount-points-with-sql-server.aspx

 

Fany Carolina Vargas | SQL Dedicated Premier Field Engineer | Microsoft Services