I jumped right into the installation of vSphere 5.1 when it went GA a few days ago, and like most was surprised with the requirement to install vCenter Single Sign On (I’ll call it vCSSO from here). I think it’s great, but had several issues during the first few installation attempts, all around the database.

I did not want to use a locally installed SQL server, so I created a database on the same instance as my lab vCloud Director database. From there, I used the sql scripts that are included with the installation media (ISO\Single Sign On\DBScripts\SSOServer\schema\mssql). You do have to edit some of the sql files, though, but it’s simple. For instance, in rsaIMSLiteMSSQLSetupUsers.sql you simply need to set a Str0ngp@ssw0rd! and in rsaIMSLiteMSSQLSetupTablespaces.sql you need to specify where to place the database files.

I figured I’d step through creating the database using both routes, and even bypass the strong password requirement. Keep in mind, this is for home labs and likely not ‘best practice’ for the enterprise. Lets see how it goes…

What do we need first?

First things first, you will need a server (I’m using a VM with Windows 2008 R2) with a flavor of SQL (I’m using SQL Express 2008 R2).  Of course, you’ll need the install bits, get them however you choose.  There are some settings in SQL that need to be set up before vCSSO will connect.

Go into the SQL Server Configuration Manager, expand SQL Server Network Configuration, edit the properties of Protocols for (whatever instance name you chose) and make sure Force Encryption is set to No.

Since the connection uses JDBC, you have to make sure you specify a port, since it’s a static string and cannot handle dynamic ports.  Click on Protocols for (instance), edit the properties of TCP/IP, and then go to the IP Addresses tab.  I left IP1 alone, but set IP2 (where my IPv4 address is) to Enabled = Yes, and set it’s TCP Port to 1433 (or another port of your choosing).  You’ll want to make sure the firewall is allowing connections in on the port you specify.

For posterity, I also set TCP Port to 1433 for “IPAll” (at the bottom of IP Addresses if you scroll down).

Creating the vCSSO database using SQL scripts

Here I’ll cover the installation of the database using SQL Server Management Studio with the supplied sql files.  Keep in mind you can run these .sql files however you like, but the mgmt studio supports running them, so I used that.  Launch the mgmt studio and connect to your db server\instance using a user account with admin privileges to create the database. Since I’m running on different machines, I copied the sql files over to the root of D, again they’re located on the ISO\Single Sign On\DBScripts\SSOServer\schema\mssql. The first file we’ll want to open is rsaIMSLiteMSSQLSetupTablespaces.sql, this actually creates the database for us.

If you want to break it down, you can see where it creates a database named ‘RSA’, creates RSA_DATA on the PRIMARY file group for this database, creates a new file group & file named ‘RSA_INDEX’, and the transaction log file. It also sets a few extra options: autoshrink to true; and ‘trunc. log on chkpt.’ to true (I couldn’t find this one in SQL Express).

You’ll need to change “C:\Change Me” to the directory of your choice, I used d:\vcsso_db for all three. In large environments, you’ll want to separate them on different classes of disk for performance reasons. Once you’ve change it, click the parse button to validate the code is good and no typos exist (sadly, it won’t pick up typos in your locations, though).

A results pane will pop and tell you the good news (or bad). Fix any errors if they exist, and try again until it is successful, then click Execute. If all went well, you should see a green check at the bottom that says “Query executed successfully”. Click on “Databases” on the left pane and refresh your view. You should now see the RSA database.

Now open rsaIMSLiteMSSQLSetupUsers.sql to create the users

Here I added “CHECK_POLICY = OFF” so I didn’t have to use a strong password for my lab. Click parse, if all’s well, click Execute. Hopefully it went well. If you go back and look at the properties of the RSA database, you will see RSA_DBA is now the owner.

That’s really it, you don’t need to run the Schema sql file, the installer will do it all for you.

Manually creating the database for vCSSO using SQL Management Studio

Now, if you don’t want to use any of the scripts, you have to do this in a different order, like this:

1. Create sysadmin user, I used vCSSO_DBA
2. Create database, I used vCSSO_DB, & list vCSSO_DBA as the owner
3. Edit vCSSO_DBA and list vCSSO_DB as the default db
4. Create db user, I used vCSSO_User, and list them as a public user

Sounds simple, right? Really is, but there are some gotchas I’ll cover.

Step 1 is simple, not much to it, just create the user vCSSO_DBA, accept the defaults and specify a password.
 (yes, I fat-fingered the name to vCSSA :P)
**IMPORTANT**
Make sure you UNCHECK the following:

Step 2 is more complex, but you can do it! Create the database named vCSSO_DB. Make sure you list vCSSO_DBA as the owner. Next, you’ll want to set the first logical name to RSA_DATA, set it’s size to 10MB and 10% growth. Change the log to RSA_log. Add a new file, name is RSA_INDEX, and make sure you create a new filegroup named RSA_INDEX for it. Also set it to 10MB and 10% growth.
 

For step 3 you stimply edit vCSSO_DBA and set the default database to vCSSO_DB.

You can also check User Mapping to validate dbo for the vCSSO_DB.

Now create a database user for step 4, which is easy. Simply create a new user named vCSSO_User and select it’s default database as vCSSO_DB.

**IMPORTANT**
Make sure you UNCHECK the following:

Before saving, you need to go to User Mapping and map them to the vCSSO_DB as a user with the dbo schema

Contacting the database

Now you should be able to continue with the installation.

You’ll need to select Mssql as the type, name is whatever you used (for this demo, I used vCSSO_DB, and it worked fine, just forgot to screenie it before I continued :(, shame on me!), I prefer to use the host name, set the port (jdbc can’t handle dynamic ports), and check the box to use manually created users.  Otherwise it uses your windows credentials to try to set up the RSA_USER account.

The first box, Database user name should contain the vCSSO_User account we created, while Database DBA User name should contain the vCSSO_DBA database owner account we created.  Click Next, sit back and let it do it’s thing.

UPDATE 09-13-2012

If you change the default installation directory, you will likely get error “Error 20020. Failed to update values in server.xml file” as pointed out here by Derek Seaman.  He has a nice walk through of the entire installation.