Confirm Microsoft SQL Database Is Correctly Configured

To confirm that the Microsoft SQL database is configured correctly to work with VMware Identity Manager, the following verification script runs after the database is configured.

Prerequisites

The Microsoft SQL database is created for the VMware Identity Manager service.

Procedure

1. Log in to the Microsoft SQL Server Management Studio session with the <saasdb> login user name and password that was created in the script you used to create the database.
   The editor window appears.
2. In the toolbar, click New Query.
3. Run the following commands. Edit the commands as required.

    execute as user = 'domain\username' 
   
   
   /* Check if user is db owner. Return true */
   SELECT IS_ROLEMEMBER('db_owner') as isRoleMember 
    
   /* Make sure user is not sysadmin. Should return false */
   SELECT IS_SRVROLEMEMBER('sysadmin')  as isSysAdmin
    
   /* check if saas schema exists, should be not null */
   SELECT SCHEMA_ID('saas') as schemaId
    
   /* check schema owner, should be user provided to installer */
   SELECT SCHEMA_OWNER FROM INFORMATION_SCHEMA.SCHEMATA where SCHEMA_NAME='saas'
    
   /* check if saas is user default schema, should return saas */
   SELECT SCHEMA_NAME() as SchemaName
    
   /* check db collation, should return Latin1_General_CS_AS */
   SELECT DATABASEPROPERTYEX('<saasdb>', 'Collation') AS Collation
    
   /* check if read committed snapshot is on, should return true */
   SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name='<saasdb>'
   

4. On the toolbar, click !Execute.

   If the configuration is not correct, error messages are displayed. Before continuing to configure the VMware Identity Manager service to use the external Microsoft SQL database, correct the problems described in the error messages.