It’s that time again (to bring up the age old topic of Microsoft SQL database permission requirements in order to install VMware vCenter Server). This brief article focuses on vCenter 5.0. Permissions on the SQL side haven’t changed at all based on what was required in vSphere 4. However, the error displayed for lacking required permissions to the MSDB System database has. In fact, in my opinion it’s a tad misleading.
To review, the vCenter database account being used to make the ODBC connection requires the db_owner role on the MSDB System database during the installation of vCenter Server. This facilitates the installation of SQL Agent jobs for vCenter statistic rollups.
In the example below, I’m using SQL authentication with an account named vcenter. I purposely left out its required role on MSDB and you can see below the resulting error:
The DB user entered does not have the required permissions needed to install and configure vCenter Server with the selected DB. Please correct the following error(s): The database user ‘vcenter’ does not have the following privileges on the ‘vc50’ database:
EXECUTE sp_add_category
EXECUTE sp_add_job
EXECUTE sp_add_jobschedule
EXECUTE sp_add_jobserver
EXECUTE sp_add_jobstep
EXECUTE sp_delete_job
EXECUTE sp_update_job
SELECT syscategories
SELECT sysjobs
SELECT sysjobsteps
Now what I think is misleading about the error thrown is that it’s pointing the finger at missing permissions on the vc50 database. This is incorrect. My vcenter SQL account has db_owner permissions on the vc50 vCenter database. The problem is actually lacking the temporary db_owner permissions on the MSDB System database at vCenter installation time as described earlier.
The steps to rectify this situation are the same as before. Grant the vcenter account the db_owner role for the MSDB System database, install vCenter, then revoke that role when vCenter installation is complete. While we’re on the subject, the installation of vCenter Update Manager 5.0 with a Microsoft SQL back end database also requires the ODBC connection account to temporarily have db_owner permissions on the MSDB System database. I do believe this is a new requirement in vSphere 5.0. If you’re going to install VUM, you might as well do that first before going through the process of revoking the db_owner role.
An example of where that role is added in SQL Server 2008 R2 Management Studio is shown below: