MSSQL Server – Service Accounts – Domain Group Requirements

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

Requirements

Operational – Computer Nodes

  1. Ensure that the Logged-In account that initiated and that is performing the installation is an Administrator on all the cluster nodes.

Existential

  1. 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.
    (http://msdn.microsoft.com/en-us/library/ms345196.aspx)

Operational – Active Directory

  1. If the Service Accounts are already part of the domain group then the User doing the install must have View Permissions
  2. 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)

Policies

  1. Make sure that existing “Active Directory Group Policies” will not prevent the installation process ability to add the Service Accounts to the Service Groups

Registry

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)

Name Nomenclature

  1. The service account will have to be referenced using the traditional syntax (DOMAIN-NAME\Account-Name) and not Account-Name@DOMAIN-NAME

Membership

  1. 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” –
http://msdn.microsoft.com/en-us/library/ms345196.aspx

Domain Group Name & SID Matching

  1. Since correlation between Service Group & Service ID is through SID mappings, the Domain Group names must match the SID IDs

(
use PSGetSid available @
http://technet.microsoft.com/en-  us/sysinternals/bb897417.aspx
to get name from SID, and SID from name
)

Uniqueness

  1. 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 –
    http://technet.microsoft.com/en-  us/sysinternals/bb897417.aspx

End of Life

  1. 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.
    http://technet.microsoft.com/en-  us/sysinternals/bb897417.aspx

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s