"An error occurred while trying to connect to the data source" after upgrading/reinstalling vCenter Server with same database shows empty inventory

Products

• VMware vCenter Server

Issue/Introduction

Symptoms:

• After upgrading or reinstalling the vCenter Server with the same database the inventory is empty
• Upgrade does not indicate the database is upgraded
• Database upgrade wizard does not show up
• When launching database upgrade manually, you see the error:
  
  An error occurred while trying to connect to the data source. please see the log file for more details.
   
• VirtualCenter inventory is empty after logging in

Environment

VMware vCenter Server 5.0.x
VMware vCenter Server 4.1.x
VMware vCenter Server 5.5.x
VMware vCenter Server 6.0.x
VMware vCenter Server 4.0.x
VMware vCenter Server 5.1.x

Cause

This issue occurs when the vCenter Server installer cannot read the existing schema and creates the vCenter data objects in a different schema. After the install there is multiple schemas in the vCenter Server database (example dbo and vmw).

 

Run this command to verify if the vCenter Server database has multiple schema:

 

USE vc_database;

GO;

SELECT distinct SCHEMA_NAME(schema_id)

FROM sys.tables ;

GO;

 

Resolution

To resolve the issue:

For a reinstalled vCenter Server with same version

Verify which schema has the data:

1. Open the SQL Management Studio and connect to the vCenter Server database.
2. Run these queries against the vCenter Server database where schema1 and schema2 are the two schema returned in the Cause section.
   
   select count(1) from schema1.vpx_host
   select count(1) from schema2.vpx_host
   
   The schema with data reports the number of ESXi hosts in the vCenter Server inventory. Using this schema verify the user configured in the vCenter Servers ODBC connection has the default schema.
    
3. To check the default schema of the vCenter Server ODBC user navigate to Security > logins, right-click the user and select Properties > User Mapping.
    
4. Select the vCenter Server and verify the default schema is set to the correct default schema based on step 2.
   
   Note: If the schema is not DBO ensure that sysadmin is not selected for the user under Server Roles.
    
5. After the users default schema has been updated restart the vCenter Server.

For an upgraded vCenter Server from a previous release

1. Open the SQL Management Studio and connect to the vCenter Server database.
2. Verify which schema has the data. Run these queries against the vCenter Server database where schema1 and schema2 are the two schema returned in the Cause section.
   
   select count(1) from schema1.vpx_host
   select count(1) from schema2.vpx_host
   
    
3. Run this query to verify the version of the database:
   
   select * from schema1.vpx_version
   select * from schema2.vpx_version
    
4. To check the default schema of the vCenter Server ODBC user navigate to Security > logins, right-click the user and select Properties > User Mapping.
    
5. Select the vCenter Server and verify the default schema is set to the correct default schema based on step 2.
   
   Note: If the schema is not DBO ensure that sysadmin is not selected for the user under Server Roles.
    
6. After the user default schema has been updated the vCenter Server must be reinstalled to complete the database upgrade.