Software Defined Single Sign On Database

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];

GO

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,

MAXSIZE = UNLIMITED,

FILEGROWTH = 10% ),

FILEGROUP RSA_INDEX(NAME = N’$(InstancePrefix)_RSA_INDEX’,

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

SIZE = 10MB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 10%)

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

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

SIZE = 10MB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 10% );

GO

Set recommended performance settings on the database

ALTER DATABASE [$(InstancePrefix)_RSA] SET AUTO_SHRINK ON;

GO

ALTER DATABASE [$(InstancePrefix)_RSA] SET RECOVERY SIMPLE;

GO

–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];

GO

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

GO

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

GO

USE [$(InstancePrefix)_RSA];

GO

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

GO

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

GO

Home