Sql-server – the default account/group for SQL 2008 R2 cluster permissions and can you create/add them

file-permissionspermissionssql serversql-server-2008windows-server-2008

Last night we had an issue where the permissions were altered a SQL 2008 R2 Cluster Instance, such that the instance could no longer access the shared drives where the system databases were installed. While we corrected it by adding the service accounts to the directories and granted them full control, I would still like to understand the root cause/default setup.

When I perform a cluster install, I can see a grant to the cluster resource drive to an MSSQL$ account/group and that account/group is granted full control of anything in that resource. I don't need to add the service accounts and the cluster works correctly, with the ability to fail over. However, if I add a LUN to that cluster resource (mount point) or if someone goes in and removes that MSSQL$ from one of the cluster resources, I can't add that same account/group and grant it permissions, as Windows will not find it in any of the security objects that can be added.

So can anyone shed light on what this account/group is and is there a way to add it to a folder AFTER a cluster install?

This is SQL 2008 R2 RTM on Windows 2008 R2.

Best Answer

yes... for me the instance was called IT so the user that I had to configure on the folder after I moved it was

"NT SERVICE\MSSQL$IT"

Is that what you are talking about?

Related Solutions

Sql-server – How to tell what user account is being used to perform an action (in context of SQL Server)

Looks like SSMS is causing this confusion. My guess is that having to select the files in a dialog box means that the account SSMS is running under also needs permissions to the file.

As long as the service account has permission to the file, then you should be able to attach the database using t-sql.

Try using the sp_ attach _db You'll need to login as sa or use setuser or use the CREATE DATABASE... FOR ATTACH

If you really must use SSMS & the thought of T-SQL makes you want to throw-up then create a user account for the sql server service to run under & then use this account to start SSMS.

How to Assign Permissions to ApplicationPoolIdentity Account

Update: The original question was for Windows Server 2008, but the solution is easier for Windows Server 2008 R2 and Windows Server 2012 (and Windows 7 and 8). You can add the user through the NTFS UI by typing it in directly. The name is in the format of IIS APPPOOL\{app pool name}. For example: IIS APPPOOL\DefaultAppPool.

IIS APPPOOL\{app pool name}

Note: Per comments below, there are two things to be aware of:

Enter the string directly into the "Select User or Group" and not in the search field.
In a domain environment you need to set the Location to your local computer first.

Reference to Microsoft Docs article: Application Pool Identities > Securing Resources

Original response: (for Windows Server 2008) This is a great feature, but as you mentioned it's not fully implemented yet. You can add the app pool identity from the command prompt with something like icacls, then you can manage it from the GUI. For example, run something like this from the command prompt:

icacls c:\inetpub\wwwroot /grant "IIS APPPOOL\DefaultAppPool":(OI)(CI)(RX)

Then, in Windows Explorer, go to the wwwroot folder and edit the security permissions. You will see what looks like a group (the group icon) called DefaultAppPool. You can now edit the permissions.

However, you don't need to use this at all. It's a bonus that you can use if you want. You can use the old way of creating a custom user per app pool and assigning the custom user to disk. That has full UI support.

This SID injection method is nice because it allows you to use a single user but fully isolate each site from each other without having to create unique users for each app pool. Pretty impressive, and it will be even better with UI support.

Note: If you are unable to find the application pool user, check to see if the Windows service called Application Host Helper Service is running. It's the service that maps application pool users to Windows accounts.

Related Topic