Configure Microsoft SQL Database Using Local SQL Server Authentication Mode

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 SQL Server Authentication, when you create the database, you enter a local user name and password.

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 is named saas.

Note: The default database collation is case-sensitive.

Prerequisites

• Supported version of the Microsoft SQL server installed as an external database server.
• Load balancing implementation configured.
• SQL Server 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
   
   BEGIN
   CREATE LOGIN <loginusername> WITH PASSWORD = N'<password>';
   END
   GO
   
   USE <saasdb>; 
   IF EXISTS (SELECT * FROM sys.database_principals WHERE name=N'<loginusername>')
   DROP USER [<loginusername>]
   GO
   
   CREATE USER [<loginusername>] FOR LOGIN [<loginusername>]
   WITH DEFAULT_SCHEMA=saas;
   GO
   
   CREATE SCHEMA saas AUTHORIZATION <loginusername>
   GRANT ALL ON DATABASE::<saasdb> TO <loginusername>;
   GO
   
   ALTER ROLE [db_owner] ADD MEMBER <loginusername>;
   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