Differences
This shows you the differences between two versions of the page.
Both sides previous revision Previous revision | Next revision Both sides next revision | ||
tutorial:adm:mssql_database_support [2020/06/10 16:53] apeterova domain user, permissions |
tutorial:adm:mssql_database_support [2020/06/24 14:59] apeterova integrated authentication |
||
---|---|---|---|
Line 64: | Line 64: | ||
==== Download a JDBC driver ==== | ==== Download a JDBC driver ==== | ||
- | Please download a JDBC driver for MsSQL. For example: [[http:// | + | Please download a JDBC driver for MsSQL. For example: |
- | The driver must be placed for example into */ | + | The driver must be placed for example into */ |
- | ==== Add connection | + | ==== Choose the type of authentication and set connection |
+ | |||
+ | 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 data source url and password are required. To see an example, visit the github page with [[https:// | The correct data source url and password are required. To see an example, visit the github page with [[https:// | ||
Line 79: | 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 | ||
</ | </ | ||
- | An example | + | An example |
+ | |||
+ | FIXME Not fully tested. If you need to use a domain user, set the property username like this: '' | ||
+ | |||
+ | === 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 " | ||
+ | |||
+ | Finally, restart the Apache Tomcat8 service so all changes take place. | ||
==== Scheduler setup (quartz.properties) ==== | ==== Scheduler setup (quartz.properties) ==== | ||
Line 104: | Line 143: | ||
* [[http:// | * [[http:// | ||
* [[http:// | * [[http:// | ||
+ | * [[https:// | ||
* *Not fully tested* [[https:// | * *Not fully tested* [[https:// | ||
Line 121: | Line 161: | ||
Run the init script in the docker (change SA-PASSWORD): | Run the init script in the docker (change SA-PASSWORD): | ||
$ docker exec test-mssql / | $ docker exec test-mssql / | ||
+ | |||
+ | |||
+ | ===== 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" | ||