Software Defined Single Sign On Database

September 30, 2013

Table of Contents

Software Defined Single Sign On Database. One difficult task since the release of vSphere 5.1 has been the creation of SSO (Single Sign On) databases.  Creating an SSO database is not that incredibly difficult, but to achieve this; you require a higher level of attention to detail. There are a few reasons for this:

  1. VMware provides multiple MS SQL scripts to set up the back end database environment (rsaIMSLiteMSSQLSetupTablespaces.sql and rsaIMSLiteMSSQLSetupUsers.sql).  To achieve this you have to be aware which script you are running and in what order they need to be run.
  2. The scripts VMware provides are hard coded in many places with things like database names, data file names, log file names, index file names, SQL login names, filegroup and tablespace information.

What VMware provides in the vCenter documentation is all well and good however it’s only good for installing a single SSO database per SQL Server instance.  The problem that presents itself is that when faced with having to stand up multiple SSO environments using a single SQL Server, you need to know what to squeeze in the scripts provided to guarantee instance uniqueness.

And more importantly – what not to put.  For instance, we want to change file names and maybe SQL logins, but mistakenly changing tablespace or filegroup information will most certainly render the database useless for the SSO application.

The problem is that each environment needs a different unique SSO database with the VMware provided scripts, this can be time consuming and error prone, and this can be a problem when deploying a vCenter server. This was an easy task back.

There are a few options to deal with this:

  1. Separate or local SQL installation for each SSO deployment – SQL Server sprawl for this use case is not clear in regard to financial, management, or resource perspective.
  2. vCenter Appliance – the MS SQL support.
  3. Tweak the VMware provided scripts – Combine the two scripts into one and remove the static attributes of the script by introducing TSQL variables via SQLCMD Mode.

The third option, modify the scripts to better suit your needs while also making them somewhat portable for community use. Once you have provided the correct path to your data, log, and index files (index files are typically stored in the same location as data files), the only variable needing changing going forward for a new SSO instance is the database instance prefix.

  • The goal of this script is to provide an easy, consistent, and repeatable

process for deploying multiple vSphere SSO databases on a single SQL Server

instance without having to make several modifications to the two VMware provided

scripts each time a new SSO database is needed.

  • – The following script combines the VMware vSphere 5.1 provided

rsaIMSLiteMSSQLSetupTablespaces.sql and rsaIMSLiteMSSQLSetupUsers.sql scripts

into one script. In addition, it removes the static database and file names

and replaces them with dynamically generated equivalants based on an

InstancePrefix variable declared at the beginning of the script. Database,

index, and log file folder locations are also defined with variables.

  • – This script meets the original goal in that it can deploy multiple iterations

of the vSphere SSO database on a single SQL Server instance simply by modifying

the InstancePrefix variable at the beginning of the script. The script then uses

that prefix with concatenation to produce the database, .mdf, .ldf, .ndf, and

two user logins and their required SQL permissions.

  • – The script must be run in SQLCMD mode (Query|SQLCMD Mode).
  • – No warranties provided. Use at your own risk.

:setvar InstancePrefix “DEVSSODB”

:setvar PrimaryDataFilePath “D:Program FilesMicrosoft SQL            ServerMSSQL10_50.MSSQLSERVERMSSQLDATA”

:setvar IndexFilePath “D:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATA”

:setvar LogFilePath “D:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATA”

USE [master];


Create database

The database name can also be customized, but cannot contain  reserved keywords like database or any characters other than letters, numbers,– _, @ and #.

CREATE DATABASE [$(InstancePrefix)_RSA] ON

PRIMARY(NAME = N’$(InstancePrefix)_RSA_DATA’,

FILENAME = N’$(PrimaryDataFilePath)$(InstancePrefix)_RSA_DATA.mdf’,

SIZE = 10MB,




FILENAME = N’$(IndexFilePath)$(InstancePrefix)_RSA_INDEX.ndf’,

SIZE = 10MB,



LOG ON(NAME = N’$(InstancePrefix)_translog’,

FILENAME = N’$(LogFilePath)$(InstancePrefix)_translog.ldf’,

SIZE = 10MB,




Set recommended performance settings on the database





–Create users

– Change the user’s passwords (CHANGE USER PASSWORD) below.

– The DBA account is used during installation and the USER account is used during

– operation. The user names below can be customised, but cannot contain

– reserved keywords like table or any characters other than letters, numbers, and _ .

– Please execute the scripts as a administrator with sufficient permissions

USE [master];


CREATE LOGIN [$(InstancePrefix)_RSA_DBA] WITH PASSWORD = ‘$(InstancePrefix)_RSA_DBA’, DEFAULT_DATABASE = [$(InstancePrefix)_RSA];


CREATE LOGIN [$(InstancePrefix)_RSA_USER] WITH PASSWORD = ‘$(InstancePrefix)_RSA_USER’, DEFAULT_DATABASE = [$(InstancePrefix)_RSA];


USE [$(InstancePrefix)_RSA];


ALTER AUTHORIZATION ON DATABASE::[$(InstancePrefix)_RSA] TO [$(InstancePrefix)_RSA_DBA];


CREATE USER [$(InstancePrefix)_RSA_USER] FOR LOGIN [$(InstancePrefix)_RSA_USER];



Share on
More posts

Dedicated Servers Quick Guide

What is a Dedicated Servers? Why bother using a dedicated server over a VPS or Shared Hosting?A dedicated server is a server 100% dedicated to your website/project or business needs.

SolusVM Easy Quick Install

Quick Easy Install Debian/Ubuntu apt update reboot wget Centos yum update reboot wget If you get an error it is because you need to install: wget Try a

Physical Memory Limits for Windows Releases

Physical Memory Limits: Windows 7 The following table specifies the limits on physical memory for Windows 7. Version Limit on X86 Limit on X64 Windows 7 Ultimate 4 GB 192 GB Windows 7 Enterprise

Emailing vCloud Organization Users

Due to a lot of requests I found necessary to show you guys who might be having a problem using the “notify users” function in the vCloud Director web UI

cPanel 2-Factor Auth Bypass

2-Factor Authentication Bypass hole in WHM cPanel security has been Reported effected WHM resellers. cPanel, a software provider to manage web hosting/websites, has recently patched a security vulnerability that allowed

Get 90% Discount

First 3 People gets the Bonus!
Don't Miss Out Our Big Sale

Get 0-90% On All
247Rack Services


The Sale Is Until The End Of March