Good morning Folks! Today I will take you through a setup issue that many of you might have run into when trying to install SQL Server 2005 (especially on a cluster). I’ve tried to list down all possible solutions to the following errors one faces while installing/patching SQL Server 2005.
Hope you find this useful and informative. The mantra for this post is this: Always take corrective actions suggested below only if you understand the change you are making. Please do not “try” these to give it a whirl to see if it works. If you have “ANY” doubts in your mind whatsoever, that should be the time to think first, act later. If any doubts persist, Microsoft PSS is there to help you.
1) MSP Error: 29512 SQL Server Setup was unable add user NT AUTHORITY\SYSTEM to local group
2) MSP Error: 29512 SQL Server Setup was unable add user YODA\starwars to local group YODA\SQL_Server_Services
3) MSP Error: 29512 SQL Server Setup was unable add user USER@DOMAIN.COM to local group COMPUTER\SQLCLUSTER
You will see this in summary.txt when setup fails with this error:
Last Action : InstallFinalize
Error String : SQL Server Setup was unable add user NTSERVER\SQLUser to local group NTSERVER\SQLSrvAgentgroup.
Error Number : 29512
1. The service account for SQL clustered service must be a direct member of the domain group. This service account must not be a member of the domain group through a group membership
2. Service account for SQL Server must be in the following format. DomainName\GroupName.
3. Domain groups must be within the same domain as the machine accounts
4. Do not change the service accounts using services.msc. This is all the more important if it’s a Cluster. Always, always use SQL Server Configuration Manager.
Items to Check:-
1. Make sure that the account performing the setup is a local admin on both nodes and also it should be a domain account. (Applicable only for clustered installations of SQL Server).
2. Run setup with the SQL Server Service Account. The accounts running setup should have the necessary security privileges to perform the setup.
3. Check if this group for which you got error is a Domain Local or Domain Global Group. (Your Windows AD admin will be able to tell you this )
4. Check what kind of permissions NT AUTHORITY/SYSTEM has on the SQL instance being patched (Is it sysadmin in SQLServer ? (Applicable only if account with error is ntauthority\system)
5. Find out if you have any custom Group Policies in place on these Cluster Nodes, which might prevent SQL from adding the user to the group.
6. Check if ANY of the SQL service startup account is in the format User@Domain.com. If it is, change it to format Domain\User and restart the services.
The common reasons for this error are,
a) The Service accounts are specified in the format User@Domain.com
b) The account running setup does not have permissions to add the user if it doesn’t exist as part of the groups.
c) There is a SID mismatch.
7. Make sure the SQL Service startup account is NOT "Local System" on both the nodes (for clustered instances)
8. Please make sure the following keys exist on both the nodes.
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)
Note: Some of the above not may not exist, depending on the components of SQL Server you have installed. If you only have database services with default settings – SQL, Agent and FTS group should be present.
a) Check the SID's for the above values to make sure they are mapped to the correct domain groups.
b) Check SID values stored in registry on both the nodes to make sure they match & map to the correct AD group.
c) You can use the psgetsid tool to verify this.
C:\> Psgetsid.exe DOMAIN\UserName
SID for DOMAIN\UserName:
C:\> Psgetsid.exe S-1-5-21-2146773085-903363285-719344707-413872
Account for machine_name\S-1-5-21-2146773085-903363285-719344707-413872:
9. Check if you can ping the Domain controller using IP and NetBIOS name.
The reason for this is to make sure we are trying to reach the correct DC and add the account in the correct group. If this step fails, then we need to look at why we are not able to reach the DC.
10. Make sure there are no incorrect entries in the hosts file on both the nodes (C:\Windows\System32\drivers\etc\hosts)
11. Make sure you have verified every step in this MSDN article on permissions for account running setup. I highly recommend reading this article:
Domain Groups for Clustered Services
12. Ensure that the user you are getting the error for, is a direct member of the domain group. This is mandatory. The account must NOT be inheriting permissions through sub-groups.
13. Verify that setup account has permission to also query\edit the group and not just Add. If possible, grant the account "Full control" on AD group.
14. If Kerberos is enabled (default) on Windows 2003 entire authentication happens using tokens. Check http://support.microsoft.com/kb/935744
Note: If a user is a member of a group either directly or by membership in another group, the security ID (SID) for that group is added to the user's token. For a SID to be added to the user's token, the SID information must be communicated by using the Kerberos token. If the required SID information exceeds the size of the token, authentication is unsuccessful.
The MaxTokenSize defaults to 12,000 bytes in Windows 2003. Change this to 65535 as per KB935744 (requires reboot) and then re-run setup.
The token size can be big if the user is a member of lot of groups and exceeds 12000 bytes. So even if his permissions are correct, due to large token size, the authentication is unsuccessful and so SQL Setup reports failure in adding the user to the group. You will notice errors about token size exceeded in your Windows Event logs.
15. For standalone install/patching, make sure the SID value stores in registry for above mentioned groups point to the correct local groups. The local groups are these,
For default instance:
For named instances:
etc etc etc....
16. Run the setup by logging in as a DOMAIN ADMINISTRATOR account (as this has permissions to add/query/edit the AD group). This might not be possible for some you, but this is just another option ; as a Domain Admin will have all the permissions you would ever need. This would ensure setup goes past this error, if it was caused because of a permission issue.
Keep in mind, if the user is already present in the AD group, even then to find that information, we have to query the appropriate domain group to get this verified. So, if the user running the setup does not have permission to do this, then also we will fail with error saying "unable to add user".
NetGroupAddUser() is a WINAPI that can be used to add a user to an AD group. The default ACL permits only Domain Admins and Account Operators to use this function in your program.
Sudarshan NTechnical Lead, Microsoft SQL Server
Very helpful - Thanks so much for sharing.
thank you a million...extremely helpful