MsSQL: Run CzechIdM with MsSQL database

The tutorial describe how to run CzechIdM with Microsoft SQL Server. Please when you follow tutorial Server preparation and CzechIdM installation skip all settings and setup which are related to database.

Tutotial doesn't describe how to install MsSQL database. For development you can install MsSQL in docker see section MsSQL in docker.

Create database

There is script for setup database for CzechIdM. Please follow these instructions. If you skip some query CzechIdM should not work correctly.

-- create database
CREATE DATABASE bcv_idm_storage;
GO
-- set read committed snapshot
ALTER DATABASE bcv_idm_storage SET READ_COMMITTED_SNAPSHOT ON;
GO
-- allow snapshot isolation - setting for lock escalation
ALTER DATABASE bcv_idm_storage SET ALLOW_SNAPSHOT_ISOLATION ON;
GO
-- set active database
USE bcv_idm_storage;
GO
-- create login, login is used for connect to server, check policy is for development prupose (password idmadmin)
CREATE LOGIN idmadmin WITH PASSWORD = 'idmadmin', check_policy = off;
GO
-- create user, user will be used for connect to database
CREATE USER idmadmin FOR LOGIN idmadmin; 
GO
-- create schema
CREATE SCHEMA bcv_idm_storage AUTHORIZATION idmadmin;
GO
-- set default schema
ALTER USER idmadmin WITH DEFAULT_SCHEMA = bcv_idm_storage;
GO
-- grant permision for schema (grant all is deprecated)
GRANT ALTER, CONTROL, CREATE SEQUENCE, DELETE, EXECUTE, INSERT, REFERENCES, SELECT, TAKE OWNERSHIP, UPDATE, VIEW CHANGE TRACKING, VIEW DEFINITION ON SCHEMA::bcv_idm_storage TO idmadmin;
GO
-- grant create table to idmadmin
GRANT CREATE TABLE TO idmadmin;
GO
-- grant create view to idmadmin
GRANT CREATE VIEW TO idmadmin;
GO

Download JDBC driver

Please download JDBC driver for MsSQL. For example: http://clojars.org/repo/com/microsoft/sqlserver/sqljdbc4/4.0/sqljdbc4-4.0.jar, or see section another supported drivers.

The driver you must be placed for example into /opt/tomcat/current/lib/ tomcat external classpath, or libraries of tomcat.

Add connection to property file (application.properties)

Is required add correct datasource url ans password. For exemple visit github page with developer profile for mssql.

Example settings:

spring.datasource.url=jdbc:sqlserver://localhost;databaseName=bcv_idm_storage
spring.datasource.username=idmadmin
spring.datasource.password=idmadmin
spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.testOnBorrow=true
spring.datasource.validationQuery=SELECT 1

Example use JDBC driver Sqljdbc4 4.0, if you want use another driver please setup correct url.

Setup scheduler (quartz.properties)

For full example please visit github page with developer profile.

There is two propeties that is different:

org.quartz.jobStore.driverDelegateClass=org.quartz.impl.jdbcjobstore.MSSQLDelegate
org.quartz.jobStore.tablePrefix=QRTZ_
  • 14.0 - 2017 SQL Server 2017

Run lasted MsSQL database (change SA-PASSWORD):

 $ docker run --name=test-mssql -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=<SA-PASSWORD>' -p 1433:1433 -d microsoft/mssql-server-linux:latest

Copy initial script (init script is described upper, in example has script file name import.sql)

 $ docker cp import.sql test-mssql:/import.sql

Run init script in docker (change SA-PASSWORD):

 $ docker exec test-mssql /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P <SA-PASSWORD> -d master -i /import.sql