Missing table vpx_dual when trying to start vCenter configured with MSSQL database

Products

VMware vCenter Server

Issue/Introduction

Symptoms:

VMware VirtualCenter Server service does not start.
You see the error:

missing table vpx_dual
You can see the table does exist in the database.
In the vCenter Server log files, you see errors similar to:

2016-03-02T17:43:06.605-08:00 [04716 info 'utilvpxdVdb'] Registry Item DB 5 value is '50'
2016-03-02T17:43:06.605-08:00 [04716 info 'utilvpxdVdb'] [VpxdVdb::SetDBType] Logging in to DSN: VCDB with username vpxuser
2016-03-02T17:43:06.745-08:00 [04716 error 'Default'] Vdb::Connection::TestConnection SQLExecute failed: 42S02:208
2016-03-02T17:43:06.745-08:00 [04716 error 'Default'] [Vdb::LockRepositoryHelper] Missing table vpx_dual
2016-03-02T17:43:06.746-08:00 [04716 error 'Default'] A database error occurred: Repository has not been created
2016-03-02T17:43:06.747-08:00 [04716 error 'vpxdvpxdMain'] [Vpxd::ServerApp::Init] Init failed: VpxdVdb::Init(VpxdVdb::GetVcVdbInstId(), false, false, NULL)
--> Backtrace:
--> backtrace[00] rip 000000018018b7fa
--> backtrace[01] rip 0000000180104c78

Environment

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

Cause

This issue may occur if the vCenter Server Service uses an account to connect to the vCenter database which has a default schema that differs from the schema that the vCenter Server uses.

For example, if using the MSSQL server sa account, which has a default schema of dbo, while the vCenter Server tables are using the vmw schema you will encounter this issue.

Resolution

Ensure that the user account which the vCenter Server Service uses to connect to the vCenter database has a default schema set to the same schema which the vCenter objects uses.

Note: To check which user the vCenter Server is using to connect to the MSSQL DB open the vCenter Server Data Sources (ODBC) and view the System DSN tab. Click Configure and then Next to see what the Login ID is.
- If the DSN is using Windows Authentication the service account that starts the vCenter Server Service will be the account that connects to the database.
- If using SQL Authentication the Login ID account is the user account that connects to the database.
To verify which schema the vCenter Server database uses:
1. Connect to the MSSQL instance using MSSQL Management Studio as the user defined in the System DSN.
2. Expand Databases.
3. Expand the vCenter Server Database, for example VIM_VCDB.
4. Expand Tables.
5. The format will be [schema].vpx_table.
To verify the default schema of the user:
1. Connect to the MSSQL instance using MSSQL Management Studio as the user defined in the System DSN.
2. Expand Security.
3. Expand Logins.
4. Highlight the user, right click and select Properties.
5. Select User Mapping and ensure the Default Schema for the vCenter Server Database matches the schema of the vCenter Server objects.
6. Select Server Roles and if the schema is not DBO, for instance vmw, ensure sysadmin is not ticked.
Verify that the DSN has the default database set to the vCenter database, for example VIM_VCDB:

On the vCenter Server:
1. In Administrative Tools open the Data Sources (ODBC) and click on the System DSN tab.
2. Locate the vCenter Server DSN and click Configure.
3. Verify the SQL server is correct and click Next
4. Check if the DSN is using Windows Authentication or SQL Authentication and click Next.
  
  Note: If using SQL Authentication you will need to enter the user account password to proceed.
5. In the Change the default database to: drop down list choose the vCenter Server database, for example VIM_VCDB.
6. Click Next, Finish and Test Data Source to ensure the vCenter Server can connect successfully to the database.

Additional Information

Troubleshooting the missing table vpx_dual error in VirtualCenter.