Skip to main content

Celonis Product Documentation

MSSQL: How do I use Windows Authentication to connect?

If you wish to use Windows authentication instead of a SQL User, you need to make certain configurations. Please keep in mind that this is only possible if the Extractor is running on Windows and the desired user has started the JDBC Extractor.

1. Allow TCP/IP connections

You need to make sure that your MSSQL accepting 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 6.0 for SQL Server (i.e. sqljdbc_<version>_enu.tar.gz) from this website: https://www.microsoft.com/en-us/download/details.aspx?id=11774

  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

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:

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 thecommand 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>
5. Connection configuration in EMS

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.