Configure Microsoft SQL Database Using Local SQL Server Authentication Mode for Workspace ONE Access

To use a Microsoft SQL database for the Workspace ONE Access, 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.

See the Microsoft SQL documentation for information about the file naming conventions before you create the database name.

Note: The default database collation is case-sensitive.

Prerequisites

• Supported version of the Microsoft SQL server installed as an external database server.
  Note: Microsoft SQL server 2012 and 2014 must be updated with the Microsoft SQL patch to support TLS 1.2.
• 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.

   You can define the COLLATE Windows collation name, case sensitivity and accent sensitivity, The default is Latin1_General_CS_AS. CS specifies case-sensitive, AS specifies accent-sensitive.

   Important: If you change the COLLATE value of the Microsoft database from Latin1_General_CS_AS, you must update the property datastore.collation in the runtime-config.properties file with that collation value.

   /*
   Values within angle brackets (< >) are example values. When replacing the example value,
   remove the angle brackets. The database name is case sensitive, and the name must be one word with no spaces. 
   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 Workspace ONE Access 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 Workspace ONE Access, 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 Workspace ONE Access server. See Configure Workspace ONE Access to Use an External Database