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.
ALTER DATABASE bcv\_idm\_storage SET READ\_COMMITTED\_SNAPSHOT ON;
ALTER DATABASE bcv\_idm\_storage SET ALLOW\_SNAPSHOT\_ISOLATION ON;
Otherwise you risk deadlock on database.
Setup MsSQL with CzechIdM
Create 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
Download a JDBC driver
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.
Add connection to property file (application.properties)
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.
Scheduler setup (quartz.properties)
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_
Supported version of MsSQL
- 14.0 - 2017 SQL Server 2017
Supported drivers
- *Not fully tested* jTDS - SQL Server and Sybase JDBC driver.
Not supported drivers
- Sqljdbc4 4.0.0 (this is version 4.0.0 not 4.0)
Develop CzechIdM with MsSQL and a 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 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