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		 Previous revision
tutorial:adm:mssql_database_support [2020/06/24 14:59] – integrated authentication apeterovatutorial:adm:mssql_database_support [2021/03/30 12:13] (current) – [Develop CzechIdM with MsSQL and a docker] doischert
Line 91: Line 91:
 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. 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).+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 === === Windows Integrated Authentication ===
Line 121: Line 121:
 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. 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.+7. Add SQL server certificate to Java truststore (FIXME how?). Workaround: add property ''trustServerCertificate=true'' to the JDBC URL above. 
 +</note>
  
 +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://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) ==== ==== Scheduler setup (quartz.properties) ====
Line 162: Line 180:
    $ 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
  
 +==== 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 ===== ===== Troubleshooting =====
  
Line 184: Line 227:
   * 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.   * 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.
  
  • by apeterova