Configure the Microsoft SQL Database for Omnissa Access

To use a Microsoft SQL database for Omnissa Access, you must create a new database in the Microsoft SQL server.

When you create the database, you enter the user name and domain. For Windows Authentication, use the format 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. You must use saas as the schema name.

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

Prerequisites

• Supported version of the Microsoft SQL server installed as an external database server include Microsoft SQL Server 2016, 2017, 2019, and 2022.

• 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. Enter the following commands to create the database with the schema name saas.

DECLARE @DBName SYSNAME = N'dbname';
DECLARE @UserName SYSNAME = N'username'; -- Use DOMAIN\username for the Windows auth
DECLARE @Password NVARCHAR(128) = N'userpassword';  -- Replace with your secure password

DECLARE @SchemaName SYSNAME = N'saas';
-- Step 1: Create the database if it doesn't exist
IF DB_ID(@DBName) IS NULL
BEGIN
    EXEC(N'CREATE DATABASE [' + @DBName + N'] COLLATE Latin1_General_CS_AS;');
END

-- Step 2: Enable Read Committed Snapshot Isolation
EXEC(N'ALTER DATABASE [' + @DBName + N'] SET READ_COMMITTED_SNAPSHOT ON;');

-- Step 3: Create SQL login if it doesn't exist
IF NOT EXISTS (
    SELECT name FROM master.sys.sql_logins WHERE name = @UserName
)
BEGIN
    EXEC(N'CREATE LOGIN [' + @UserName + N'] WITH PASSWORD = N''' + @Password + N''', CHECK_POLICY = ON;');
END

-- Step 4: Use DB and configure user/schema/ownership
DECLARE @sql NVARCHAR(MAX) = '
USE [' + @DBName + N'];

-- Drop user if exists
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N''' + @UserName + N''')
    DROP USER [' + @UserName + N'];

-- Create user
CREATE USER [' + @UserName + N'] FOR LOGIN [' + @UserName + N'] WITH DEFAULT_SCHEMA = [' + @SchemaName + N'];

-- Create schema if not exists
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N''' + @SchemaName + N''')
    EXEC(''CREATE SCHEMA [' + @SchemaName + N'] AUTHORIZATION [' + @UserName + N']'');

-- Optionally grant CONTROL permission on schema to user
GRANT CONTROL ON SCHEMA::[' + @SchemaName + N'] TO [' + @UserName + N'];

-- Add user to db_owner
ALTER ROLE [db_owner] ADD MEMBER [' + @UserName + N'];
';

EXEC(@sql);

4. On the toolbar, click !Execute.

   The Microsoft SQL database server is now ready to be connected to the Omnissa 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 Omnissa 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 Omnissa Access server. See Manage Omnissa Access External Database Settings.