Differences

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

--- tutorial:adm:mssql_database_support [2019/05/06 11:53] – [Develop CzechIdM with MsSQL and a docker] kopro
+++ tutorial:adm:mssql_database_support [2021/03/30 12:13] (current) – [Develop CzechIdM with MsSQL and a docker] doischert
@@ Line 46: / Line 46: @@
 GO
 -- grant permission for the schema (grant all is deprecated)
+-- sometimes this is not needed when the user is owner of the schema - then you get 'Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.' which is OK
 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
@@ Line 55: / Line 56: @@
 GO
 </code>
+<note tip>
+If you use a domain user to connect to the database, use this syntax: ''CREATE SCHEMA bcv\_idm\_storage AUTHORIZATION "DOMAIN\idmadmin;"''.
+If your domain user has a very long username (e.g. "sql\_dbinstance'_czechidm", then you must supply only the shorter sAMAccountName, typically first 20 characters - e.g."sql\_dbinstance\_czech").
+</note>
 ==== 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. If CzechIdM runs on Windows, the path is *C:\Program Files\Apache Software Foundation\Tomcat 8.5\lib*.
+==== Choose the type of authentication and set connection properties (application.properties) ====
-The driver must be placed for example into */opt/tomcat/current/lib/* tomcat external classpath, or libraries of tomcat.
+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.
-==== Add connection to property file (application.properties) ====
+=== 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]].
@@ Line 72: / Line 85: @@
 spring.datasource.password=idmadmin
 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
 </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.
+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). Also you should probably set ''authentication=ActiveDirectoryIntegrated'', but it hasn't been truly tested yet. FIXME
+=== 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:
+. 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]]).
+. Grant write access to the Tomcat directory (C:\Program Files\Apache Software Foundation\Tomcat 8.5) to the domain user (or simply Full control).
+. 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.)
+. Put ''mssql-jdbc\_auth-8.2.2.x86.dll'' to ''C:\CzechIdM\lib''.
+. 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.
+. 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.
+. Add SQL server certificate to Java truststore (FIXME how?). Workaround: add property ''trustServerCertificate=true'' to the JDBC URL above.
+</note>
+. 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://docs.microsoft.com/en-us/sql/connect/jdbc/using-ntlm-authentication-to-connect-to-sql-server?view=sql-server-ver15|NTLM authentication]]. You will explicitly set username and password.
+Example properties:
+<code properties>
+spring.datasource.url=jdbc:sqlserver://xsqlserver123\CZECHIDM:1433;databaseName=bcv_idm_storage;integratedSecurity=true;authenticationScheme=NTLM;domain=yourdomain.tld
+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
+</code>
+The example is valid for SQL server running on the server "xsqlserver123", instance "CZECHIDM", database "bcv\_idm\_storage", domain "yourdomain.tld". Note that you don't specify the domain in the username.
 ==== Scheduler setup (quartz.properties) ====
@@ Line 89: / Line 153: @@
 ===== Supported version of MsSQL =====
   * **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: ''SQL Error: 21, SQLState: S0001. Warning: Fatal error 605 occurred'', event viewer: ''Attempt to fetch logical page (1:604718) in database 5 failed. It belongs to allocation unit 72057594065387520 not to 72057594089439232.''
@@ Line 95: / Line 161: @@
   * [[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]],
+  * [[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]].
@@ Line 113: / Line 180: @@
    $ docker exec test-mssql /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P <SA-PASSWORD> -d master -i /import.sql
+==== 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 'docker-compose.yml':
+<code>
+version: "3.2"
+services:
+  sql-server-db:
+    container_name: sql-server-db
+    image: microsoft/mssql-server-linux:2017-latest
+    ports:
+      - "1433:1433"
+    environment:
+      SA_PASSWORD: "Password123456"
+      ACCEPT_EULA: "Y"
+      MSSQL_BACKUP_DIR: "/var/opt/sqlserver"
+      MSSQL_DATA_DIR: "/var/opt/sqlserver"
+      MSSQL_LOG_DIR: "/var/opt/sqlserver"
+    volumes:
+      - 'systemdbs:/var/opt/mssql'
+      - 'userdbs:/var/opt/sqlserver'
+volumes:
+  systemdbs:
+  userdbs:
+</code>
+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>
+.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.
+==== 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://docs.microsoft.com/en-us/sql/tools/configuration-manager/sql-server-browser-service?view=sql-server-ver15|SQL Server Browser service]] and obtains the dynamic port, where the DB instance runs. This works also when there is only a default instance.
+The default SQL Server port (1433) doesn't need to be accessible through the network, so don't use it in the connection URL if you don't need to.