Skip to main content

Celonis Product Documentation

MSSQL: Using Windows Authentication to connect

New data integration content available

You're currently viewing a data integration topic that has been replaced and will eventually be removed from our documentation.

For the latest data integration content, see: Data Integration.

You can use Windows authentication instead of an SQL user, with configurations made when the extractor is running on Windows and the desire user has started the JDBC extractor.

To use the Window Authentication to connect, make the following configurations:

1. Allow TCP/IP connections

You need to make sure that your MSSQL accepts TCP/IP connections.

2. Additional driver library

You need a specific library on the PATH environment variable on the Extractor server

  1. Download Microsoft JDBC Driver 12.X for SQL Server (i.e. sqljdbc_<version>_enu.tar.gz) from the Microsoft website: Download Microsoft JDBC Driver for SQL Server

  2. Unzip the file and go to following folder: sqljdbc_<version>\<language>\auth\x64

  3. Copy the sqljdbc_auth.dll into a folder which is defined in your PATH environment variable.

    This should be the absolute PATH of where you added the folder, for example: C:\Windows\System\mssql-jdbc_auth-12.4.2.x64.dll

3. Configure the service to be run by the desired user

The Extractor has to be run by the user that should be used to authenticate at the MSSQL Server instance. If you would like to run the Extractor as a Windows service, you need to make sure that the service is using the desired account.

22120364.png

For this, please go to the Services application on the Extractor server, find the service, most likely CelonisIBCDatabase (ID: celonis-ibc-database) and change the user from the local system account to the account that you want to use for authentication at the MSSQL server.

In the example on the left, the user j.doe of the domain CORP is used for this purpose.

4. Adjust the startup script to reference the correct JDBC driver

In order to start the extractor with the JDBC driver that has been downloaded in Step 2, the startup script needs to be adjusted in order to reference the correct driver.

This requires a custom JDBC driver, with further instructions provided here: How do I set up a database connection using a custom JDBC driver?.

Please ensure to reference the full path to the driver!

You need to specify the driver in the following way when running the Extractor via the command line:

java -Dloader.path=<path_to_driver> -jar <connector_file_name>.jar

And when running the extractor as a service you need to change the arguments line in the CelonisJDBCExtractor.xml file as follows:

<arguments>-Djava.io.tmpdir="%BASE%\temp"-Dloader.path=<path_to_driver> -jar connector-jdbc.jar</arguments>

Note

The DLL file used must match the version name of the jar (e.g. 12.2.4_mssql.jar =12.2.4_mssql.dll). The version name of the jar can be changed manually if needed.

5. Connection configuration in Celonis Platform

You need to specify an additional parameter when configuring the Connection. Either append the following to the JDBC URL (when using a custom JDBC string) or add it as an additional parameter:

IntegratedSecurity=true

Afterwards the user name and password input will need to be specified, but it will be ignored and Windows authentication will be used instead.