locked
SQL Server 2005 Cluster domain groups RRS feed

  • Question

  • We have an SQL Server 2005 cluster installation on Windows. At installation time of the cluster, we defined three domain groups for accessing the cluster:

    ·         AD\DG_Cluster

    ·         AD\DG_Cluster_FullText

    ·         AD\DG_Cluster_SysAdmin

    In all three of these domain groups, the same one user (AD\Cluster_svc) is specified as a member. SQL Server creates logins for and assigns the following server roles to the domain groups:

    ·         AD\DG_Cluster: sysadmin

    ·         AD\DG_Cluster_FullText: no roles or db access.

    ·         AD\DG_Cluster_SysAdmin: sysadmin

    This results in the user AD\Cluster_svc getting two different type of roles: none and sa.

    In our situation, it makes no sense to have three different domain groups for the cluster as they contain only one user (AD\Cluster_svc).

    Two questions:

    ·         Is it possible to specify only one domain group for the cluster without re-installing the whole cluster and how could we achieve this?

    ·         Why is the AD\DG_Cluster_FullText domain group login assigned no roles (as well in the instance as in any database)?

     


    Peter Elzinga
    Monday, March 8, 2010 10:55 AM

Answers

  • These groups not only have SQL Server permissions but also DACL entries on SQL folders.

    SQL Server AD Group will have Full Control on DATA, LOG, BACKUP, FTData folders in the SQL Installation path.
    SQL FT  AD Group will have Full Control on FTData folder.
    SQL Agent  AD Group will have Full Control on LOG folder.

    Does that makes sense for using 3 different groups for these 3 services?

    Answer for question 1:
    Yes. You can change these group by following this approach:
    Step 1: Download PsGetSID from http://technet.microsoft.com/en-us/sysinternals/bb897417.aspx
    Step 2: Run PsGetSID <FT Group Name> to get SID value. SID value would be like S-1-5-21-1602856525-803909848-2320068549-1118
    Step 3: Locate key AGTGroup, FTSGroup under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x\Setup where x will be your Instance ID and you can note that SID Values you got from PsGetSID is matching with registry value.
    Step 4: Change the values of these keys to new SID (If you create new group in AD, then retreive SID for this group using PsGetSID else if you are planning to use SQL Server Group then copy paste SID from SQLGroup key to FTSGroup, AGTGroup)
    Step 5: Add the new group to SQL Server if you are adding new group and Remove the unwanted group from SQL Server login
    Step 6: Provide necessary folder permissions to this group on DATA, BACKUP, LOG... folders

    Answer for question 2:
    SQL Agent account needs sysadmin permission to connect to SQL Server, thus why you see sa given to SQLAGT Group
    SQL Fulltext Service doesn't need this permission and just needs public server role.

    Make sure you add the SQL Server service account manually to the new domain group created.

    Instead of this manuall approach, Refer "Method 2" given in the KB article http://support.microsoft.com/?kbid=915846

    Method 2 will not reinstall SQL Server, it will just update the domain groups and registry

    Reply if you have any clarifications..


    Thanks!
    Sakthi
    Tuesday, March 9, 2010 2:17 PM

All replies

  • These groups not only have SQL Server permissions but also DACL entries on SQL folders.

    SQL Server AD Group will have Full Control on DATA, LOG, BACKUP, FTData folders in the SQL Installation path.
    SQL FT  AD Group will have Full Control on FTData folder.
    SQL Agent  AD Group will have Full Control on LOG folder.

    Does that makes sense for using 3 different groups for these 3 services?

    Answer for question 1:
    Yes. You can change these group by following this approach:
    Step 1: Download PsGetSID from http://technet.microsoft.com/en-us/sysinternals/bb897417.aspx
    Step 2: Run PsGetSID <FT Group Name> to get SID value. SID value would be like S-1-5-21-1602856525-803909848-2320068549-1118
    Step 3: Locate key AGTGroup, FTSGroup under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x\Setup where x will be your Instance ID and you can note that SID Values you got from PsGetSID is matching with registry value.
    Step 4: Change the values of these keys to new SID (If you create new group in AD, then retreive SID for this group using PsGetSID else if you are planning to use SQL Server Group then copy paste SID from SQLGroup key to FTSGroup, AGTGroup)
    Step 5: Add the new group to SQL Server if you are adding new group and Remove the unwanted group from SQL Server login
    Step 6: Provide necessary folder permissions to this group on DATA, BACKUP, LOG... folders

    Answer for question 2:
    SQL Agent account needs sysadmin permission to connect to SQL Server, thus why you see sa given to SQLAGT Group
    SQL Fulltext Service doesn't need this permission and just needs public server role.

    Make sure you add the SQL Server service account manually to the new domain group created.

    Instead of this manuall approach, Refer "Method 2" given in the KB article http://support.microsoft.com/?kbid=915846

    Method 2 will not reinstall SQL Server, it will just update the domain groups and registry

    Reply if you have any clarifications..


    Thanks!
    Sakthi
    Tuesday, March 9, 2010 2:17 PM
  • Hi Sakti,

    Thank you very much for your very clear reply. You have helped us a lot.

    We are going to try this out in our test environment which is also a cluster solution.
    When this test is satisfactory, we will apply it also to our production environment.
    If anything pops up during this process, I will post my findings here.

    Kind regards,

    Peter Elzinga
    Monday, March 22, 2010 9:52 AM