Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server 2008 R2 Upgrade Failure due to Security Group upgrade rule


By:   |   Read Comments (8)   |   Related Tips: More > Upgrades and Migrations


Share your SQL Server knowledge and make some money too!


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 would not allow us to proceed and disabled the "Upgrade Rules" page as shown below.

SQL Server Upgrade to SQL Server 2008 R2 Wizard Security Group SID (Secuirty Identifer))

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:

Rule "Security Group SID (Security Identifier)" failed. One or more selected features for upgrade have failed the SID check. See the rules documentation at http://go.microsoft.com/fwlink/?LinkId=94001 for information on how to resolve.

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 save 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:

SQL Server Setup Bootstrap Log

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 should 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 would 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:

Windows Registry

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 would be part of all the group accounts.

WHOAMI /GROUPS
WHOAMI /Groups command and output

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 should recognize the correct SID values enable you to proceed past the "Upgrade Rules" interface.

Successful SQL Server Upgrade Rules
Next Steps
  • As you work through your upgrade preparation, be sure to include the steps from this tip as a preliminary check.
  • Check out the remainder of the SQL Server Upgrade Tips.


Last Update:


signup button

next tip button



About the author
MSSQLTips author Manvendra Singh Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Monday, May 08, 2017 - 7:55:22 AM - RITESH RAMESH GUJARAN Back To Top

 Hi Manvendra,

 

Your post is excellent but in my case SID in registry hive and actual SID are the same but still the upgrade fails with same SID rule.

PLease help whatsapp me on 9970585550.

 

Below is the log :

 

2017-05-08 13:19:22 Slp: Initializing rule      : Security Group SID (Security Identifier)

2017-05-08 13:19:22 Slp: Rule applied features  : ALL

2017-05-08 13:19:22 Slp: Rule is will be executed  : True

2017-05-08 13:19:22 Slp: Init rule target object: Microsoft.SqlServer.Configuration.SetupExtension.SqlSidRule

2017-05-08 13:19:22 Slp: SqlSidRule.QueryDatastore for InstanceMajorVersion - Querying datastore for major version value for instance MAGNESIUM05_UAT

2017-05-08 13:19:22 Slp: SqlSidRule.Init - Instance MAGNESIUM05_UAT matches version requirement (version = 9)

2017-05-08 13:19:22 Slp: SqlSidRule.Init - SQL is being upgraded

2017-05-08 13:19:22 Slp: SqlSidRule.Init - AS is not being upgraded

2017-05-08 13:19:22 Slp: SqlSidRule.Init - FTS is being upgraded

2017-05-08 13:19:22 Slp: SqlSidRule.GetSidValue - opening SQL Reg hive SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup

2017-05-08 13:19:22 Slp: Sco: Attempting to create base registry key HKEY_LOCAL_MACHINE, machine 

2017-05-08 13:19:22 Slp: Sco: Attempting to open registry subkey SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup

2017-05-08 13:19:22 Slp: SqlSidRule.GetSidValue - Unable to open registry key SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup

2017-05-08 13:19:22 Slp: SqlSidRule.GetSidValue - opening SQL Reg hive SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup

2017-05-08 13:19:22 Slp: Sco: Attempting to create base registry key HKEY_LOCAL_MACHINE, machine 

2017-05-08 13:19:22 Slp: Sco: Attempting to open registry subkey SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup

2017-05-08 13:19:22 Slp: SqlSidRule.GetSidValue - Unable to open registry key SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup

2017-05-08 13:19:22 Slp: SqlSidRule.GetSidValue - opening SQL Reg hive SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup

2017-05-08 13:19:22 Slp: Sco: Attempting to create base registry key HKEY_LOCAL_MACHINE, machine 

2017-05-08 13:19:22 Slp: Sco: Attempting to open registry subkey SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup

2017-05-08 13:19:22 Slp: SqlSidRule.GetSidValue - Unable to open registry key SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup

2017-05-08 13:19:22 Slp: SqlSidRule.EvaluateFlags - SQL Sid check did not pass, expect rule failure

2017-05-08 13:19:22 Slp: SqlSidRule.EvaluateFlags - IsSQLFeatureUpgraded is True, IsSQLGroupExist is False IsAgentGroupExist is False

2017-05-08 13:19:22 Slp: SqlSidRule.EvaluateFlags - AS Sid check passed

2017-05-08 13:19:22 Slp: SqlSidRule.EvaluateFlags - FTS Sid check did not pass, expect rule failure

2017-05-08 13:19:22 Slp: SqlSidRule.EvaluateFlags - IsFTSFeatureUpgraded is True, IsFTSGroupExist is False

 

 


Wednesday, September 17, 2014 - 11:57:51 PM - B. Tucker Back To Top

Thanks -- Worked -- with a little patience -- note my instances with bad SIDS were located in  Computer\HKLM\Software\Wow6432Node\Microsoft\Microsoft SQL Server\MSSQL.1\Setup


Monday, October 08, 2012 - 2:23:01 PM - c0rtex Back To Top

What if the sql server is 2 servers in a 2k8 failover cluster. fts, agent and sql server groups are domain groups. I have followed your guide but can not get things to work.


Tuesday, September 18, 2012 - 11:39:30 AM - Satinder Thakur Back To Top

Good one, Manvendra! What could be the possible reason for these orphaned SIDs?

 


Sunday, June 17, 2012 - 6:17:54 PM - SÚrgio Douglas Back To Top

Work perfect. 

 

Thank you!


Thursday, May 03, 2012 - 10:52:51 AM - Manvendra Back To Top

Hi Bill,

If you mean the colour coding representing a status bar during a upgrade window then that means blue bar means your step is processing, red means that particluar rule is failed and green represents a successful rule check.

It would be great if you can elebarote your issue.


Wednesday, May 02, 2012 - 5:18:13 PM - Bill Swan Back To Top

What does it mean when the server name is highlighted in red font? Or blue i am having the same issue with both, the sooner you can respond to this the better.

Thank you,

Bill


Saturday, February 18, 2012 - 12:34:16 PM - Pojo Back To Top
Awesome problem identification, analysis, and solution presentation! The author did an excellent job in sharing how he pulled apart the error and dissected it to reveal the sources to go to for further examination and correction. Thank you for the level of detail and explanation. This example answers to how I learn. I very much look forward to reviewing more troubleshooting tips from this author.

Learn more about SQL Server tools