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.

There is a script for setting up a database for CzechIdM. Please follow these instructions. If you omit any of the statements, CzechIdM might not work properly.

-- 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 up a 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 connecting to the server, check policy is for development purposes (password idmadmin)
CREATE LOGIN idmadmin WITH PASSWORD = 'idmadmin', check_policy = off;
GO
-- create a user, this user will be used to connect to the database
CREATE USER idmadmin FOR LOGIN idmadmin; 
GO
-- create a schema
CREATE SCHEMA bcv_idm_storage AUTHORIZATION idmadmin;
GO
-- set a default schema
ALTER USER idmadmin WITH DEFAULT_SCHEMA = bcv_idm_storage;
GO
-- grant permission for the 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

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

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

The correct data source url and password are required. To see an example, visit the 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

An example of using a JDBC driver Sqljdbc4 4.0, if you want to use another driver please setup the correct url.

For full example please visit the github page with developer profile.

There are two propeties that are different:

org.quartz.jobStore.driverDelegateClass=org.quartz.impl.jdbcjobstore.MSSQLDelegate
org.quartz.jobStore.tablePrefix=QRTZ_

• 14.0 - 2017 SQL Server 2017

• Sqljdbc4 4.0,
• Microsoft JDBC Driver For SQL Server 6.4.0.jre8,
• *Not fully tested* jTDS - SQL Server and Sybase JDBC driver.

• Sqljdbc4 4.0.0 (this is version 4.0.0 not 4.0)

Run lasted MsSQL database (change <SA-PASSWORD> with your 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 the initial script (init script is described above, in our example the name of the script file is import.sql)

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

Run the init script in the 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