Setting up the Microsoft SQL Server database for VMware vSphere 5.1 Single Sign On component installation

I have to say that I wasn’t too satisfied with the documentation available setting up the database for installing the VMware vSphere 5.1 Single Sign On component independently:

… rather than using the VMware vCenter Simple Install option:

I found that there are plenty of blog posts, snippets of documents scattered across KB articles and the VMware Documentation Center but not a single document that demonstrates step by step instructions for setting up the database.  The good news is that it’s not too difficult to set up and this post to demonstrate that but before I begin, let me include a few links that I found useful for understanding the installation SSO service a bit more:

Prerequisites for Installing vCenter Single Sign-On, Inventory Service, and vCenter Server
http://pubs.vmware.com/vsphere-51/index.jsp?topic=%2Fcom.vmware.vsphere.install.doc%2FGUID-C6AF2766-1AD0-41FD-B591-75D37DDB281F.html

Installing vCenter Server 5.1 best practices
http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=2021202

vCenter Single Sign-On FAQ
http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=2034918

Locating SSO (Single Sign On) Database Scripts

There are actually official scripts that VMware includes in the vSphere 5.1 installation package to automate the creation and security assignment of user accounts for the database.  These scripts can be found in the following location (assuming you’ve extracted the package to your C drive):

C:\VMware-VIMSetup-all-5.1.0-880471\Single Sign On\DBScripts\SSOServer\schema\mssql

1. rsaIMSLiteMSSQLCreateSchema.sql
2. rsaIMSLiteMSSQLDropSchema.sql
3. rsaIMSLiteMSSQLDropUsers.sql
4. rsaIMSLiteMSSQLSetupTablespaces.sql
5. rsaIMSLiteMSSQLSetupUsers.sql

The scripts we’ll be using are:

1. rsaIMSLiteMSSQLSetupTablespaces.sql
2. rsaIMSLiteMSSQLSetupUsers.sql

Begin by launching SQL Server Management Studio and opening the first script:

rsaIMSLiteMSSQLSetupTablespaces.sql

CREATE DATABASE RSA ON PRIMARY(

    NAME='RSA_DATA',

FILENAME='C:\CHANGE ME\RSA_DATA.mdf',

    SIZE=10MB,

    MAXSIZE=UNLIMITED,

    FILEGROWTH=10%),

FILEGROUP RSA_INDEX(

    NAME='RSA_INDEX',

FILENAME='C:\CHANGE ME\RSA_INDEX.ndf',

    SIZE=10MB,

    MAXSIZE=UNLIMITED,

    FILEGROWTH=10%)

LOG ON(

    NAME='translog',

FILENAME='C:\CHANGE ME\translog.ldf',

    SIZE=10MB,

    MAXSIZE=UNLIMITED,

    FILEGROWTH=10% )

GO

-- Set recommended perform settings on the database

EXEC SP_DBOPTION 'RSA', 'autoshrink', true

GO

EXEC SP_DBOPTION 'RSA', 'trunc. log on chkpt.', true

GO

CHECKPOINT

GO

------------------------------------------------------------------------------------------------------------------------------------------------------------------

It’s important to note that names RSA_DATA and RSA_INDEX are supposed to be hard coded in the Single Sign On service and while this may change in the future, it’s best to not attempt to change them.  With that being said, you are free to change the database name RSA to a different name.  Whether you change use a different name would be your choice but to keep the database recognizable to anyone upon first glance, I’m going to continue using RSA as the name.  This means that the only content I’m going to change is the location of the mdf, ndf and ldf files highlighted in RED:

CREATE DATABASE RSA ON PRIMARY(

    NAME='RSA_DATA',

FILENAME='E:\Databases\RSA_DATA.mdf',

    SIZE=10MB,

    MAXSIZE=UNLIMITED,

    FILEGROWTH=10%),

FILEGROUP RSA_INDEX(

    NAME='RSA_INDEX',

FILENAME='E:\Databases\RSA_INDEX.ndf',

    SIZE=10MB,

    MAXSIZE=UNLIMITED,

    FILEGROWTH=10%)

LOG ON(

    NAME='translog',

FILENAME='E:\Logs\translog.ldf',

    SIZE=10MB,

    MAXSIZE=UNLIMITED,

    FILEGROWTH=10% )

GO

-- Set recommended perform settings on the database

EXEC SP_DBOPTION 'RSA', 'autoshrink', true

GO

EXEC SP_DBOPTION 'RSA', 'trunc. log on chkpt.', true

GO

CHECKPOINT

GO

--------------------------------------------------------------------------------------------------------------------------------------------------------------------

Executing the script above should create the RSA database shown in the Object Explorer on the left:

With the database created, the next step is to set up the 2 user objects required for accessing the RSA database.  To so do, use the following script:

rsaIMSLiteMSSQLSetupUsers.sql

USE MASTER

GO

CREATE LOGIN RSA_DBA WITH PASSWORD = '<CHANGE DBA PASSWORD>', DEFAULT_DATABASE = RSA

GO

CREATE LOGIN RSA_USER WITH PASSWORD = '<CHANGE USER PASSWORD>', DEFAULT_DATABASE = RSA

GO

USE RSA

GO

ALTER AUTHORIZATION ON DATABASE::RSA TO [RSA_DBA]

GO

CREATE USER RSA_USER FOR LOGIN [RSA_USER]

GO

CHECKPOINT

GO

------------------------------------------------------------------------------------------------------------------------------------------------------------------

It’s important to note that the comments state the following:

-- The DBA account is used during installation and the USER account is used during
-- operation. The user names below can be customised, but it is forbidden to include
-- reserved keywords like table or any characters other than letters, numbers, and _ .

So proceed with using the default user account names RSA_USER and RSA_DBA or change them if you would like to use another name.  For the purpose of this demonstration and to keep things consistent with the first script used to create the database, I will only change the passwords highlighted in RED:

USE MASTER

GO

CREATE LOGIN RSA_DBA WITH PASSWORD = '2wsx@WSX', DEFAULT_DATABASE = RSA

GO

CREATE LOGIN RSA_USER WITH PASSWORD = '2wsx@WSX', DEFAULT_DATABASE = RSA

GO

USE RSA

GO

ALTER AUTHORIZATION ON DATABASE::RSA TO [RSA_DBA]

GO

CREATE USER RSA_USER FOR LOGIN [RSA_USER]

GO

CHECKPOINT

GO

--------------------------------------------------------------------------------------------------------------------------------------------------------------------

Executing the script above should create the user accounts and assign the appropriate permissions to the RSA database:

Note the 2 accounts located under Security –> Logins:

• RSA_DBA
• RSA_USER

 

Opening the properties of RSA_DBA should show that this account is given:

• db_owner
• public

… membership for the RSA database and opening the properties of RSA_USER should show that this account is given:

• public

… membership for the RSA database.

**Note that there is no need to run the other scripts such as:

rsaIMSLiteMSSQLCreateSchema.sql

… or you may run into the error message:

Error 29120. Database schema already exists

… that I blogged about earlier here: http://terenceluk.blogspot.com/2013/02/installing-vsphere-51-vcenter-single.html

With the RSA database created and the user accounts RSA_USER and RSA_DBA created and assigned permissions, proceed with the vCenter Single Sign On install: