none
Upgrade from SQL2005 - Security Group SID Upgrade Rule Fails

    Question

  • Hello,

    I am trying to upgrade from SQL2005 to the RTM of SQL2008 Standard that was recently released to MSDN on a 32-bit machine running Windows Server 2008 Standard. I get through the upgrade wizard to the "Upgrade Rules" step. On that step, the "Security Group ID (Security Identifier)" rule fails. If I click on the "Failed" status to get error details, the following details are shown...

    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.

    If I follow that link, however, it takes me to a page that has information about how to unblock applications in Windows Firewall. I would not have expected that. I have, however, turned off Windows Firewall on my server.

    Does anyone have any ideas what could be causing this issue?

    Thanks,
    Dave
    Tuesday, August 19, 2008 4:18 AM

Answers

  • That's good to know. If you replace SID as described in the previous post as follows, does setup pass the rule check?

     

    - Go to SQL Server registry key to replace the obsolete SID with the current one.  Those registry keys can be found like following example.

     

       [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup]

       "AGTGroup"="S-1-5-21-1606980848-1390067357-1417001333-1173"

       "SQLGroup"="S-1-5-21-1606980848-1390067357-1417001333-1174"

       "FTSGroup"="S-1-5-21-1606980848-1390067357-1417001333-1175"

     

    Thanks.

     

    Yuhong

    Tuesday, August 26, 2008 6:55 PM

All replies

  • That rule checks three service account group: SQL Engine, AS and FullText.  We need the detail log and see which one caused failure.

     

    Can you post the detail log file here?  The file is called "Detail.txt" under setup log foder.

     

    %ProgramFiles%\Microsoft SQL Server\100\Setup Bootstrap\LOG

     

    There are many folders from every run of setup.  The one we need is the latest run that can be identified with the time stamp.

     

    Thanks.

     

    Yuhong

     

    Wednesday, August 20, 2008 1:03 AM
  •  

    Thank you for replying, Yuhong. The contents of the file are too big to post here, but I saved it out to my web server. You can download it at...

     

    http://www.racingcow.com/Detail.txt

    Wednesday, August 20, 2008 2:10 AM
  • Thank you for the detail log.  I got it.

     

    The log indicated all three accounts failed.

     

    SQLGroup SID:  S-1-5-21-1421276065-2299552668-3877806105-1012

    AGTGroup SID:  S-1-5-21-1421276065-2299552668-3877806105-1011

    ASGroup SID:    S-1-5-21-1421276065-2299552668-3877806105-1014

     

    Those account SIDs are SQL service group account that was created during SQL Server 2005 installation.  The error message indicates that SQL is not able to map those SIDs to the names of those service group accounts.  That's strange.  I don't think you would delete those group accounts.  If it is deleted, SQL Server 2005 won't work.

     

    Can you go to Compunter Manager tool "Local Users and Groups" and check if those service group accounts are present?

     

    To locate the service group account, please look for the group name started with "SQLServer2005", e.g, "SQLServer2005MSSQLUser$YourMachineName$MSSQLSERVER".

     

    Thanks.

     

    Yuhong

    Wednesday, August 20, 2008 6:26 PM
  • Yuhong,

     

    I too am having the same error when trying to upgrade but my SID numbers are different.

     

    SQLGroup SID: S-1-5-21-256380386-1554834559-2676489931-1012
    AGTGroup SID: S-1-5-21-256380386-1554834559-2676489931-1011

    ASGroup SID: S-1-5-21-256380386-1554834559-2676489931-1014

     

    I have verified the accounts do exist in both SQL Server and Computer Manager. The instance of SQL Server in question is working and is functioning as a watcher server for two other mirrored SQL Servers.

     

    The server itself was upgraded to Windows 2008 earlier in the week.

     

    Thanks for any help,

     

    James

    Thursday, August 21, 2008 9:51 PM
  •  

    Another look at the log shows FTSGroup SID: S-1-5-21-256380386-1554834559-2676489931-1013 is failing as well.

     

    James

    Thursday, August 21, 2008 10:23 PM
  • Yuhong,

    Sorry it took me so long to get back to you on this. Similar to James, I also upgraded Windows on this box from Windows Server 2003 to Windows Server 2008 a few weeks ago. I think I forgot to mention that in my original post.

    When I look in the "Groups" folder under "Local Users and Groups" in Computer Manager, I only see the following groups that match the naming convention that you provided...

    SQLServer2005DTSUser$COWWEB
    SQLServer2005NotificationServicesUser$COWWEB
    SQLServer2005ReportingServicesWebServiceUser$COWWEB$MSSQLSERVER
    SQLServer2005ReportServerUser$COWWEB$MSSQLSERVER

    Those are the only groups I have with names that begin with "SQLServer2005". I don't see any groups with the names you provided. I also don't see any accounts with the SIDs that you mentioned above.

    -David Miller
    Friday, August 22, 2008 5:21 AM
  • The following groups are showing in Computer Manager;

     

    SQLServer2005DTSUser$ROSVR18

    SQLServer2005MSFTEUser$ROSVR18$MSSQLSERVER

    SQLServer2005MSOLAPUser$ROSVR18$MSSQLSERVER

    SQLServer2005MSSQLServerADHelperUser$ROSVR18

    SQLServer2005MSSQLUser$ROSVR18$MSSQLSERVER

    SQLServer2005NotificationServicesUser$ROSVR18

    SQLServer2005ReportingServicesWebServiceUser$ROSVR18$MSSQLSERVER

    SQLServer2005ReportServerUser$ROSVR18$MSSQLSERVER

    SQLServer2005SQLAgentUser$ROSVR18$MSSQLSERVER

    SQLServer2005SQLBrowserUser$ROSVR18

     

    And in SQL I see;

     

    ROSVR18\SQLServer2005MSFTEUser$ROSVR18$MSSQLSERVER

    ROSVR18\SQLServer2005MSSQLUser$ROSVR18$MSSQLSERVER

    ROSVR18\SQLServer2005SQLAgentUser$ROSVR18$MSSQLSERVER

     

    I've looked at our SQL Servers that haven't been upgraded to Win2k8 yet and they are showing the same groups in both Computer Manager and SQL. I'm going to try upgrading one of these servers to see if this has something to do with upgrading to Win2k8.

     

    James

     

     

     

    Friday, August 22, 2008 3:29 PM
  •  

    I just tried upgrading one of our SQL Servers running on Win2k3 and it failed with the same error. I guess we can assume this has nothing to do with upgrading to Win2k8.

     

    James

     

    Friday, August 22, 2008 5:25 PM
  •  

    Thank you all for the additional information.   Since all group account names are still present, this is good news for recovery.

     

    Let’s first confirm if SID is really changed or not.   If the SID is changed, we can use the following steps to let installation pass.

     

    -  Map those group account names to the current SIDs.

    -  Go to SQL Server registry key to replace the obsolete SID with the current one.  Those registry keys can be found like following example.

     

       [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup]

       "AGTGroup"="S-1-5-21-1606980848-1390067357-1417001333-1173"

       "SQLGroup"="S-1-5-21-1606980848-1390067357-1417001333-1174"

       "FTSGroup"="S-1-5-21-1606980848-1390067357-1417001333-1175"

     

    To map an account name to a SID, I am not aware what tool is available for public.  One way you can try is to use W2K8 OS command “calcs.exe” in the following steps, though it is not straightforward.

     

    1. Create a new folder saying C:\temp

     

    2. Run calcs.exe on C:\temp to take a snapshot of its ACL.

            C:\>  cacls.exe C:\Temp /S

         This will print ACL in SDDL format that shows account names in SID format except well-know account.   SDDL looks like following strings.

     

     …(A;OICIID;FA;;;BA)(A;ID;FA;;;S-1-5-21-2127521184-1604012920-1887927527-2735400)…

     

    3. Grant read-right or whatever right of C:\Temp to the service group account name, saying the account name SQLServer2005MSSQLUser$ROSVR18$MSSQLSERVER”.  You can do so like below.

           Open File Explorer -> Right-click on C:\Temp -> Select Properties -> Go to Security page -> Select Edit -> Select Add -> Add the group account. 

          When you add the account name, please add it in the format <ComputerName>\<AccountName>.

     

    4.  Repeat cacls.exe command to get the second snapshot.

     

    5.  Compare two results.  The newly added SID is the service group name.

     

    If SID you got is same as recorded in SQL registry key, that will be the different issue, and we need more investigation.

     

    Thanks.

     

    Yuhong

    Friday, August 22, 2008 10:50 PM
  •  

    Yuhong,

    The SIDs are indeed different;

    SQLGroup SID: S-1-5-21-2313972561-2086481262-3275809042-1012
    FTSGroup SID: S-1-5-21-2313972561-2086481262-3275809042-1011
    AGTGroup SID: S-1-5-21-2313972561-2086481262-3275809042-1014
    ASGroup SID: S-1-5-21-2313972561-2086481262-3275809042-1013

    James

    Monday, August 25, 2008 3:16 PM
  • That's good to know. If you replace SID as described in the previous post as follows, does setup pass the rule check?

     

    - Go to SQL Server registry key to replace the obsolete SID with the current one.  Those registry keys can be found like following example.

     

       [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup]

       "AGTGroup"="S-1-5-21-1606980848-1390067357-1417001333-1173"

       "SQLGroup"="S-1-5-21-1606980848-1390067357-1417001333-1174"

       "FTSGroup"="S-1-5-21-1606980848-1390067357-1417001333-1175"

     

    Thanks.

     

    Yuhong

    Tuesday, August 26, 2008 6:55 PM
  •  

    Oops, yes, I forgot to mention that in my last post.  Changing the SIDs in the registry fixed the problem and the upgrade was successful.

    Just so you have some more information on this problem. The first server, as I mentioned before, is a watcher server for two mirrored database servers. The SIDs on the watcher server DID NOT match, the SIDs on the redundant mirrored server DID NOT match, but the SIDs on the primary mirrored server DID match. The primary server was upgraded from SQL 2000 to SQL 2005 soon after 2005 was released. The redundant and watcher were/are fresh installs of SQL 2005.

    All three servers are running in virtual machines on separate physical boxes. They are all at the same component/patch level.

    Thanks for the help and I hope this extra info helps,

     

    James

    Tuesday, August 26, 2008 7:21 PM
  • Yuhong,

    I am still having trouble getting the install to pass the SID check. I went into the registry and updated the keys to the values that you provided (they were indeed different), but when I run the upgrade, it still fails. I posted the latest setup log at http://www.racingcow.com/detail2.txt.

    I went back and ran calcs.exe as you had described in your earlier post. In step 3 of the post...

     Yuhong Li - MSFT wrote:

    3. Grant read-right or whatever right of C:\Temp to the service group account name, saying the account name SQLServer2005MSSQLUser$ROSVR18$MSSQLSERVER”.  You can do so like below.

           Open File Explorer -> Right-click on C:\Temp -> Select Properties -> Go to Security page -> Select Edit -> Select Add -> Add the group account. 

          When you add the account name, please add it in the format <ComputerName>\<AccountName>.



    ... but Windows doesn't find the accounts that James listed...

     James Thornton ITS IV wrote:

    The following groups are showing in Computer Manager;

     

    SQLServer2005DTSUser$ROSVR18

    SQLServer2005MSFTEUser$ROSVR18$MSSQLSERVER

    SQLServer2005MSOLAPUser$ROSVR18$MSSQLSERVER

    SQLServer2005MSSQLServerADHelperUser$ROSVR18

    SQLServer2005MSSQLUser$ROSVR18$MSSQLSERVER

    SQLServer2005NotificationServicesUser$ROSVR18

    SQLServer2005ReportingServicesWebServiceUser$ROSVR18$MSSQLSERVER

    SQLServer2005ReportServerUser$ROSVR18$MSSQLSERVER

    SQLServer2005SQLAgentUser$ROSVR18$MSSQLSERVER

    SQLServer2005SQLBrowserUser$ROSVR18

     

    And in SQL I see;

     

    ROSVR18\SQLServer2005MSFTEUser$ROSVR18$MSSQLSERVER

    ROSVR18\SQLServer2005MSSQLUser$ROSVR18$MSSQLSERVER

    ROSVR18\SQLServer2005SQLAgentUser$ROSVR18$MSSQLSERVER



    ...the only group accounts that I see on my machine are...

     racingcow wrote:


    SQLServer2005DTSUser$COWWEB
    SQLServer2005NotificationServicesUser$COWWEB
    SQLServer2005ReportingServicesWebServiceUser$COWWEB$MSSQLSERVER
    SQLServer2005ReportServerUser$COWWEB$MSSQLSERVER



    Is it possible that the registry keys exist for the group accounts, but the accounts themselves have somehow been deleted on my machine?

    Is there anything else that I can try?

    -David
    Friday, August 29, 2008 2:08 AM
  • Also, when I go to the "Security" node of SQL 2005 Management Studio, the only Logins I see that match the naming convention are...

    COWWEB\SQLServer2005MSFTEUser$COWWEB$MSSQLSERVER
    COWWEB\SQLServer2005MSSQLUser$COWWEB$MSSQLSERVER
    COWWEB\SQLServer2005SQLAgentUser$COWWEB$MSSQLSERVER
    Friday, August 29, 2008 2:25 AM
  • Hi David,


    Every machine has the different group account names of SQL services, because the group account names of SQL services are created with association of machine names.

     

    You can't use the account names from James' case.  You need to use the account names that are found in your machine.  As you mentioned in the previous message, I guess those account names are as follows.


    COWWEB\SQLServer2005MSFTEUser$COWWEB$MSSQLSERVER
    COWWEB\SQLServer2005MSSQLUser$COWWEB$MSSQLSERVER
    COWWEB\SQLServer2005SQLAgentUser$COWWEB$MSSQLSERVER
    .....


    If you use those account names in the process of troubleshooting, does that get rule check passed?

    Thanks.

     

    Yuhong

     

    Tuesday, September 02, 2008 6:14 PM
  • Your SIDs will be different as well I believe.

     

    James

    Tuesday, September 02, 2008 7:28 PM
  •  

    If you are having this same issue, this is a work around.

     

    Check to see if your version of SQL Server is upgradeable to 2008.

     

    http://msdn.microsoft.com/en-us/library/ms143393.aspx

     

    Fire up your Enterprise Manager / SQL Management Studio and run the following query against your master catalog:

     

    SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

     

    If you are running developer editions, that explains this issue.

     

    B

    Thursday, September 04, 2008 4:32 PM
  • There is another solution to find out the SID for a group.

     

    - Add your self into all groups that you are interested on

    - open a new Command Windows (open it as run as and logon again with your credentials)

    - whoami /groups /SID

     

    This will show you all your group memberships and the SID of each group.

     

     

    But my problem is still the same:

    I found the Registry keys but can somebody let me know which Key is which groupname?

     

     

    Thursday, December 11, 2008 4:09 PM
  • Hi,

       We are having the problem upgrading to SQL 2008 from SQL 2005 with the SID checked failed. After reading all posts instructions and found out the machine doesn't has those SQLServer2005DTSUser$... etc accounts exist.

        Can someone tell me how can I regernate these accounts so the upgrade will pass the SID test?

        The machine is Domain Controller, all those SQL 2005 accounts are missing on the AD User Groups.


    Thanks so much,
    Chris
    Tuesday, January 13, 2009 6:21 PM
  • I know this is an older post but I just had the same problem and fixed it using some of the information on here and another tool I downloaded.  I started by checking the SQL Groups and the local user groups.  On my machine, there were two that matched.  Here are two examples (one had FTEUser in the middle of the name and one had SQLUser).  

    SQLServer2005MSFTEUser$ServerName$MSSQLSERVER

    SQLServer2005MSSQLUser$ServerName$MSSQLSERVER

    I then downloaded the free PSTools utility that will show the SID for any user group.  

    Here's the link:  http://technet.microsoft.com/en-us/sysinternals/bb897417.aspx

    I unzipped the PSTools into a directory on the C drive and used the command line to run the command psgetsid <Enter the groupname>

    Run the psgetsid command for each group and note the SIDs

    I then went to  [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup] to see the SIDs for each group (these are sample SIDs)

      "SQLGroup"="S-1-5-21-1606980848-1390067357-1417001333-1174"

       "FTSGroup"="S-1-5-21-1606980848-1390067357-1417001333-1175"

    I replaced the registry SIDs with the ones found using the PSTools software, clicked Re-Run on the SQL 2008 Upgrade Rules page and the security group check passed!

    I recommend copying and pasting the old SIDs in a notepad document just in case.  

    Steve

    • Proposed as answer by nctmyers Friday, July 23, 2010 6:52 PM
    Friday, July 23, 2010 6:50 PM
  • Hello,

    I am trying to upgrade from sql express 2005 to 2008 and I am having the same error as this post.

    I read all the replies a lot of times and I am still not sure that I understand what I need to do.

    My problem is that on MSSQL.3 there is a value for the sql group that in the log i can see that it cannot find it.

    so probably this is the problem, but now I should go and look for a group, which group? which user?

    i looked in my Active directory for a user start with sql, i find a few but why do i need to assign his SID to this?

    I really do not understand what is the difference between mssql.1/mssql.2/mssql.3.

    please help me as I really have to upgrade it and I am not sure on what is the right solution for the problem.

     

    Thank you very much....

    Friday, January 14, 2011 9:00 PM
  • Hello,

    I was able to fix the problem.

    for everybody who will needs it what I did is:

    I went into the sql managment and choose the secutiry than I saw which SQL user was in the security, than I did exactly like in the post before using the psexec in order to understand what is the sid of this user (i find this user in the Active directory).

    after this I changed the registry of this database to this sid and it worked!

    Thank you all

    Saturday, January 15, 2011 2:17 PM
  • Thank you Yuhong Li, i was having the same problem when upgrading my situation was that i renamed the server after installing sql server 2005. When i tried to upgrade to sql server 2008 then i was getting the same error. I went through the steps you provided and that fixed my problem.
    Saturday, February 12, 2011 11:53 AM
  • Had the same issue today myself.

    Upgrading from SQL 2005 Express to SQL 2008 R2 Express.

     

    The accounts, once used to install 2005, did no longer exist.  It seems  at the time of sql 2005 installation, the server was a member server, and was later promoted to a domain controller, so local users and groups disappeared !

    To solve the isse, I manually created 3 local groups in AD, one for each SQL instance running on the server,  and then obtained the SID of each group, and then changed the registry key for each instance with the SID of the newly created groups.

    Installation pre-check of SQL 2008 did not report any problems afterwards.   

    (see messages above how to obtain the SID of a group, and which key(s) to change)

    Tuesday, February 22, 2011 4:40 PM
  • This solution working for me. Thank you a lot.
    Monday, May 09, 2011 1:33 PM