Answered by:

Upgrade from SQL2005 - Security Group SID Upgrade Rule Fails

Question

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.

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".

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.

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...

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.

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.

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.

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.

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.

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.

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 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.

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)