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, DBO_VCDB_objects.zip if redundant since it already exists in Upgrade-Remove-DBO-Role.zip
2. After unzipping Upgrade-Remove-DBO-Role.zip, 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)
- Stop all vCenter server services
- Modify both the verify and upgrade scripts with correct values and send them to your DBA
- DBA will execute the verify scripts for any errors
- If there are no errors, DBA will execute the upgrade script
- Start vCenter server services and perform sanity checks on the vCenter, e.g.perform some vmotion
Phase 2 (Migrating to Windows authenication)
- Again, stop all vCenter server services
- Get DBA to run the SQL script below
- Get DBA to change all scheduled tasks from local use to the domain account
- In Windows, type “runas /user:<domain service account> cmd”, this will start an elevated cmd prompt
- Navigate to C:\Windows\System32 and run odbcad32.exe (Alternatively, you can look for the ODBC icon and runas a different user)
- Reconfigure ODBC for Windows authentication and test your ODBC connection
- Restart all vCenter servicer services
- Perform sanity check on vCenter, e.g. run some vmotion.
CREATE LOGIN [domain\vpxuser] FROM WINDOWS
WITH DEFAULT_DATABASE=[VPX DB]
USE [VPX DB]
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.