Configure database

After upgrade, database needs to be configured and manual editing is required in file:

  • boot.json

Update of the file must be done prior to starting the system.
If external database should be used, creation of the database must be done prior to starting the system.
In clustered environments, external database with MySQL or MSSQL is mandatory.

Step by step document for upgrade can be found here:
Upgrade

NOTE: Make sure to have a backup of the file, before editing.

Overview

Version 4.0 of PAS introduced a new internal database, that is used by default. Version 4.0 also introduces the possibility to use an external database, where MySQL or MSSQL is supported. This option can be used if desired and is mandatory for installations where the database should be clustered.

This document will explain the steps to configure the database of choice and also migrate current data.

NOTE:
Depending on the choice of database, default reports might need to be adjusted.
The query used for the report can contain statements that are not supported.
More information can be found in this document (step 10):



Prerequisites

Migrate current data

Download our command line application for migrating data between different databases. More information about how to use it is included in the package.

The application can be downloaded from here: https://files.phenixid.se/s/CEnARs8sA28Z9PW

Configure database

Create database

HSQLDB will be created when PAS is started. 

For Mysql and MSSQL, use the "mssql_phenixid.sql" for MSSQL and "mysql_phenixid.sql" for MySQL to create the initial database structure. These files are included in the migration application package.

NOTE: When using MSSQL Integrated authentication, the command needs to be run with an account that has correct privileges to the database.

Configure database in boot.json

We will now add configuration for the database of choice.
In boot.json, set the mpl module according to the examples below.
NOTE: For MySQL and MSSQL, the parameter "url" will be dependent on the database of choice. This value should be provided by the person(s) responsible for the database configuration.
NOTE2: The default database name, "phenixd", can be changed on MySQL/MSSQL (NOT on internal HSQLDB).
To use a different database name than phenixid, on MySQL/MSSQL, set the parameter:
"catalog":"<databasename>"
in the mpl section of boot.json.


Example:

<p>"user": "<user_name>","
"password": "<password>",
"encryption.key": "<encryptionkey>",
"catalog":"phxprod",
"export_start": "15:00",</encryptionkey></password></user_name></p>
Click to copy

HSQLDB (default):

<p>{
	"name": "com.phenixidentity~phenix-store-mpl",
	"config": {
	   "user": "phenixid",
	   "password": "<password>",
	   "encryption.key": "<encryptionkey>",
	   "export_start": "15:00",
	   "driver_class": "org.hsqldb.jdbc.JDBCDriver",
	   "dataretention": "60",
	   "is_server": "true",
	   "server.address": "0.0.0.0",
	   "url": "jdbc:hsqldb:hsql://localhost:9001/phenixid"
	}
}</encryptionkey></password></p>
Click to copy

MySQL:

<p>{
	"name": "com.phenixidentity~phenix-store-mpl",
	"config": {
	   "user": "<user_name>",
	   "password": "<password>",
	   "encryption.key": "<encryptionkey>",
	   "export_start": "15:00",
	   "driver_class": "com.mysql.cj.jdbc.Driver",
	   "dataretention": "60",
	   "is_server": "false",
	   "url": "jdbc:mysql://172.24.0.3:3306/phenixid"
	}
}</encryptionkey></password></user_name></p>
Click to copy

MSSQL (SQL authentication):

<p>{
	"name": "com.phenixidentity~phenix-store-mpl",
	"config": {
	   "user": "<user_name>",
	   "password": "<password>",
	   "encryption.key": "<encryptionkey>",
	   "export_start": "15:00",
	   "driver_class": "com.microsoft.sqlserver.jdbc.SQLServerDriver",
	   "dataretention": "60",
	   "is_server": "false",
	   "url": "jdbc:sqlserver://172.24.0.3:1433;instance=sqlexpress;databaseName=phenixid"
	}
}</encryptionkey></password></user_name></p>
Click to copy

MSSQL (Integrated authentication):

<p>{
	"name": "com.phenixidentity~phenix-store-mpl",
	"config": {
	   "user": "dummy",
	   "password": "dummy",
	   "encryption.key": "<encryptionkey>",
	   "export_start": "15:00",
	   "driver_class": "com.microsoft.sqlserver.jdbc.SQLServerDriver",
	   "dataretention": "60",
	   "is_server": "false",
	   "url": "jdbc:sqlserver://172.24.0.3:1433;instance=sqlexpress;databaseName=phenixid;integratedSecurity=true;"
	}
}</encryptionkey></p>
Click to copy

NOTE: When using integrated authentication, the PAS windows service has to be launched with the same  account used for the database connection. This account also needs privilege to right to disk.