To use a Microsoft SQL database for the VMware Identity Manager, you must create a new database in the Microsoft SQL server. During setup, you must select an authentication mode for the database. If you select Windows Authentication, when you create the database, you enter the user name and domain. The user name and domain is entered as domain\username.

When you run the Microsoft SQL commands, you create a database on the Microsoft SQL server, enter the database name, add the login user credentials, and create the schema. The schema name is saas.

Note:

The default collation is case-sensitive.

Prerequisites

  • Supported version of the Microsoft SQL server installed as an external database server.

  • Load balancing implementation configured.

  • Windows Authentication selected as the authentication mode.

  • Administrator rights to access and create the database components using Microsoft SQL Server Management Studio or from another Microsoft SQL Server CLI client.

Procedure

  1. Log in to the Microsoft SQL Server Management Studio session as the sysadmin or a user account with sysadmin privileges.

    The editor window appears.

  2. In the toolbar, click New Query.
  3. To create the database with the default schema named saas, enter the following commands in the editor window.

    /*
    Values within angle brackets (< >) are example values. When replacing the example value,
    remove the angle brackets. The database name is case sensitive. Make sure you enter the database name the same in all instances.
    */
    
    
    CREATE DATABASE <saasdb>
    COLLATE Latin1_General_CS_AS;
    ALTER DATABASE <saasdb> SET READ_COMMITTED_SNAPSHOT ON;
    GO
    
    IF NOT EXISTS
    (SELECT name
    FROM master.sys.server_principals
    WHERE name=N'<domain\username>')
    BEGIN
    CREATE LOGIN [<domain\username>] FROM WINDOWS;
    END
    GO
    
    USE <saasdb>; 
    IF EXISTS (SELECT * FROM sys.database_principals WHERE name=N'<domain\username>')
    DROP USER [<domain\username>]
    GO
    
    CREATE USER [<domain\username>] FOR LOGIN [<domain\username>] 
    WITH DEFAULT_SCHEMA=saas;
    GO
    
    CREATE SCHEMA saas AUTHORIZATION "<domain\username>"
    GRANT ALL ON DATABASE::<saasdb> TO "<domain\username>";
    GO
    
    ALTER ROLE db_owner ADD MEMBER "<domain\username>";
    GO
    
    

  4. On the toolbar, click !Execute.

    The Microsoft SQL database server is now ready to be connected to the VMware Identity Manager database.

    The server role used to grant server-wide security privileges is set to public. The database role membership is db_owner. Do not set any other roles.

Results

When you install the VMware Identity Manager for Windows, you select this database server instance to connect to. After the installation, the JDBC URL and the user name and password created for the database are configured in the Database Connection Setup page in the VMware Identity Manager server. See Configure VMware Identity Manager to Use an External Database