今天在论坛看到有用户出现Security Group upgrade rule失败 错误:rule "Security Group SID(Security Identifier)" failed。发生这个错误的原因是SQL Server创建的Windows本地组SID信息与注册表中的SID不匹配,需要用 WHOAMI /GROUPS查出正确的SID然后替换注册表中的错误SID,问题可以解决。
具体步骤参考:
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.
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 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:
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:
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.
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.
分享到:
相关推荐
用于解决SQLSERVER连接问题驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接问题JAR包。
1、如果是从SQL2008升级到SQL2008R2那么只能选择左侧“安装”对应右侧“从SQL SERVER 2000,SQL 2005或SQL 2008升级”这个选项来升级。 大家也注意到有一个“维护”但是这个选项是“从SQL 2008其他例如EXPRESS版本...
sqlserver 安装补丁程序 sp4 帮您的sqlserver 升级,只有您升级的sqlserver 才能帮助你使用jar包连数据库。
从SQL Server 2000升级至SQL Server 2008 R2实验手册
SQL Server升级:两款工具解决应用程序问题.
在本次的技术手册中,我们将讨论SQL Server数据库安全的话题,其中包括数据加密、SQL注入攻击、角色控制以及安全通信方面的内容,希望对SQL Server安全有个更全面了解的读者,赶紧下载阅读吧。 来自TT
北京 在使用SQLServer的过程中,用户遇到的最多的问题莫过于连接失败了。一般而言,有以下两种连接SQLServer的方式,一是利用SQLServer自带的客户端工具,如企业管理器、查询分析器、事务探查器等;二是利用用户...
以前SQL Server 2008 不能登陆的时候,总是通过“计算机管理”→“SQL Server服务”更改一下,”SQL Server(MSSQLSERVER)”。可是现在出现的问题却成了SQL Server服务打不开的情况,很是郁闷。如图: 通过网上查...
SQL Server 2005 移动版(SQL Server Mobile)或SQL Server 2000 Windows CE 2.0版(SQL Server CE 2.0)的企业和个人用户如果计划与SQL Server 2000或SQL Server 2005数据库保持同步,需要在您运行Microsoft ...
在使用 SQL Server 的过程中,用户遇到的最多的问题莫过于连接失败了。一般而言,有以下两种连接 SQL Server 的方式,一是利用 SQL Server 自带的客户端工具,如企业管理器、查询分析器、事务探查器等;二是利用用户...
处理SQLSERVER客户端连接无法通过使用安全套接字层(SSL)加密,同时能够使 SQL Server 建立安全连接问题JAR包。
SQL Server 安全加固
【SQLServer数据库教程】课程列表 ...SQLServer数据库教程 第9章 SQL Server 2008安全管理 (共43页) SQLServer数据库教程 第12章 C#.NETSQL Server开发与编程 (共9页) SQLServer数据库教程 数据库练习题(共4页)
将sql server 2000升级到sql server 2005视频教程
SQLServer+ 免安装版 SQLServer+是在原有SQLServer2000的基础上改善了数据库安装的繁锁性,让软件企业在发布基于SQLServer2000数据库软件的时候,只要把软件打包进入安装包而不需要再单独安装数据库,也不需要另外...
sqlserver的安全保护机制、白皮书
SQL Server 2016 SP2 补丁 SQLServer2016SP2-KB4052908-x64-CHS
SQL Server数据库健康检查 巡检报告