Install and configure Microsoft SQL Server (KOTS)

If you are using Microsoft SQL Server for your database, follow these steps to install and configure it.

Important considerations

You must have full database admin permissions to the server hosting the SQL Server database.
If you need to upgrade the Microsoft SQL Server, see "Install and configure Microsoft SQL Server (upgrading traditional to KOTS)" in Jama Connect User Guide 9.17.x.

Before installing Jama Connect 9.17.x

Install Microsoft SQL Server 2022 for the database server.
Create an empty Jama Connect database and two additional database schemas for the installation to succeed.
Jama Connect requires that the MSSQL COMPATIBILITY_LEVEL value is 130 or greater.
To confirm the current value:
```
SELECT compatibility_level FROM sys.databases WHERE name = <DATABASENAME>;
```
To modify the value:
```
ALTER DATABASE <DATABASENAME> SET COMPATIBILITY_LEVEL = 130;
```
For more information, see https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver16

Organizations using Microsoft SQL Server must enter database users in Replicated. Without these entries, the installation will fail.

The new schema must be created for a successful installation. Otherwise, the system continues to attempt to connect to the databases and produces log failures. After you create the database schemas, you must restart Jama Connect.

For more information, go to Supported software, environments, and system requirements and select your version of Jama Connect.

Follow these steps for a first-time installation of Jama Connect:

Connect to the SQL Server using a SQL management application (such as SQL Server Management Studio).
Replace the following values in the installation script: <JAMA_LOGIN_Psswd>, <SAML_LOGIN_Psswd> & <OAUTH_LOGIN_Psswd>.
Copy and store the passwords you create here. You will need them later to configure the Admin Console settings.

In a new query window, run this SQL query script:

-- Fresh Install Preparation SCRIPT
/*
Jama Connect Preparation Commands for a fresh install. It is required to run
 these command / script on the Microsoft SQL Server BEFORE running the Jama 
Connect 8.62.x install
for ON-PREM installation using Microsoft SQL Server 2016 - 2019
DATE: 05/10/2021
NOTES:
This script assumes this is a new Installation of JAMA Connect. DO NOT RUN 
THIS SCRIPT ON AN EXISTING JAMA INSTALLATION.
The script will create a new empty JAMA database, add 2 new schemas (empty) to 
the Jama Database, 2 new DB Logins and Database users to support the Multi-
Auth functionality released in Jama Connect 8.62.0.

INSTRUCTIONS:
This script must be run prior to Jama installation or installation may fail to 
complete.
Modify the <JamaUser_LOGIN_Psswd>, <SAML_LOGIN_Psswd> & <OAUTH_LOGIN_Psswd> 
values in the script below before Execution. 
Passwords must be enclosed in single quotes.
*/

USE master;
CREATE LOGIN jamauser with password = 'password';
CREATE LOGIN samluser with password = 'password';
CREATE LOGIN oauthuser with password = 'password';
GO

USE master;
CREATE DATABASE jama;
GO
ALTER DATABASE jama SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE jama CONVERT TO CHARACTER SET latin1 [COLLATE = 'latin1_general_CI_AI'];
GO

USE jama;
EXEC ('CREATE SCHEMA oauth');
EXEC ('CREATE SCHEMA saml');
GO

USE jama;
CREATE USER jamauser for LOGIN jamauser;
CREATE USER samluser for LOGIN samluser with DEFAULT_SCHEMA=saml;
CREATE USER oauthuser for LOGIN oauthuser with DEFAULT_SCHEMA=oauth;
GO

EXEC sp_addrolemember N'db_owner', jamauser;
EXEC sp_addrolemember N'db_owner', samluser;
EXEC sp_addrolemember N'db_owner', oauthuser;
GO

Create a database schema for Quartz to support horizontal scaling in KOTS:

USE master;      
CREATE LOGIN quartzuser with password = 'password';      
GO             

USE jama;      
EXEC ('CREATE SCHEMA quartz');      
GO             

USE jama;      
CREATE USER quartzuser for LOGIN quartzuser with DEFAULT_SCHEMA=quartz;      
GO             

EXEC sp_addrolemember N'db_owner', quartzuser;            
GO

Confirm that these actions were successful:

Script completed — Check the Query Execution results for errors.
Users created — Run the following SQL script in a new query window.
```
USE jama
SELECT * from master.sys.sql_logins
SELECT * from Jama.sys.sysusers
```
The results include jamauser, samluser, and oauthuser in the "Name" column of the result panes.

Users granted the DB_owner role — Run the following SQL script in a new query window.

USE jama
SELECT DP1.name AS DatabaseRoleName,
isnull (DP2.name, 'No members') AS DatabaseUserName
FROM sys.database_role_members AS DRM
RIGHT OUTER JOIN sys.database_principals AS DP1
ON DRM.role_principal_id = DP1.principal_id
LEFT OUTER JOIN sys.database_principals AS DP2
ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.type = 'R'
ORDER BY DP1.name;

The results show that db_owner role is granted to jamauser, samluser, and oauthuser.

Keep the database from locking users' accounts while they are logging in or working in Jama Connect (you must have db_owner permissions):
```
ALTER DATABASE jama SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;
```
Make sure the flag was successfully enabled:
```
SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name='jama';
```
If the returned value is 1, the flag is on.

In this section:

Jama Connect User Guide

Install and configure Microsoft SQL Server (KOTS)

Search results