Differences

This shows you the differences between two versions of the page.

Link to this comparison view

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://clojars.org/repo/com/microsoft/sqlserver/sqljdbc4/4.0/sqljdbc4-4.0.jar|http://clojars.org/repo/com/microsoft/sqlserver/sqljdbc4/4.0/sqljdbc4-4.0.jar]], or see the section other supported drivers. +Please download a JDBC driver for MsSQL. For example: [[https://docs.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-2017|driver from official Microsoft Docs]], or [[http://clojars.org/repo/com/microsoft/sqlserver/sqljdbc4/4.0/sqljdbc4-4.0.jar|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 driver must be placed for example into */opt/tomcat/current/lib/* tomcat external classpath, or libraries of tomcat. If CzechIdM runs on Windows, the path is *C:\Program Files\Apache Software Foundation\Tomcat 8.5\lib*.
  
-==== Add connection to property file (application.properties) ====+==== Choose the type of authentication and set connection properties (application.properties) ==== 
 + 
 +The configuration depends on the type of authentication, which you will use for connecting to the CzechIdM database. There are several options: 
 +  * 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://github.com/bcvsolutions/CzechIdMng/blob/develop/Realization/backend/app/src/main/resources/application-dev-mssql.properties|developer profile for mssql]]. The correct data source url and password are required. To see an example, visit the github page with [[https://github.com/bcvsolutions/CzechIdMng/blob/develop/Realization/backend/app/src/main/resources/application-dev-mssql.properties|developer profile for mssql]].
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
 </code> </code>
  
-An example of using a JDBC driver [[http://clojars.org/repo/com/microsoft/sqlserver/sqljdbc4/4.0/sqljdbc4-4.0.jar|Sqljdbc4 4.0]], if you want to use another driver please setup the correct url.+An example is valid for JDBC driver [[https://docs.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-2017|from official Microsoft Docs]] or [[http://clojars.org/repo/com/microsoft/sqlserver/sqljdbc4/4.0/sqljdbc4-4.0.jar|Sqljdbc4 4.0]], if you want to use another driver please setup the correct url. 
 + 
 +FIXME Not fully tested. If you need to use a domain user, set the property username like this: ''DOMAIN\\idmamin''. Java will translate the double backslash to a single backslash. If you use only single backslash, it will not be sent to the SQL Server at all and the SQL Server will not find the user. Also, if you have too long login, use its shorter version (sAMAccountName). 
 + 
 +=== Windows Integrated Authentication === 
 + 
 +This type of authentication can be used for a domain user account. It requires a bit more configuration, but the main advantage is that the password of the domain user is not directly written in the application properties, so it's more secure and recommended by Microsoft. [[https://docs.microsoft.com/en-us/sql/connect/jdbc/building-the-connection-url?view=sql-server-2017#Connectingintegrated|Connecting with integrated authentication On Windows]]. 
 + 
 +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" recommended by the [[tutorial:adm:server_preparation_win#tomcat|Server preparation tutorial]]). 
 + 
 +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 ''sqljdbc\_8.2\enu/auth/x86/mssql-jdbc_auth-8.2.2.x86.dll'' from the [[https://docs.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-2017|downloaded Microsoft driver]]. (Use the same package which you already downloaded when downloading JDBC driver in the previous part.) 
 + 
 +4. Put ''mssql-jdbc\_auth-8.2.2.x86.dll'' to ''C:\CzechIdM\lib''
 + 
 +5. Add the option ''-Djava.library.path=C:\CzechIdM\lib'' to the **Tomcat Java Options**. To do it, run the Monitor Tomcat application from the Start menu (or run Tomcat8w.exe from the Tomcat bin directory - C:\Program Files\Apache Software Foundation\Tomcat 8.5\bin) -> Java -> Java Options. 
 + 
 +6. Set the ''C:\CzechIdM\etc\application-production.properties'' to use Integration Authentication (the property integratedSecurity). 
 + 
 +<code properties> 
 +spring.datasource.url=jdbc:sqlserver://xsqlserver123;databaseName=bcv_idm_storage;instanceName=CZECHIDM;integratedSecurity=true 
 +spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver 
 +spring.datasource.test-on-borrow=true 
 +spring.datasource.validationQuery=SELECT 1 
 +</code> 
 +The example is valid for SQL server running on the server "xsqlserver123", instance "CZECHIDM", database "bcv\_idm\_storage". Note that the parameters username and password are omitted; this is required for this type of authentication. 
 + 
 +Finally, restart the Apache Tomcat8 service so all changes take place. 
  
 ==== Scheduler setup (quartz.properties) ==== ==== Scheduler setup (quartz.properties) ====
Line 104: Line 143:
   * [[http://clojars.org/repo/com/microsoft/sqlserver/sqljdbc4/4.0/sqljdbc4-4.0.jar|Sqljdbc4 4.0]],   * [[http://clojars.org/repo/com/microsoft/sqlserver/sqljdbc4/4.0/sqljdbc4-4.0.jar|Sqljdbc4 4.0]],
   * [[http://central.maven.org/maven2/com/microsoft/sqlserver/mssql-jdbc/6.4.0.jre8/mssql-jdbc-6.4.0.jre8.jar|Microsoft JDBC Driver For SQL Server 6.4.0.jre8]],   * [[http://central.maven.org/maven2/com/microsoft/sqlserver/mssql-jdbc/6.4.0.jre8/mssql-jdbc-6.4.0.jre8.jar|Microsoft JDBC Driver For SQL Server 6.4.0.jre8]],
 +  * [[https://docs.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-2017|Microsoft JDBC Driver for SQL Server - official Microsoft Docs]] - tested version 8.2.2
   * *Not fully tested* [[https://sourceforge.net/projects/jtds/files/jtds/1.3.1/jtds-1.3.1-dist.zip/download|jTDS - SQL Server and Sybase JDBC driver]].   * *Not fully tested* [[https://sourceforge.net/projects/jtds/files/jtds/1.3.1/jtds-1.3.1-dist.zip/download|jTDS - SQL Server and Sybase JDBC driver]].
  
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 /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P <SA-PASSWORD> -d master -i /import.sql    $ docker exec test-mssql /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P <SA-PASSWORD> -d master -i /import.sql
 +
 +
 +===== 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
 +</code>
 +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") in combination with SQL Server authentication made for local accounts.
  
  
  • by doischert