SCCM and lost administrative rights
SCCM and lost administrative rights
Scenario
I like scenarios as they neatly represent the difference between the real world and the ordered world portrayed in textbooks. For this scenario, let us assume that it has been decided to activate automatic client upgrades in your SCCM infrastructure but, lo and behold, the option to do so is greyed out and cannot be selected. Yet, it is possible to add new distribution points and assign SCCM administrative rights so what has gone wrong?
In this case, SCCM had been deployed by a user who had left the organisation. When the user left, his user account – which, it turned out, had admin rights in the domain – had, as you might expect, been deleted. Many months had passed so the account could not be restored from the recycle bin or a backup and a new SCCM administrator had now been hired. He soon discovered that he could not do some of the things that an SCCM administrator should be able to do.
As is so often the case, it was a bit of SQL surgery that saved the SCCM day. Using the SQL management studio and connecting to the SCCM SQL database (this will be called CM_ and your three digit site code e.g. CM_ABC), it was also noted that some of the application roles seemed to be missing but an attempt to recreate them manually failed with an error indicating that the missing roles already existed. Just in case you ever need to recreate the SCCM SQL roles yourself, a list of them is shown in the following illustration. Thsi procedure was tested on SCCM2012R2 SP1 but should work with other versions as the SQL table structure is the same.
Note that you will need sysadmin rights on your SQL server to sort this out. As, in this kind of scenario, the sysadmin account would likely be the same AD account that had been deleted then you might find this post on getting your sysadmin rights back useful.
Restoring SQL Administrative Rights
The Solution
First, create a new service account for use – not a user account so it won’t get deleted in future - with SCCM and assign it sysadmin rights on the SCCM SQL server. Now, get the SID (security identifier for this new account) which you can get from the properties of the account in Active Directory Users and Computers as shown in the following illustration.
Select the objectSID attribute and then the View button. You want to copy the hexadecimal value and remove all the spaces so that you end up with a string that looks like the following:
01050000000000051555555530351D0A9A5D3077777FAB322EC96E01
You will use this value to replace the value in one of the tables inside the SCCM SQL database, specifically the RBACAdmins table, so that your new SCCM service account effectively becomes the account used to install SCCM. Using the SQL Management Studio, the value that you will replace will be the AdminSID value for the first account in that table as that will be the account that was used to install SCCM in the first place. Conveniently, this will have the AdminID value set to 16777217. You can check the LogonName value in the same table prior to surgery to verify that it is the account that was deleted. If you right click on the RBACAdmins table and, from the popup menu, select the option to view the first 1000 rows then you can see all the entries in that table.
So, to the SQL that will sort it all out (if you don't feel particularly brave then do remember to backup your database first). The process does not need any downtime.
Step 1
In SQL Management studio – using an account that has sysadmin rights to the SQL installation even if it isn’t the account that will shortly get SCCM admin rights – type the following as a new query:
Use CM_xxx (where xxx is your site code e.g. CM_ABC)
update dbo.RBAC_Admins
set AdminSID=0x01050000000000051555555530351D0A9A5D3077777FAB322EC96E01
where AdminID=16777217
(all on one line and the AdminSID is 0x and the value that you retrieved from AD for the new SCCM service account. In this example it is 01050000000000051555555530351D0A9A5D3077777FAB322EC96E01)
Step 2
Then select the Execute button
That’s it. Now logon to the SCCM Configuration Manger console using the new SCCM service account and you will be able to do global SCCM configuration tasks like activating automatic client upgrades. SQL saves the day again…