Configure the Microsoft SQL Database with Windows Authentication Mode

To use a Microsoft SQL database for 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 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.

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

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, 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
   
   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 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