SQL Server fails to check the security group Sid (Security Identifier ).

The security group upgrade rule failed error: Rule "Security Group Sid (Security Identifier)" failed was reported in the forum today. This error occurs because the Windows Local Group Sid information created by SQL Server does not match the SID in the registry. You need to use whoami/groups to find the correct Sid and replace the error Sid in the registry, the problem can be solved.

 

For detailed steps, refer:

 

Problem

Last month I was working on a SQL Server 2005 to SQL Server 2008 r2
Upgrade. unfortunately, we were not able to complete our upgrade because of a failing a upgrade rule named "Security Group Sid (Security Identifier )". the SQL server upgrade setup wizard did not enable the 'Next' button in order to proceed with the upgrade
Until this failure was resolve. In this tip, I will describe the reason for this failure and how I fixed the issue in order to have a successful upgrade.

Solution

As I described in the problem statement, we were not able to upgrade our SQL Server because one of the upgrade rules I. e. "Security Group Sid (Security Identifier)" failed during the setup process. the SQL Server 2008 R2 upgrade Setup Wizard wocould not allow
Us to proceed and disabled the "upgrade rules" page as shown below.

For the detailed error message, I clicked on "failed" hyperlink in the Status column to read more about the error, but this was not enough to help me understand the issue. here is the screen shot:

So I decided to check the upgrade log files to find out the main reason why this rule failed. the upgrade and Installation Log Files are saved in the setup Bootstrap folder. as such, my log files are stored in the c: \ Program Files \ Microsoft SQL Server \ 100 \ setup
Bootstrap \ log \ 'Folder.

In the log file, I found the following detailed error messages:

As per the above error log messages highlighted in red, you can see SQL server setup is trying to read account information from the Windows registry. what the Upgrade Wizard is doing is reading account information I. e. SIDS from the registry and then searching
For the correct account based on the exact Sid (Security Identifiers) for the groups in the Local Computer Management. as such, the SIDS shocould be the same in Computer Management and the Windows registry hive for all groups. unfortunately in this circumstance
SQL Server Setup is not able to find the account information for three accounts.

Those account SIDS are the SQL Server service groups account that were created during the SQL Server 2005 installation. the error message indicates that SQL server is not able to map the SIDS to the names of the service group accounts. all three accounts
Can be found in SQL Server as logins and in Computer Management. the sqlgroup account is used for the SQL Server database engine, agtgroup is used for SQL Server Agent and ftsgroup is used for full text search, SQL Server creates a system account for all
Its features when you install it. if you have SQL Server reporting services and SQL Server Analysis Services Installed then a additional accounts wocould be created and assigned SIDS. below are the SQL Server 2005 group account names:

• Sqlserver2005dtsuser $ servername
• Sqlserver2005msfteuser $ servername $ MSSQLServer
• Sqlserver2005mssqlserveradhelperuser $ servername
• Sqlserver2005mssqluser $ servername $ MSSQLServer
• Sqlserver2005notificationservicesuser $ servername
• Sqlserver2005sqlagentuser $ servername $ MSSQLServer
• Sqlserver2005sqlbrowseruser $ servername

Resolving the upgrade SQL Server 2008 R2 Security Group Sid (Security Identifier) issue

Below outlines the step by step process which I followed to resolve this issue.

Step 1-After reading log file I decided to look into registry location which is mentioned in error log file:

My Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup

Here are the applicable parameters from the Registry location:

As you can see in the above screenshot, the SIDS are the same as in error log. As such, our next step is to find out the SIDS for all these system accounts in Computer Management.

Step 2-At the DOS prompt, I used the "whoami" command to find out the system accounts in Computer Management. this utility can be used to get the user name and group information along with the respective Security Identifiers (SID), privileges,
Logon identifier (logon ID) for the current user on the local system. i. e. who is the current logged on. if you are not part of these accounts then add yourself in all of the group accounts. normally the SQL Server service account wocould be part of all
Group accounts.

WHOAMI /GROUPS

Now you can see the issue. the SID for same account in above screenshot is different than the SID in registry or error log. so now our next step will be copy to the Sid from the above screenshot for each group which is causing the upgrade failure and replace
This value in the Registry Hive.Make sure you backup your registry before making any changes.

Step 3-To replace the registry value double click on the group which is highlighted in red from step 1 and replace the SID with the value from the whoami command in the above screenshot. follow this process for all three groups and replace
The appropriate values.

Step 4-Now reboot the server and start your upgrade again. This time the upgrade to SQL Server 2008 R2 wizard shocould recognize the correct Sid values enable you to proceed past the "upgrade rules" interface.

Source: http://www.mssqltips.com/sqlservertip/2569/sql-server-2008-r2-upgrade-failure-due-to-security-group-upgrade-rule/