PhenixID Documentation

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 will introduce a new internal database, that will be used by default. Version 4.0 also introduce the possibility to use 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 import 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

A few tools and files are needed for the export/import of current database.
Files needed to export the current database, sql script for creation of new database, script for import to new database and database drivers can be found in the attached file:

SQLImport.zip

Configure database

Place this file in the PAS installation folder (PhenixID/Server) and extract the files.
Path to JAVA will then work without any changes.
If the files are extracted in different folder, please see instructions for path to JAVA below.

Export

Versions of PAS before 4.0 use OrientDB as database.
An export of the database is done every 24h and placed in the folder:
/Server/export

If needed, a manual export can be done. This should be performed if new data has been added to the database since last automatic export.

Connection to the database is done with the file console.sh/console.bat, found in the bin folder. If files are not extracted in PAS installation folder, start by setting an absolute path to java, on line 31 for Linux and line 9 for Windows (example below), then run the file and connect to the database using one of the following commands:

connect plocal:"/<pasinstalldir>/data/databases/phenixid" admin pwd

connect remote:127.0.0.1/phenixid admin pwd

(first command connects directly to the file structure, second can be used if the PAS service is running)

When connected, use this command to export the database:

EXPORT DATABASE /tmp/<filename> -excludeALL -includeClass="TOKENS EVENT LOCKOUTS DEVICES"

NOTE: When export is done, exit out of the console.

Example, path to java:
JAVA=/opt/Phenixid/Server/jre/bin/java
set JAVA="D:\Program Files\PhenixID\Server\jre\bin\java"

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). On version 4.0.0/4.0.1, please replace the file "SQLConnector.class" according to instructions:

Changedatabasename.zip

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.

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.

Import database

Import the database with the respective command below.

NOTE: Delimiter for -cp is colon (:) on Linux and semicolon (;) on Windows.

HSQLDB (default):
java -cp c:\SQLImport\dataimport-1.0.jar;c:\SQLImport\hsqldb-2.5.0.jar com.phenixidentity.dataimport.Start c:\SQLImport\export.20201027T151625 jdbc:hsqldb:hsql://localhost:9001/phenixid org.hsqldb.jdbc.JDBCDriver phenixid <password>

MySQL:
java -cp c:\SQLImport\dataimport-1.0.jar;c:\SQLImport\mysql-connector-java-8.0.21.jar com.phenixidentity.dataimport.Start c:\SQLImport\export.20201027T151625 jdbc:mysql://<ipordns>:3306/phenixid com.mysql.jdbc.Driver phenixid <password>

MSSQL (SQL authentication):
java -cp c:\SQLImport\dataimport-1.0.jar;c:\SQLImport\mssql-jdbc-8.4.1.jre8.jar com.phenixidentity.dataimport.Start c:\SQLImport\export.20201027T151625 jdbc:sqlserver://<ipordns>:1433;databaseName=phenixid com.microsoft.sqlserver.jdbc.SQLServerDriver sa <password>

NOTE for MSSQL: If PAS is installed on Linux and the command is run from there, any semicolon (;) in the URL to the database must be escaped using backslash (\), like this example:
jdbc:sqlserver://<ipordns>:1433\;databaseName=phenixid

MSSQL (Integrated authentication):
java -cp c:\SQLImport\dataimport-1.0.jar;c:\SQLImport\mssql-jdbc-8.4.1.jre8.jar -Djava.library.path=C:\SQLImport com.phenixidentity.dataimport.Start c:\SQLImport\export.20201027T151625 jdbc:sqlserver://<ipordns>:1433;databaseName=phenixid;integratedSecurity=true; com.microsoft.sqlserver.jdbc.SQLServerDriver a b

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