Database Setup

View product develop

The Docker images automatically keep the database schema up-to-date using Liquibase. Whenever you install the application or update it to a new version, the container updates the DB schema. On application downgrade, the changes are rolled back automatically (losing information specific for the new versions). Therefore, you only need to follow this documentation when you are installing and setting up the database from scratch.

Install Microsoft SQL Server

To get started, download and install the current version of MSSQL:

Microsoft SQL Server Downloads

Start the database and connect to it using your preferred database tool.

Tip: We use DataGrip by JetBrains for any database related tasks. It allows easy data editing for all popular database engines and contains a powerful SQL console for even the most complex database tasks.

Create User

Start by creating the powerauth user in the database and by setting the user a strong password.

Tip: You can generate a strong password locally on your computer using openssl rand -base64 12.

CREATE LOGIN powerauth WITH PASSWORD = '$PASSWORD$';
CREATE USER powerauth FOR LOGIN powerauth;

Create Database

Now, let’s create the powerauth database to which we will store the data.

CREATE DATABASE powerauth;

USE powerauth;

EXEC sp_addrolemember 'db_owner', 'powerauth';

You can assign more granular privileges instead of using the db_owner role, if required for security reasons.

MSSQL Server Snapshot Isolation

Enable the SNAPSHOT isolation to avoid deadlocks. You can enable the SNAPSHOT isolation mode using following query:

ALTER DATABASE [powerauth_database] SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE [powerauth_database] SET READ_COMMITTED_SNAPSHOT ON;

The SNAPSHOT transaction isolation level is enforced automatically by PowerAuth server when database sessions are started using following query:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

MSSQL Performance Setting

PowerAuth utilizes varchar data type as a primary key on several tables. The default behavior of JDBC and MSSQL 2019 is causing an implicit conversion when executing selects statements by these IDs - converting from nvarchar to varchar. We strongly recommend adding the following parameter to the database URL:

sendStringParametersAsUnicode=false

Based on your localization setting, you should store only the characters your DB can handle, typically the names of registrations and operations parameters.