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:
I think the installation wizard should be changed to supply options like what most other sql apps with same msdb requirements do. That means have one section ask for an account with administration priviliges for the installation, like ‘sa’ or another administrator. Then have a seperate section ask for vcenter sql account. Vcenter account should be limited to read, write and stored procedure exec rights in the vcenter db, dbowner is overkill and less secure.
I think this should really be changed with vCenter and its frustrating VMware is not thinking with an enterprise mindset.
This means you cannot have vCenter run in a corporate environment on a corporate SQL Server. No corporate DBA’s agree to give any application access to the MSDB system db on their corporate highly available super duper SQL envieonment
This means you are forced to have a separate SQL server just for vC & VUM separate from the pure corporate database environment just for the necessary MSDB rights (even if it is just for installation).
The access required is not for critical vCenter operation, only running stored procedures for stats roll ups.
What VMware should do is warn that the SQL Agent jobs cannot be scheduled without MSDB access but allow the installation to complete.
The stored procedures can then be given to DBA’s who can run the scripts as part of their maintenance activity which makes everyone happy.
Julian, we just install any such app into their own instance. SQL Enterprise allows up to 99 instances (AFAIR) so there’s little reason other than some administrative overhead not to do it this way.
At one point in time I worked at in enterprise shop where the DBAs had no issues granting db_owner on MSDB. In fact, they were comfortable with leaving that role in place for the vcenter account permanently. That last clause surprised me but it did make my life easier.
Ah, the old chestnuts that just WON’T go away… Seems like for YEARS we have been battling with VMware to properly document the perms & rights required for the “installation”, separate and distinct from “operation”. It something that afflicts other products too, SRM being a case in point. Almost NO documentation, apart from cryptic stuff only a DBA would understand, and I’m NOT a SQL DBA!
As I understand it the MSDB access is only required in installs & upgrades. So most of my customers turn it on/off as needed. That seems to keep most DBAs happy even in the most sniffy of corporate environments.
Good post Jason!
Jason
Can you confirm whether vcenter requires SQl Authentication to be used for a remote SQL instance.
We have our 4.1 installation running on a remote SQL 2008 R2 server using windows authentication. I have just come across some info that suggests only SQL Authentication is supported.
is that the case?
Chris: Page 193 of the vSphere 5.0 installation document explains the options:
http://pubs.vmware.com/vsphere-50/topic/com.vmware.ICbase/PDF/vsphere-esxi-vcenter-server-50-installation-setup-guide.pdf
You can use the Microsoft Windows built-in system account or a user account to run vCenter Server. With a
user account, you can enable Windows authentication for SQL Server, and it provides more security.
The user account must be an administrator on the local machine. In the installation wizard, you specify the
account name as DomainName\Username. You must configure the SQL Server database to allow the domain
account access to SQL Server.
The Microsoft Windows built-in system account has more permissions and rights on the server than the vCenter
Server system needs, which can contribute to security problems.
For SQL Server DSNs configured with Windows authentication, use the same user account for the VMware
VirtualCenter Management Webservices service and the DSN user.
If you do not plan to use Microsoft Windows authentication for SQL Server or you are using an Oracle or DB2
database, you might still want to set up a local user account for the vCenter Server system. The only requirement
is that the user account is an administrator on the local machine.
NOTE If you install an instance of vCenter Server as a local system account on a local SQL Server database
with Integrated Windows NT Authentication, and you add an Integrated Windows NT Authentication user
to the local database server with the same default database as vCenter Server, vCenter Server might not start.
See “vCenter Server Fails to Start When Installed as a Local System Account on a Local SQL Server Database
with Integrated Windows NT Authentication,” on page 193.
Thanks for that
had similar issue, but it complains like this:
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) :
%s
resolution:
http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=2006904
Thanks for this. It fixed my issue.
If you’d prefer not to grant the db_owner privilege to the MSDB database, the “SQLAgentOperatorRole” fixed server role can be granted instead. This should suffice for all the permissions listed in the error message in the article.
For further info on the MSDB fixed server roles see: http://msdn.microsoft.com/en-us/library/ms188283.aspx
After installation, I want to revoke the db_owner permissions on MSDB database. Can anyone post the roles/permissions required to be set for the vCenter database account ?
Configure the account used to connect to the vCenter database with the DBO role. So for instance, if you have a SQL account named ‘vcenter’ which is used by the vCenter Server to make the ODBC/DSN connection, it should be granted the DBO role in MS SQL Server.
Jason, the installation guide recommends db_owner roles for vCenter & MSDB databases (or sysadmin). I have the db_owner role for the login on vCenter database but our senior DBA says db_owner role on MSDB is not recommended. The installation guide says something for advanced SQL Admins, the permissions can be set manually. If there is anyone here who has set the permissions, can please post the what they have set ?
The db_owner role on MSDB is only temporarily required during the installation of vCenter, then it can be revoked after the vCenter installation is complete. That role is needed during installation so that the database scripts can install the SQL Agent statistics rollup jobs. I did read in the past 2 months somewhere (I don’t remember where exactly) where an alternate SQL role could potentially be used which also has the ability to install SQL Agent jobs during installation, but again I don’t know what that role was and I don’t know if that is fully supported. The Senior DBA you have on staff would probably know what that role is. You could try it as a POC (I’d love to hear back if it was successful). Your best bet is to follow the vCenter installation guide which is where DBO is specified temporarily.
The installation is completed using db_owner roles on VCDB and MSDB. But now post installation, I will need to remove the db_owner role on MSDB database and set the only the minimum required permissions. I think the answer is on the below link inside installation guide. I will set them and see how it goes.
http://pubs.vmware.com/vsphere-50/index.jsp?topic=/com.vmware.vsphere.install.doc_50/GUID-BBA11020-58CB-44EC-86CA-F8B15625041E.html
Commenting on kind of an old post, but thought I’d add a little something extra that may help someone. Yes, you can use a 32 bit Windows Authentication ODBC connection to connect to a remote VUM database by changing the account that the VUM service runs as, but what about installation? If you are logged on as username JoeAdmin and run the VUM installer it will by default try to connect to the VUM database during VUM installation as the currently logged on user (JoeAdmin), but if you’ve set the VUM and MSDB database owners to a service account (svc-vum), the installation will fail. The workaround = browse the VUM install media to D:\VMware VIM\updateManager\VMware-UpdateManager.exe then Shift+Right Click this exe and choose “Run As Different User”. Enter the svc-vum account credentials and your VUM installation (and consequently the ODBC connection that the installer launches) will run under this user context…win!
Thanks for sharing, great advice Jason. I hope to see more interesting and valuable posts from you in the future.