In Microsoft SQL Server 2005, Microsoft fleshed out implementation of Service Accounts and Service Accounts Domain Groups; especially for Clustered installations.
The requirements are a bit perplexing and probably worthwhile documenting. It goes without saying that an exhaustive list will take time, but here is a working copy.
Please keep in mind that thereafter, that is in subsequent versions such as SQL Server 2008 and SQL Server 2008 – R2, it appears that MS took a step back; especially with SIDS, etc.
But, less than downplay the situation in MS SQL Server 2005, let us at least take notes.
What are there use
On a SQL Server 2005 failover cluster, domain groups that are common to all cluster nodes are used to control access to registry keys, files, SQL Server objects, and other cluster resources. All resource permissions are controlled by domain-level groups that include SQL Server service accounts as members – http://msdn.microsoft.com/en-us/library/ms345196.aspx
Operational – Computer Nodes
- Ensure that the Logged-In account that initiated and that is performing the installation is an Administrator on all the cluster nodes.
- The domain and domain group names must already exist at the time Setup is run. If necessary, ask your domain administrator for the names of existing domain groups, or to create domain groups for your failover cluster. If domain groups are created immediately prior to running Setup, allow time for changes to propagate through a corporate network.
Operational – Active Directory
- If the Service Accounts are already part of the domain group then the User doing the install must have View Permissions
- On the other hand , if the Service Account(s) are not yet part of the Service Group(s), then the Account doing the install must have Add\Edit\View privileges on the domain group(s)
- Make sure that existing “Active Directory Group Policies” will not prevent the installation process ability to add the Service Accounts to the Service Groups
The following registry entries have to exist on each node:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\Setup\SQLGroup
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\Setup\AGTGroup
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\Setup\FTSGroup
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\Setup\ASGroup (for analysis services)
- The service account will have to be referenced using the traditional syntax (DOMAIN-NAME\Account-Name) and not Account-Name@DOMAIN-NAME
- The Service Account must also be explicit members of the Domain Group. It is not enough for membership to be merely through inheritance.
”The user must be a direct member of the domain group, not a member via sub-groups. Setup will not follow sub-group memberships to validate that the user account is in the domain group” –
Domain Group Name & SID Matching
- Since correlation between Service Group & Service ID is through SID mappings, the Domain Group names must match the SID IDs
use PSGetSid available @
to get name from SID, and SID from name
- Each service should use a different domain group. However, you can use the same domain group and the same account for all SQL Server services, you can use the same domain group and different accounts for each SQL Server service, or you can use a different domain group and a different account for each SQL Server service. To maintain the most granular control over permissions, use a different account and different domain group for each SQL Server service and for each virtual server in your domain –
- The SQL Server domain groups should not be shared with any other application.http://technet.microsoft.com/en- us/sysinternals/bb897417.aspx
End of Life
- SQL Server accounts will not be removed from the domain groups if SQL Server is uninstalled or if the accounts are changed. A domain administrator must ensure that all unwanted accounts are removed following removal of SQL Server.