System Center Configuration Manager TechCenter >
System Center Configuration Manager Forums
>
Configuration Manager Setup/Deployment
>
Problem Upgrading SCCM SP1 R2 to SCCM SP2: Fatal error. Setup cannot upgrade ConfigMgr database
Problem Upgrading SCCM SP1 R2 to SCCM SP2: Fatal error. Setup cannot upgrade ConfigMgr database
- Hello! I have a serious problem. Im trying to upgrade my SCCM from version SP1 R2 to SCCM SP2 version but the setup fails on fatal error in the "Create and initialize site database" phase.
Here is a part of the ConfigMgrSetup.log:
<10-27-2009 12:20:43> Adding Permission: GRANT EXECUTE ON CH_UpdateInactiveClients TO [smsdbrole_CH]
<10-27-2009 12:20:43> Adding smsdbrole_siteprovider to db_owner: EXEC sp_addrolemember 'db_owner', 'smsdbrole_siteprovider'
<10-27-2009 12:20:43> Adding smsdbrole_siteserver to db_owner: EXEC sp_addrolemember 'db_owner', 'smsdbrole_siteserver'
<10-27-2009 12:20:44> Adding a login for SMS Site Server: IF NOT EXISTS (select * from master.sys.server_principals where name='NT AUTHORITY\SYSTEM') CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS
<10-27-2009 12:20:44> Creating user for SMS Site Server to master db: IF NOT EXISTS (select * from sys.database_principals where name='NT AUTHORITY\SYSTEM') CREATE USER [NT AUTHORITY\SYSTEM]
<10-27-2009 12:20:44> Granting an execute permissions on sys.xp_msver: GRANT EXECUTE on sys.xp_msver to [NT AUTHORITY\SYSTEM]
<10-27-2009 12:20:44> Granting VIEW SERVER STATE to site server: GRANT VIEW SERVER STATE to [NT AUTHORITY\SYSTEM]
<10-27-2009 12:20:44> Adding site server machine account to db_datareader role: sp_addrolemember 'db_datareader', 'NT AUTHORITY\SYSTEM'
<10-27-2009 12:20:44> Adding site server to securityadmin: sp_addsrvrolemember 'NT AUTHORITY\SYSTEM', 'securityadmin'
<10-27-2009 12:20:44> Creating user for SMS Site Server: IF NOT EXISTS (select * from sys.database_principals where name='NT AUTHORITY\SYSTEM') CREATE USER [NT AUTHORITY\SYSTEM]
<10-27-2009 12:20:44> Adding site server to smsdbrole_siteserver: EXEC sp_addrolemember 'smsdbrole_siteserver', 'NT AUTHORITY\SYSTEM'
<10-27-2009 12:20:44> Adding a login for SMS Provider: IF NOT EXISTS (select * from master.sys.server_principals where name='NT AUTHORITY\SYSTEM') CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS
<10-27-2009 12:20:44> Creating user for SMS Provider: IF NOT EXISTS (select * from sys.database_principals where name='NT AUTHORITY\SYSTEM') CREATE USER [NT AUTHORITY\SYSTEM]
<10-27-2009 12:20:44> Adding SMS Provider machine account to smsdbrole_siteprovider: EXEC sp_addrolemember 'smsdbrole_siteprovider', 'NT AUTHORITY\SYSTEM'
<10-27-2009 12:20:44> Creating logins and assigning roles for site systems.
<10-27-2009 12:20:44> Assigning server DISTPOINT to role SMS PXE Service Point
<10-27-2009 12:20:44> Assigning server DISTPOINT to role SMS PXE Service Point
<10-27-2009 12:20:44> Assigning server SERVER1 to role SMS PXE Service Point
<10-27-2009 12:20:44> Assigning server DISTPOINT to role SMS PXE Service Point
<10-27-2009 12:20:45> Assigning server DISTPOINT to role SMS PXE Service Point
<10-27-2009 12:20:45> Assigning server DISTPOINT to role SMS PXE Service Point
<10-27-2009 12:20:45> Assigning server DISTPOINT to role SMS PXE Service Point
<10-27-2009 12:20:45> Assigning server PRIMARYSITE to role SMS Device Management Point
<10-27-2009 12:20:45> Assigning server PRIMARYSITE to role SMS Management Point
<10-27-2009 12:20:45> Assigning server PRIMARYSITE to role SMS PXE Service Point
<10-27-2009 12:20:45> Assigning server PRIMARYSITE to role SMS Reporting Point
<10-27-2009 12:20:45> Assigning server PRIMARYSITE to role SMS Server Locator Point
<10-27-2009 12:20:45> ***SqlError: [42000][15063][Microsoft][ODBC SQL Server Driver][SQL Server]The login already has an account under a different user name.
<10-27-2009 12:20:45> ***SqlError: [42000][15410][Microsoft][ODBC SQL Server Driver][SQL Server]User or role 'DOMAIN\SERVER1$' does not exist in this database. : sp_addrolemember
<10-27-2009 12:20:45> SqlExecute < IF NOT EXISTS(SELECT * FROM sys.database_principals dp JOIN sys.server_principals sp ON dp.sid=sp.sid AND dp.name='dbo' AND sp.name='DOMAIN\DISTPOINT$') BEGIN IF NOT EXISTS(SELECT * FROM master.sys.server_principals where name='DOMAIN\DISTPOINT$') CREATE LOGIN [DOMAIN\DISTPOINT$] FROM WINDOWS IF NOT EXISTS(SELECT * FROM sys.database_principals where name='DOMAIN\DISTPOINT$') CREATE USER [DOMAIN\DISTPOINT$] EXEC sp_addrolemember 'smsdbrole_PSP', 'DOMAIN\DISTPOINT$' END ;
IF NOT EXISTS(SELECT * FROM sys.database_principals dp JOIN sys.server_principals sp ON dp.sid=sp.sid AND dp.name='dbo' AND sp.name='DOMAIN\DISTPOINT$') BEGIN IF NOT EXISTS(SELECT * FROM master.sys.server_principals where name='DOMAIN\DISTPOINT$') CREATE LOGIN [DOMAIN\DISTPOINT$] FROM WINDOWS IF NOT EXISTS(SELECT * FROM sys.database_principals where name='DOMAIN\DISTPOINT$') CREATE USER [DOMAIN\DISTPOINT$] EXEC sp_addrolemember 'smsdbrole_PSP', 'DOMAIN\DISTPOINT$' END ;
IF NOT EXISTS(SELECT * FROM sys.database_principals dp JOIN sys.server_principals sp ON dp.sid=sp.sid AND dp.name='dbo' AND sp.name='DOMAIN\SERVER1$') BEGIN IF NOT EXISTS(SELECT * FROM master.sys.server_principals where name='DOMAIN\SERVER1$') CREATE LOGIN [DOMAIN\SERVER1$] FROM WINDOWS IF NOT EXISTS(SELECT * FROM sys.database_principals where name='DOMAIN\SERVER1$') CREATE USER [DOMAIN\SERVER1$] EXEC sp_addrolemember 'smsdbrole_PSP', 'DOMAIN\SERVER1$' END ;
IF NOT EXISTS(SELECT * FROM sys.database_principals dp JOIN sys.server_principals sp ON dp.sid=sp.sid AND dp.name='dbo' AND sp.name='DOMAIN\DISTPOINT$') BEGIN IF NOT EXISTS(SELECT * FROM master.sys.server_principals where name='DOMAIN\DISTPOINT$') CREATE LOGIN [DOMAIN\DISTPOINT$] FROM WINDOWS IF NOT EXISTS(SELECT * FROM sys.database_principals where name='DOMAIN\DISTPOINT$') CREATE USER [DOMAIN\DISTPOINT$] EXEC sp_addrolemember 'smsdbrole_PSP', 'DOMAIN\DISTPOINT$' END ;
IF NOT EXISTS(SELECT * FROM sys.database_principals dp JOIN sys.server_principals sp ON dp.sid=sp.sid AND dp.name='dbo' AND sp.name='DOMAIN\DISTPOINT$') BEGIN IF NOT EXISTS(SELECT * FROM master.sys.server_principals where name='DOMAIN\DISTPOINT$') CREATE LOGIN [DOMAIN\DISTPOINT$] FROM WINDOWS IF NOT EXISTS(SELECT * FROM sys.database_principals where name='DOMAIN\DISTPOINT$') CREATE USER [DOMAIN\DISTPOINT$] EXEC sp_addrolemember 'smsdbrole_PSP', 'DOMAIN\DISTPOINT$' END ;
IF NOT EXISTS(SELECT * FROM sys.database_principals dp JOIN sys.server_principals sp ON dp.sid=sp.sid AND dp.name='dbo' AND sp.name='DOMAIN\DISTPOINT$') BEGIN IF NOT EXISTS(SELECT * FROM master.sys.server_principals where name='DOMAIN\DISTPOINT$') CREATE LOGIN [DOMAIN\DISTPOINT$] FROM WINDOWS IF NOT EXISTS(SELECT * FROM sys.database_principals where name='DOMAIN\DISTPOINT$') CREATE USER [DOMAIN\DISTPOINT$] EXEC sp_addrolemember 'smsdbrole_PSP', 'DOMAIN\DISTPOINT$' END ;
IF NOT EXISTS(SELECT * FROM sys.database_principals dp JOIN sys.server_principals sp ON dp.sid=sp.sid AND dp.name='dbo' AND sp.name='DOMAIN\DISTPOINT$') BEGIN IF NOT EXISTS(SELECT * FROM master.sys.server_principals where name='DOMAIN\DISTPOINT$') CREATE LOGIN [DOMAIN\DISTPOINT$] FROM WINDOWS IF NOT EXISTS(SELECT * FROM sys.database_principals where name='DOMAIN\DISTPOINT$') CREATE USER [DOMAIN\DISTPOINT$] EXEC sp_addrolemember 'smsdbrole_PSP', 'DOMAIN\DISTPOINT$' END ;
IF NOT EXISTS(SELECT * FROM sys.database_principals dp JOIN sys.server_principals sp ON dp.sid=sp.sid AND dp.name='dbo' AND sp.name='NT AUTHORITY\SYSTEM') BEGIN IF NOT EXISTS(SELECT * FROM master.sys.server_principals where name='NT AUTHORITY\SYSTEM') CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS IF NOT EXISTS(SELECT * FROM sys.database_principals where name='NT AUTHORITY\SYSTEM') CREATE USER [NT AUTHORITY\SYSTEM] EXEC sp_addrolemember 'smsdbrole_DMP', 'NT AUTHORITY\SYSTEM' END ;
IF NOT EXISTS(SELECT * FROM sys.database_principals dp JOIN sys.server_principals sp ON dp.sid=sp.sid AND dp.name='dbo' AND sp.name='NT AUTHORITY\SYSTEM') BEGIN IF NOT EXISTS(SELECT * FROM master.sys.server_principals where name='NT AUTHORITY\SYSTEM') CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS IF NOT EXISTS(SELECT * FROM sys.database_principals where name='NT AUTHORITY\SYSTEM') CREATE USER [NT AUTHORITY\SYSTEM] EXEC sp_addrolemember 'smsdbrole_MP', 'NT AUTHORITY\SYSTEM' END ;
IF NOT EXISTS(SELECT * FROM sys.database_principals dp JOIN sys.server_principals sp ON dp.sid=sp.sid AND dp.name='dbo' AND sp.name='NT AUTHORITY\SYSTEM') BEGIN IF NOT EXISTS(SELECT * FROM master.sys.server_principals where name='NT AUTHORITY\SYSTEM') CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS IF NOT EXISTS(SELECT * FROM sys.database_principals where name='NT AUTHORITY\SYSTEM') CREATE USER [NT AUTHORITY\SYSTEM] EXEC sp_addrolemember 'smsdbrole_PSP', 'NT AUTHORITY\SYSTEM' END ;
IF NOT EXISTS(SELECT * FROM sys.database_principals dp JOIN sys.server_principals sp ON dp.sid=sp.sid AND dp.name='dbo' AND sp.name='NT AUTHORITY\SYSTEM') BEGIN IF NOT EXISTS(SELECT * FROM master.sys.server_principals where name='NT AUTHORITY\SYSTEM') CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS IF NOT EXISTS(SELECT * FROM sys.database_principals where name='NT AUTHORITY\SYSTEM') CREATE USER [NT AUTHORITY\SYSTEM] EXEC sp_addrolemember 'smsdbrole_RP', 'NT AUTHORITY\SYSTEM' END ;
IF NOT EXISTS(SELECT * FROM sys.database_principals dp JOIN sys.server_principals sp ON dp.sid=sp.sid AND dp.name='dbo' AND sp.name='NT AUTHORITY\SYSTEM') BEGIN IF NOT EXISTS(SELECT * FROM master.sys.server_principals where name='NT AUTHORITY\SYSTEM') CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS IF NOT EXISTS(SELECT * FROM sys.database_principals where name='NT AUTHORITY\SYSTEM') CREATE USER [NT AUTHORITY\SYSTEM] EXEC sp_addrolemember 'smsdbrole_SLP', 'NT AUTHORITY\SYSTEM' END ;
>
<10-27-2009 12:20:45> Could not create logins and assign roles.
<10-27-2009 12:20:45> Setup cannot upgrade the ConfigMgr database. Contact your SQL administrator.
As you can see the setup cannot create login for distibution point DOMAIN\server1$.
Distribution point server server1 was actually before distribution point server2. We changed the the physical location of server2 so we wanted to rename it. So I changed server2 name to server1 joined it to domain and added it as distribution point to SCCM. But of course I didn't realize to change it's SID.
There was actually a login for DOMAIN\server2$ in the SCCM database. I deleted it and tryed to add the DOMAIN\server1$ login manually, but i receive this error:
TITLE: Microsoft SQL Server Management Studio
------------------------------Create failed for Login 'DOMAIN\SERVER1$'. (Microsoft.SqlServer.Smo)
------------------------------
ADDITIONAL INFORMATION:An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The server principal 'DOMAIN\SERVER1$' already exists. (Microsoft SQL Server, Error: 15025)
So what can I do? Can I somehow safely clean the SCCM database of all information about server2 or could it help if I change the SID of server1 at this point?
Sorry about my poor english. I hope you understand my problem anyway.
All Replies
- Can anybody help me with this? This is a serious problem for us.
- Ok. I know now that I should not mess with the database directly, but know I have another problem.
I want to restore my SCCM to the state that I had before SP2 upgrade. I SCCM backup just before I ran SP2 setup but the problem is that the Repair Wizard does not exist anymore.
So is there someway I can restore my backup? Can I uninstall my SCCM and install it again with the same sitecode and the run the wizard and restore my SCCM? Or is there someway I dont have to uninstall my SCCM?
Please help! - Is your server1 is only DP ?
If so you need not to add this server account to SQL logins, just remove this server name from SQL Logins.
Surendar Reddy - Since you have SCCM Backup before upgrading to SP2, so its better to uninstall SCCM and install again with same site code, installation path, site name. then restore from backup.
Surendar Reddy - Hi
Is there a test that can verify the ability to upgrade the database (offline) before the actual upgrade?
Ronny - Hi guys,
I have just fixed this issue in our environment a few minutes ago. I had exactly the same issue. The problem relates to the computer record as you described. The line:
<10-27-2009 12:20:45> ***SqlError: [42000][15410][Microsoft][ODBC SQL Server Driver][SQL Server]User or role 'DOMAIN\SERVER1$' does not exist in this database. : sp_addrolemember
is the give away.
I went in to the SQL Management Studio, opend the SCCM database and removed the 'DOMAIN\SERVER1$' record. In my case the problem was becasue a Branch DP server had been renamed but the DB login still hadt he old server name. Removing the OLD server login record from the databse and the main SQL security database and readding it using the NEW name resolved the problem.
When you re-add the server name make sure that you give the login the correct database rights, e.g. if it is a PXE Service Point it will need that database right again. It is best to check the user rights for the computer and note the rights down before you remove and re-add it.
After this I re-ran the SETUP.EXe for SP2 and it went through perfectly.
Hope this helps.
Nathan
NSutton- Proposed As Answer byNathan sutton Sunday, November 01, 2009 11:25 PM
- Can you display the query that you used to remove the 'domain\server1$' record from the database?
- Hi Ronny11,
You can restore SQL DB on any other SQL server and perform TestDBUpgrade before you go with actual upgrade on production server.
use Setup.exe /testdbupgrade <DB Name>
Surendar Reddy - Hi,
I didn't use a SQL query. I fired up the SQL Management Sutio and remove the record manually from the DB and the Master Security database via the interface. However there willl be plenty of examples on the web on how this is to be done. In my case I only had two logins that needed deleting, notworth writing a script for this.
Hope that helps,
Nathan
NSutton- Proposed As Answer byReddy Surendar Tuesday, November 03, 2009 3:30 PM
- Yep! You were right Nathan. Deleting the logins solved the problem. Thank you!
