Differences
This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision | ||
tutorial:adm:mssql_database_support [2019/01/29 08:01] kotisovam [MsSQL: Run CzechIdM with MsSQL database] |
tutorial:adm:mssql_database_support [2021/03/30 12:13] (current) doischert [Develop CzechIdM with MsSQL and a docker] |
||
---|---|---|---|
Line 1: | Line 1: | ||
====== MsSQL: Run CzechIdM with MsSQL database ====== | ====== MsSQL: Run CzechIdM with MsSQL database ====== | ||
+ | {{tag> sqlserver sql mssql install database supported drivers}} | ||
The tutorial describes how to run CzechIdM with a Microsoft SQL Server. When following the tutorials [[tutorial: | The tutorial describes how to run CzechIdM with a Microsoft SQL Server. When following the tutorials [[tutorial: | ||
Line 5: | Line 6: | ||
<note important> | <note important> | ||
+ | <note important> | ||
+ | |||
+ | ----- | ||
+ | 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 ===== | ===== Setup MsSQL with CzechIdM ===== | ||
==== Create database ==== | ==== Create database ==== | ||
- | There is script for setup database for CzechIdM. Please follow these instructions. If you skip some query CzechIdM | + | There is a script for setting up a database for CzechIdM. Please follow these instructions. If you omit any of the statements, |
<code sql> | <code sql> | ||
-- create database | -- create database | ||
Line 17: | Line 27: | ||
ALTER DATABASE bcv_idm_storage SET READ_COMMITTED_SNAPSHOT ON; | ALTER DATABASE bcv_idm_storage SET READ_COMMITTED_SNAPSHOT ON; | ||
GO | GO | ||
- | -- allow snapshot isolation - setting | + | -- allow snapshot isolation - setting |
ALTER DATABASE bcv_idm_storage SET ALLOW_SNAPSHOT_ISOLATION ON; | ALTER DATABASE bcv_idm_storage SET ALLOW_SNAPSHOT_ISOLATION ON; | ||
GO | GO | ||
Line 23: | Line 33: | ||
USE bcv_idm_storage; | USE bcv_idm_storage; | ||
GO | GO | ||
- | -- create login, login is used for connect | + | -- create login, login is used for connecting |
CREATE LOGIN idmadmin WITH PASSWORD = ' | CREATE LOGIN idmadmin WITH PASSWORD = ' | ||
GO | GO | ||
- | -- create user, user will be used for connect to database | + | -- create |
CREATE USER idmadmin FOR LOGIN idmadmin; | CREATE USER idmadmin FOR LOGIN idmadmin; | ||
GO | GO | ||
- | -- create schema | + | -- create |
CREATE SCHEMA bcv_idm_storage AUTHORIZATION idmadmin; | CREATE SCHEMA bcv_idm_storage AUTHORIZATION idmadmin; | ||
GO | GO | ||
- | -- set default schema | + | -- set a default schema |
ALTER USER idmadmin WITH DEFAULT_SCHEMA = bcv_idm_storage; | ALTER USER idmadmin WITH DEFAULT_SCHEMA = bcv_idm_storage; | ||
GO | GO | ||
- | -- grant permision | + | -- grant permission |
+ | -- sometimes this is not needed when the user is owner of the schema - then you get ' | ||
GRANT ALTER, CONTROL, CREATE SEQUENCE, DELETE, EXECUTE, INSERT, REFERENCES, SELECT, TAKE OWNERSHIP, UPDATE, VIEW CHANGE TRACKING, VIEW DEFINITION ON SCHEMA:: | GRANT ALTER, CONTROL, CREATE SEQUENCE, DELETE, EXECUTE, INSERT, REFERENCES, SELECT, TAKE OWNERSHIP, UPDATE, VIEW CHANGE TRACKING, VIEW DEFINITION ON SCHEMA:: | ||
GO | GO | ||
Line 46: | Line 57: | ||
</ | </ | ||
- | ==== Download JDBC driver ==== | + | <note tip> |
- | Please download JDBC driver for MsSQL. For example: [[http:// | + | If you use a domain user to connect to the database, use this syntax: '' |
- | The driver | + | If your domain user has a very long username (e.g. " |
+ | </ | ||
- | ==== Add connection to property file (application.properties) | + | ==== Download a JDBC driver |
+ | Please download a JDBC driver for MsSQL. For example: [[https:// | ||
- | Is required add correct | + | The driver must be placed for example into */ |
+ | |||
+ | ==== Choose the type of authentication and set connection properties (application.properties) ==== | ||
+ | |||
+ | The configuration depends on the type of authentication, | ||
+ | * SQL Server Authentication - this can be used with local DB accounts | ||
+ | * Windows Integrated Authentication - this can be used with domain user accounts (who have granted permissions to access the DB) when CzechIdM runs on Windows. | ||
+ | |||
+ | === SQL Server Authentication === | ||
+ | |||
+ | The correct | ||
Example settings: | Example settings: | ||
Line 62: | Line 85: | ||
spring.datasource.password=idmadmin | spring.datasource.password=idmadmin | ||
spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver | spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver | ||
- | spring.datasource.testOnBorrow=true | + | spring.datasource.test-on-borrow=true |
spring.datasource.validationQuery=SELECT 1 | spring.datasource.validationQuery=SELECT 1 | ||
</ | </ | ||
- | Example use JDBC driver [[http:// | + | An example is valid for JDBC driver |
- | ==== Setup scheduler | + | If you need to use a domain user, set the property username like this: '' |
- | For full example please visit github page with [[https:// | + | |
- | There is two propeties that is different: | + | === Windows Integrated Authentication === |
+ | |||
+ | This type of authentication can be used for a domain user account. It requires a bit more configuration, | ||
+ | |||
+ | This authentication is not supported by Microsoft JDBC drivers prior to the version 6. | ||
+ | |||
+ | Steps to setup: | ||
+ | |||
+ | 1. Set the **Apache Tomcat8 service** to **Log on as** the domain user which you will use to connect to the database (instead of "Local Service" | ||
+ | |||
+ | 2. Grant write access to the Tomcat directory (C:\Program Files\Apache Software Foundation\Tomcat 8.5) to the domain user (or simply Full control). | ||
+ | |||
+ | 3. Extract the file '' | ||
+ | |||
+ | 4. Put '' | ||
+ | |||
+ | 5. Add the option '' | ||
+ | |||
+ | 6. Set the '' | ||
+ | |||
+ | <code properties> | ||
+ | spring.datasource.url=jdbc: | ||
+ | spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver | ||
+ | spring.datasource.test-on-borrow=true | ||
+ | spring.datasource.validationQuery=SELECT 1 | ||
+ | </ | ||
+ | The example is valid for SQL server running on the server " | ||
+ | |||
+ | 7. Add SQL server certificate to Java truststore (FIXME how?). Workaround: add property '' | ||
+ | </ | ||
+ | |||
+ | 8. Finally, restart the Apache Tomcat8 service so all changes take place. | ||
+ | |||
+ | === Windows Authentication with NTLM === | ||
+ | |||
+ | If you need to use Windows Authentication but can't use the integrated authentication as above (e.g. you are not running IdM on Windows), it's possible to use [[https:// | ||
+ | |||
+ | Example properties: | ||
+ | <code properties> | ||
+ | spring.datasource.url=jdbc: | ||
+ | spring.datasource.username=someserviceuser | ||
+ | spring.datasource.password=somepassword | ||
+ | spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver | ||
+ | spring.datasource.test-on-borrow=true | ||
+ | spring.datasource.validationQuery=SELECT 1 | ||
+ | </ | ||
+ | |||
+ | The example is valid for SQL server running on the server " | ||
+ | |||
+ | ==== Scheduler setup (quartz.properties) ==== | ||
+ | For full example please visit the github page with [[https:// | ||
+ | |||
+ | There are two propeties that are different: | ||
<code properties> | <code properties> | ||
org.quartz.jobStore.driverDelegateClass=org.quartz.impl.jdbcjobstore.MSSQLDelegate | org.quartz.jobStore.driverDelegateClass=org.quartz.impl.jdbcjobstore.MSSQLDelegate | ||
Line 79: | Line 153: | ||
===== Supported version of MsSQL ===== | ===== Supported version of MsSQL ===== | ||
* **14.0** - 2017 SQL Server 2017 | * **14.0** - 2017 SQL Server 2017 | ||
+ | * Tested version: 14.0.3294.2 | ||
+ | * Unsupported version: 14.0.1000.169 - contains a bug, which causes failure of the task DeleteExecutedEventTaskExecutor with the following error: '' | ||
Line 85: | Line 161: | ||
* [[http:// | * [[http:// | ||
* [[http:// | * [[http:// | ||
+ | * [[https:// | ||
* *Not fully tested* [[https:// | * *Not fully tested* [[https:// | ||
- | ===== Not Supported | + | ===== Not supported |
* [[https:// | * [[https:// | ||
- | ===== Develop CzechIdM with MsSQL and Docker | + | ===== Develop CzechIdM with MsSQL and a docker |
+ | Beware this is recommended only for develop. | ||
- | Run lasted MsSQL database (change SA-PASSWORD): | + | Run lasted MsSQL database (change |
$ docker run --name=test-mssql -e ' | $ docker run --name=test-mssql -e ' | ||
- | Copy initial script (init script is described | + | Copy the initial script (init script is described |
$ docker cp import.sql test-mssql:/ | $ docker cp import.sql test-mssql:/ | ||
- | Run init script in docker (change SA-PASSWORD): | + | Run the init script in the docker (change SA-PASSWORD): |
$ docker exec test-mssql / | $ docker exec test-mssql / | ||
+ | ==== Use docker-compose ==== | ||
+ | You can also use the following docker-compose.yml file. The advantage is that it uses persistent volumes and docker-compose cleans after itself better. Copy and edit (if needed) the code below to a file called ' | ||
+ | |||
+ | < | ||
+ | version: " | ||
+ | services: | ||
+ | sql-server-db: | ||
+ | container_name: | ||
+ | image: microsoft/ | ||
+ | ports: | ||
+ | - " | ||
+ | environment: | ||
+ | SA_PASSWORD: | ||
+ | ACCEPT_EULA: | ||
+ | MSSQL_BACKUP_DIR: | ||
+ | MSSQL_DATA_DIR: | ||
+ | MSSQL_LOG_DIR: | ||
+ | volumes: | ||
+ | - ' | ||
+ | - ' | ||
+ | volumes: | ||
+ | systemdbs: | ||
+ | userdbs: | ||
+ | </ | ||
+ | |||
+ | Then, in the same directory, use the command `docker-compose up` to start the database. | ||
+ | ===== Troubleshooting ===== | ||
+ | |||
+ | ==== Increase log level ==== | ||
+ | |||
+ | For debugging the problems when connecting to SQL Server database from CzechIdM, it's useful to turn on the Debug mode of the SQL Server JDBC Driver. Set C:\Program Files\Apache Software Foundation\Tomcat 8.5\conf\logging.properties like this: | ||
+ | |||
+ | <code properties> | ||
+ | 1.catalina.out.org.apache.juli.AsyncFileHandler.level = FINEST (instead of the original INFO) | ||
+ | |||
+ | com.microsoft.sqlserver.jdbc.level=FINEST | ||
+ | </ | ||
+ | After this, the file C:\Program Files\Apache Software Foundation\Tomcat 8.5\conf\tomcat.log shows detailed information about processing the different connection parameters and its results. | ||
+ | |||
+ | Make sure to change the settings back after you finish debugging. It generates **really big ** log files. | ||
+ | |||
+ | ==== Event Viewer ==== | ||
+ | |||
+ | If you have access to the Event Viewer on the server running the SQL Server, you will see the connection attempts under Windows Logs -> Application. The log level is Information even for unsuccessful atempts. You can see e.g.: | ||
+ | * if SQL server knows the account, which is used for connecting | ||
+ | * which type of authentication is used - e.g. if you use a domain account ("NT account" | ||
+ | |||
+ | ==== Instances and ports ==== | ||
+ | |||
+ | Usually, you don't need to specify the port when connecting to SQL Server. By default the driver calls built-in [[https:// | ||
+ | |||
+ | The default SQL Server port (1433) doesn' | ||