Manually Adding a User With the Administrator Role to the vCenter VPX_ACCESS Table
I did a vCenter upgrade recently and neglected to check what users and groups were configured as vCenter Administrators. The environment was configured with the local vCenter Server Administrators group assigned the Administrator role, a few other domain users with Virtual Machine user access, and a single Domain User with Administrator access.
When upgrading vCenter Server it displays a warning that it is going to remove some users that the VMware SSO Server is not aware of and writes a text file containing the deleted users and groups called deleted_vc_users.txt into the system’s temp directory. Any local server users or groups that have vCenter permissions are deleted.
During the upgrade the pop up warned me, but I was sure that the Domain Admins group had been configured with the Administrator role (it was not – Domain Admins had been added to the local Administrators group -ugh!). The upgrade finished successfully but it had deleted the local Administrators group from the vCenter permissions. Oh no!!!
Again I had been warned, but thought for sure the Domain Admins group had the permissions. If the upgrade is going to delete all the users with the Administrator role it allows you add a group during the upgrade, since there was a single Domain User that did have the role I did not receive this prompt.
I could have tracked down or changed the password for the user that still had Administrator access but since I had some time and there was no production impact I decided to do some poking around.
***DISCLAIMER*** ***WARNING*** ***DANGER***
Make sure you have a good backup of your vCenter database and know how to get yourself out of a jam before messing around in it. You have been warned!
I came across a forum thread about exporting the original VPX_ACCESS table from the vCenter Database and then importing it over the new VPX_ACCESS table, this gave me the idea to just try and manually add the Domain Admins group to the VPX_ACCESS table.
A little more digging and I found this VMware KB article that walks you through the process of changing a user from the Read Only role to the Administrator role by updating the VPX_ACCESS table. From the information in the KB I was able to add the Domain Admins group and regain access to the vCenter Server.
First stop the vCenter Server Service. Then use Microsoft SQL Server Management Studio to access the vCenter Database. Navigate to the Tables folder of the vCenter database and find the table dbo.VPX_ACCESS. Right click on the table and choose “Edit Top 200 Rows”
The table should display users configured with permissions in the environment.
Select the Null row to add a new entry in the table where the ID is a unique ID (just add a number that does not already exist), PRINCIPAL is the user or group (LAB\Domain Admins for this example), ROLE_ID is set to -1 for Administrator role (-2 is Read Only), ENTITY_ID is the object the user is granted permission to which is 1 for vCenter, and FLAG is 3 for a group (or 1 for a user).
I had found a post that explained the fields but I am not able to find the link again. Will update this post when/if I come across it again.
Once the row has been added to the table start the vCenter Server Service.
You should now be able to login and administer the vCenter environment with a user from the group that was added. The new permissions should also display.
Of course the better option would had been to verify the permissions and heed the warnings that popped up during the upgrade but it is good to know there is another option.
Note that for this to work the Identity Source for the domain must be configured correctly in VMware SSO Sign-On and Discovery. If the domain is not set up correctly you will not be able to authenticate any users in the domain.