vCenter Server performance charts do not display after performing SQL security hardening

Products

• VMware vCenter Server

Issue/Introduction

 

Symptoms:
After performing SQL security hardening procedures, you experience these symptoms:

• vCenter Server performance charts display a message for all objects:
  
  No data available
   
• vCenter Server real time performance statistics are visible.
• The vCenter Server log file, vpxd.log (located at C:\ProgramData\VMware\VMware VirtualCenter\Logs)show statistics are being processed normally.
• The VPX_HIST_STATx tables do not contain any data.
• While executing the insert_stats_proc stored procedure, the VPX_PROC_LOG table reports errors similar to:
  
  Error:229 at line:1 :The EXECUTE permission was denied on the object 'sp_executesql', database 'mssqlsystemresource', schema 'sys'.- INSERT INTO VPX_HIST_STAT1_233(COUNTER_ID, TIME_ID, STAT_VAL) SELECT @p1, @p2, @p3,5174,85215,26

Environment

VMware vCenter Server 5.1.x

Cause

This issue occurs when the vCenter Server Database user, configured in the vCenter Server data source name (DSN), does not have the execute permission on the SQL Server system stored procedure (sp_executesql) to execute dynamically generated SQL statements.

By default, all SQL server users are granted Execute permission on the sp_executesql stored procedure. If this permission is revoked during SQL Server hardening procedures, insert_stats_proc fails to run and hence vCenter Server performance statistics fail to insert in to the VPX_HIST_STAT1_X tables and performance charts are empty.

Resolution

To resolve this issue, perform these steps:

• Grant the execute permission to the vCenter Server Database user on the sp_executesql system stored procedure.
• Replace the existing insert_stats_proc stored procedure so that correct error information is displayed in the vpxd.log file.

To grant the execute permission to the vCenter Server Database user:

1. Connect to SQL Server Management Studio as the SQL Administrator (sa).
2. Under the Databases folder, select the database and expand Programmability as in below image.
   • ​​​​​​​
3. Expand Extended Stored Procedures and System Extended Stored Procedures.
4. Right-click sp_executesql and click Properties
5. Select Permissions.
6. Click Search to open the Select users or users dialog box.
7. Click Browse.
8. Select the checkbox next to the vCenter Server Database user.
9. Click OK.

To replace the insert_stats_proc stored procedure:

1. Stop the VMware VirtualCenter Server service. For more information, see Stopping, starting, or restarting vCenter services (1003895).
2. Connect to SQL Server Query Analyzer or SQL Server Management Studio as the vCenter Server user.
3. Click the vCenter Server database to select it.
4. Click New Query.
5. Enter this query and click Execute:
   
   drop procedure insert_stats_proc
   
   In the Messages pane, you see:
   
   Command(s) completed successfully
    
6. Download the 2059546_insert_stats_proc_mssql.zip file attached to this KB article and extract the contents.
7. Copy the contents of the insert_stats_proc_mssql.sql file and paste it in to the query window.
   
   Note: Delete the drop procedure insert_stats_proc query from step 5.
    
8. Click Execute.
   
   In the Messages pane, you see:
   
   Command(s) completed successfully
    
9. Start the VMware VirtualCenter Server service. For more information, see Stopping, starting, or restarting vCenter services (1003895).

Additional Information

How to stop, start, or restart vCenter Server services