Tag Archives: mssql

vCenter server SQL database custom DB schema and Windows authentication

What is the standard method of provisioning a new SQL database for your vCenter server? In most organisation, it would be something like this:

  • DBA will create a new SQL login account
  • DBA will create the blank database (with required configurations, of course) and grant db owner role to the account
  • You install vCenter and specify to use SQL login credentials

This is probably this simplest and quick way to setup the SQL database. Now what if, due to regulatory commitments and best practices, your DBA tells you that you had to use Windows authentication instead and NOT use the db owner roles?

Well, switching over to Windows authentication is straight forward enough, but how not to use the db owner role? Well, if you are installing a brand new vCenter, then the setup is quite straight forward. In the vCenter 5.5 installation guide, sections “Create a SQL Server Database and User for vCenter Server” (page 33) and “Use a Script to Create a Microsoft SQL Server Database Schema and Roles” (page 36) details how this can be done.

This will create a custom DB schema to grant permission to the vpxuser account. With the new custom DB schema, the vpxuser must be in VC_USER_ROLE for regular operations and VC_ADMIN_ROLE during installation and upgrades

Now if you have an existing vCenter 5.x server and need to migrate over to a custom DB schema, more work is required. The main details are described in VMware KB 1036331. There are no shortcuts, you do need to read through the steps and work out together with your DBA on how to get it migrated.


1. The KB contains two attached files, IMHO, if redundant since it already exists in

2. After unzipping, rename all the files by remove “Upgrade-Remove-DBO-Role” prefix. If you keep the file name as is, the script will fail.

3, For both VCenterUSER and VCUSER, you can only use a local SQL account, the script will fail if VCenterUSER is a domain account, i.e. domain\user syntax. If you are already using Windows authentication, you need to modify the script before use.

The steps can be summarized as follows (assuming that you are migrating to a custom DB schema AND from a local SQL account, since I already mentioned that you will probably fail if you use the attached script with Windows authenticated account, without modifying the script:

Phase 1 (Migrating to custom DB schema)

  1. Stop all vCenter server services
  2. Modify both the verify and upgrade scripts with correct values and send them to your DBA
  3. DBA will execute the verify scripts for any errors
  4. If there are no errors, DBA will execute the upgrade script
  5. Start vCenter server services and perform sanity checks on the vCenter, e.g.perform some vmotion

Phase 2 (Migrating to Windows authenication)

  1. Again, stop all vCenter server services
  2. Get DBA to run the SQL script below
  3. Get DBA to change all scheduled tasks from local use to the domain account
  4. In Windows, type “runas /user:<domain service account> cmd”, this will start an elevated cmd prompt
  5. Navigate to C:\Windows\System32 and run odbcad32.exe (Alternatively, you can look for the ODBC icon and runas a different user)
  6. Reconfigure ODBC for Windows authentication and test your ODBC connection
  7. Restart all vCenter servicer services
  8. Perform sanity check on vCenter, e.g. run some vmotion.
USE [master]
CREATE USER [domain\vpxuser] FOR LOGIN [domain\vpxuser] WITH DEFAULT_SCHEMA=[VMW]

Note: If you are also migrating the VUM service account to Windows authentication, you need to launch (runas) the 32-bit ODBC in C:\Windows\SysWOW64 instead.


Posted by on April 10, 2015 in vmware, Windows


Tags: ,

Fixing vCenter 5.5 server’s database usage monitoring issue in service health status

Sometimes an upgrade with swanky new features may be the opposite of what you want. A good example is the inclusion of database usage monitoring within vCenter 5.5 server’s service status.

If your SQL database was or is provisioned as usual and you install vCenter 5.5 or upgrade to it, you will see the following amber warning when you check the vCenter service health status:

“Unable to monitor database storage usage. Refer to VMware KB 2078305 for details.”

But when you fire up that KB, it tells you nothing on how to resolve this warning but rather tells you how to manage your database. This feature, it seems was available since vCenter 5.0 but it never showed any warning if you did not configure your database. But with 5.5 (or maybe even 5.1, sorry we skipped that version), you are force to deal with this and cannot ignore it.

The link from tells you that you need to run the following on your SQL database:

grant VIEW SERVER STATE to [vpxuser] --&gt; where vpxuser is the account used to access the vCenter database

However, after making this configuration, the warning never went away and I gave VMware support a call. And it turns out, an additional configuration is required to fix this. So the full configuration is:

 use master
 grant VIEW SERVER STATE to [vpxuser]

However, once database monitoring is enable, the status turns red with the following message:

“Database storage space is critically low and affects vCenter Server functionality. Refer to VMware KB 2078305 for details.”

If you manage a virtualized estate in a big enterprise like I do, you will know that it is highly unlikely that the databases are that full or else the DBAs will be pounding on my door daily!

The VMware support guy pointed me to this link ->

It turns out that with database monitoring turned on, vCenter will alert you on ALL drives space issue on your database server, regardless whether they are used by the database or not. Typically, we have the SQL database running off a Microsoft cluster with a quorum of size 1 GB.

The default threshold alerts is found in vCenter’s advanced settings: value=5000 value=1000

So I must have at least 5 GB of free space in ALL drives to keep it green. This is obviously impossible in a clustered environment using disk quorum. Further, we have a system volume where the application is installed and which doesn’t grow over time and has about 500 MB free space most of the time.

Here is the vpxd-xxx.log capture identify the drives which are below the default threshold

2015-02-05T20:48:30.686-05:00 [09596 info 'utilvpxdVdb'] [VpxdVdb::GetDBSpaceStatus] WarningThreshold(MB): 5000 AlertThreshold(MB): 1000. Updating Health Message Parameter to
 --&gt; Drive: Q, Free Space:479 MB, Used Space: 0 MB
 --&gt; Drive: H, Free Space:454 MB, Used Space: 2320 MB
 --&gt; Drive: I, Free Space:461 MB, Used Space: 0 MB
 --&gt; Drive: C, Free Space:37381 MB, Used Space: 0 MB
 --&gt; Drive: D, Free Space:66157 MB, Used Space: 0 MB
 --&gt; Total Used Space: 2320 MB, Minimum Free Space: 454 MB

My solution is to set the threshold low so that this status is always green. This is not a problem for us because we don’t need to monitor the disk space on the SQL servers, the DBA team does it and will ping us if disk space is low. However, if you are a do-it-all administrator, ensure that you have other disks monitoring tools when doing this. value=200 value=100

This is a case of new features adding more noise of some of us. I hope VMware will update this feature to allow us to selective turn on and off monitoring of each disk and change the threshold for each disk.


Posted by on February 12, 2015 in vmware


Tags: ,

MSSQL DB instance cannot be connected via UDP 1434

Yesterday there was a major outage in the office. Apparently an important application could not connect to it database. The usual means of connecting to a database would be via the following context: server\instance_name. When you connect via this method, the client driver talks to the UDP 1434 port on the DB server to get a connect. Somehow, this did not work today.

There have been some patches done to the servers the night before, but nobody could figure out what was wrong with the instance. None of them would cause such problems. Stranger still, this occurs at the instance level not the SQL server. Another instance on the same SQL server could be connect without any problems.

To complicate the matter, you could connect to this trouble instance via a port connection syntax instead: server,port_num. And worked…

A netmon capture between the client and server show that the client tried to make a connection to the server via UDP 1434 and the server recieves that request… but it does nothing! As if it doesn’t understand what its supposed to do. Netstat veried that the server is still listening on UDP on 1434.

Well, this occur during APAC time and come London start of day, some smart DBA said that they had a similar problem with another DB sometimes back and the resolved the issue!

Apparently, this issue was caused by a missing registry entry:

HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\\MSSQLSERVER\SuperSocketNetLib\Np

After recreating this registry from a working instance, it started working immediately!

Leave a comment

Posted by on October 4, 2006 in Windows