RSS

vCenter server SQL database custom DB schema and Windows authentication

10 Apr

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.

Note:

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)

  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 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.

Advertisements
 
2 Comments

Posted by on April 10, 2015 in vmware, Windows

 

Tags: ,

2 responses to “vCenter server SQL database custom DB schema and Windows authentication

  1. Will

    April 15, 2015 at 9:31 pm

    Struggling with modifying the scripts to use a windows domain account for the VCenterUSER, I don’t suppose you know or could detail the required changes?

     
    • Kelvin Wong

      April 16, 2015 at 10:25 pm

      I am not an SQL expert but I believe that the username needs to be enclosed with square brackets [] for the commands to work properly. You need to test it out and see where they fail and fix the lines that fail.

       

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: