Book Creator
 Add this page to your book
Book Creator
 Remove this page from your book  

 Manage book(0 page(s))
 Help

This is an old revision of the document!

MsSQL: Run CzechIdM with MsSQL database

The tutorial describes how to run CzechIdM with a Microsoft SQL Server. When following the tutorials Server preparation and CzechIdM installation please do skip all the settings and setup related to database.

The tutorial doesn't describe how to install an MsSQL database. For development you can install MsSQL in a docker, see the section MsSQL in a docker.

Setup MsSQL with CzechIdM

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_

Supported version of MsSQL

  • 14.0 - 2017 SQL Server 2017

Supported drivers

Not Supported drivers

Develop CzechIdM with MsSQL and Docker

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
  • by kotisovam