Connecting to Microsoft SQL
You can connect your Microsoft SQL server to the Celonis Platform using either an SQL user or with Windows authentication.
Creating a connection between Microsoft SQL and the Celonis Platform using Windows authentication
To use Windows authentication, you need to perform additional configuration steps, including downloading an additional JDBC driver from Microsoft.
To configure your Windows authentication for the connection between Microsoft SQL and the Celonis Platform:
Your Microsoft SQL server must accept TCP and IP connections before you can continue. To learn how to do this, see the official Microsoft document: Microsoft Learn - SQL Server.
You now need a specific library on the PATH environment variable on the extractor server:
Download the latest Microsoft JDBC Driver for SQL Server (i.e. sqljdbc_<version>_enu.tar.gz) from the Microsoft website: Micrsoft Learn - Download JDBC driver
Unzip the file and go to following folder: sqljdbc_<version>\<language>\auth\x64
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
The extractor has to be run by the user that should be used to authenticate at the Microsoft SQL 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.
For this, navigate 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.
As this connection requires the use of a custom JDBC driver, you need to configure this driver.
For more information, see: Using custom 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.
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>
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.
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.
Creating a connection between Microsoft SQL and the Celonis Platform using an SQL user
To use an SQL user, you need access to a user who has the necessary permissions to extract tables from the database. For more information, see the official Microsoft documentation: Microsoft Learn - Create a database user
After configuring your SQL user, you can create the connection between Micrsoft SQL and the Celonis Platform from your data pool diagram:
Click Data Connections.
Click Add Data Connection and select Connect to Data Source.
Select Cloud - Database.
Configure the following connection details:
Name: An internal reference for this database connection.
Database type: Select Microsoft SQL (native).
Host: The database server name or IP address of the database server.
Port: Set to 1433 by default.
Username and password: Add the credentials for the user profile that has extract access to the Microsoft SQL tables.
Click Test Connection and correct any highlighted issues.
Click Save.
The connection between your Microsoft SQL server and the Celonis Platform is establised. You can manage this connection at any time by clicking Options:
