Skip to main content

Connecting to Microsoft SQL Server (extractor)

Important

Any references to third-party products or services do not constitute Celonis Product Documentation nor do they create any contractual obligations. This material is for informational purposes only and is subject to change without notice.

Celonis does not warrant the availability, accuracy, reliability, completeness, or usefulness of any information regarding the subject of third-party services or systems.

The Celonis Microsoft SQL Server extractor allows you to transfer data from your Microsoft SQL Server (MSSQL) database to the Celonis Platform for process mining and analysis. It supports the following basic features:

Note

To use Windows authentication for an on-premise integration, you must use a custom JDBC driver and custom JDBC string in the extractor configuration. For more information, see Authentication method – Windows authentication for on-premise connections.

Additionally, to use Microsoft Entra authentication for an on-premise integration, you must be using JDBC extractor version 2.100 or later. For more information, see Authentication method – Microsoft Entra.

Prerequisites

This section details important prerequisites or prerequisite knowledge for using this extractor.

Choosing the connection type

Before creating a connection between your database and the Celonis Platform you must decide which connection type you want to use. Except where stated in Supported database types, all databases have two basic connection types: Direct connections and Uplink connections via an on-premise extractor, as described below:

  • Direct connections: Use direct connections when you want to allow the Celonis Platform direct access to your database without additional infrastructure. Meaning, you do not need to install, patch, or maintain on-premises extractors, which speeds up implementation, reduces complexity, and simplifies operations.

    Note

    By default, all cloud-based extractors are direct connections.

  • Uplink connections via an on-premise extractor: Use uplink connections when you don't want to or can't allow the Celonis Platform to directly access your on-premise or private cloud database. The connection between the database and Celonis is then established using an on-premise extractor that's installed within your network ideally on a dedicated server.

    The role of the extractor is to poll and fetch job requests from the Celonis Platform, before then submitting the execution information the the database via an SQL query. Once the data is retrieved from the database, the extractor fetches it and sends it back to the Celonis Platform. As such, the connection between the database and the Celonis Platform is always made by the extractor, with it continuously querying the Celonis Platform for any extractions to execute.

    Note

    To use an uplink connection, you must install an on-premise extractor in your environment. To do so, see Setting up. Additionally, if you want to use a proxy (optional), see Proxy settings for on-prem clients.

Modifying your network settings for connections

For the database extractor to communicate with your database and the Celonis Platform, you must modify your network settings to allow access.

Note

Follow the instructions in network settings section below based on the connection type you using. Additionally, if you are using uplink connections, follow the instructions in Celonis Platform IP addresses depending on the cluster .

Network settings for direct connections

The following network settings apply only for direct connections:

Source system

Target system

Port

Protocol

Description

Celonis Platform

Source system

Depending on the database, typical ports are 5432 for PostgreSQL and 30015 for HANA for example

TCP

JDBC connection from the Celonis Platform to the database. The port is the one you normally use to connect to the database. The IPs of the Celonis Platform depending on the cloud cluster (which can be seen in the URL).

Network settings for uplink connections

The following network settings apply only for uplink connections (via the on-premise extractor):

Source system

Target system

Port

Protocol

Description

On-premise extractor server

Source system

Depending on the database, typical ports are 5432 for PostgreSQL and 30015 for HANA for example.

TCP

JDBC connection from on-premise extractor server to the database. The port is the one you normally use to connect to the database.

On-premise extractor server

Celonis Platform

443

TCP

HTTPS connection from on-premise extractor server to Celonis cloud endpoint. The IPs of the Celonis Platform depending on the cloud cluster (which can be seen in the URL).

Celonis Platform IP addresses depending on the cluster

The respective clusters use multiple IPs each, so you need to enable all three of them in your firewall configuration to connect the on-premise extractor server and the cloud endpoint.

For a complete list of inbound and outbound Celonis Platform IP addresses to be allowlisted if needed, see: Allowlisting Celonis domain names, IP addresses, and third-party domains

JDBC string guidelines

This section describes the guidelines for using custom JDBC strings in extractor configurations:

  • Authentication: The Credentials fields in the extractor configuration are required and always used to authenticate the connection. Do not embed credentials directly in your JDBC string.

  • Encryption: For standard (unencrypted) extractors (examples: SAP HANA, PostgreSQL), you can enable encryption by adding encrypt=true to the JDBC string. For encrypted extractors (examples: SAP HANA encrypted, PostgreSQL encrypted), connections are established with encryption enabled (encrypt=true) by default. You do not need to include this parameter in your JDBC string.

  • Certificate validation: Do not include validateCertificate=true in your JDBC strings. Instead, use Advanced Settings > Validate Certificate > Enabled.

  • Additional properties: You can include additional properties in either the JDBC string or the Additional Properties field. Do not specify the same properties in both places.

Authentication methods

This extractor supports the authentication methods described in the following sections.

Note

For cloud integrations, only ??? is available.

Authentication method – username and password

The Microsoft SQL Server extractor can connect to the database using a database user account. Provide the username and password for this account to authenticate the connection. Ensure this database user has sufficient permissions to access the data to be extracted.

Authentication method – Windows authentication for on-premise connections

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:

  1. Configure your Microsoft SQL Server to accept TCP and IP connections before you continue. To learn how to do this, see the official Microsoft document: Microsoft Learn - SQL Server.

  2. On the extractor server, down load the JDBC driver and specific the Windows authentication library in the PATH environment variable:

    1. 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.

    2. Unzip the file, and navigate to sqljdbc_<version>\<language>\auth\x64.

    3. Copy the sqljdbc_auth.dll into a directory that is defined in your PATH environment variable. Ensure it is the absolute PATH of where you added the folder, for example C:\Windows\System\mssql-jdbc_auth-12.4.2.x64.dll.

  3. 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, ensure that the service is using the desired account.

    To do so, 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 Microsoft SQL Server.

  4. 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.

  5. To start the extractor with the JDBC driver that you downloaded, the startup script needs to be adjusted in order to reference the correct driver.

    You need to specify the driver you downloaded 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.

  6. You need to specify an additional parameter when configuring the connection. Either append the following to the JDBC Connection String value or add it in the Additional Properties:

    IntegratedSecurity=true

Important

Even though Windows authentication is configured, the Credential > Username and Password fields are still required in the extractor configuration. You must provide values in these fields; however, when the connection is established, those values are ignored, and Windows authentication is used instead.

Authentication method – Microsoft Entra

If you're using JDBC extractor version 2.100 or later, you can also use Microsoft Entra authentication. This is available out-of-the-box with the following authentication flows supported:

  • ActiveDirectoryManagedIdentity

  • ActiveDirectoryManagedIdentity

  • ActiveDirectoryIntegrated

  • ActiveDirectoryPassword

  • ActiveDirectoryServicePrincipal

  • SqlPassword

For more information about Microsoft Entra, see: Learn Microsoft - Connect using Microsoft Entra authentication.

Configuring the Microsoft SQL Server extractor

This section describes the basic setup of configuring the Microsoft SQL Server extractor. To configure the extractor:

  1. In the Celonis Platform left navigation, select Data > Data Integration.

  2. On the Data Pools screen, select the data pool you want to use for the extraction.

    Note

    If you do not have a data pool to use for this extraction, see Creating and managing data pools for instructions on how to create one.

  3. In the Data Integration section, select Connect to Data Source.

    Note

    If this is not the data pool's first connection, the Data Connections window opens below. Select + Add Data Connection to add a new connection.

  4. In the Add Data Connection window, select Connect to Data Source.

  5. In the Connect to Data Source window, depending on your use case, select either Database – On Premise or Database – Cloud.

    Note

    Select Database – On Premise to connect to on-premise or private cloud databases.

    1. If you selected Database – On Premise, follow the on-screen instructions.

  6. In the New Database Data Connection window, fill in the following information:

    1. For Name, provide a name for this configuration.

    2. For Database Type, select Microsoft SQL Server.

    3. For Connection Type, select either Standard or Custom JDBC Connection String.

      Note

      For uplink connections with this extractor, you must select Custom JDBC Connection String.

      1. If you selected Standard:

        • For Host, enter the database server name or IP address of the database server.

        • For Port, provide the port to connect to (Default is 1433).

        • For Database Name, enter the name of the database that contains the data you want to extract.

        • (Optional) For Schema Name, enter the name of the schema that contains the tables to extract.

        • (Optional) For Additional Properties, enter any additional connection properties required by your database or driver. Separate each with ;.

      2. If you selected Custom JDBC Connection String:

        Important

        When using JDBC strings, there are specific guidelines to follow. For more information, see JDBC string_guildelines.

        • For JDBC Connection String, provide your string. Use the format:

          jdbc:sqlserver://<hostname>:<port>;databaseName=<database_name>;property1=value1;property2=value2...

          Note

          For more information, see the Microsoft SQL Server documentation.

        • Optionally, provide values for:

          • Schema Name: Enter the name of the schema that contains the tables to extract.

          • Additional Properties: Enter any additional connection properties required by your database or driver. Separate each with ;.

    4. For Credentials, enter the username and password for your database user.

      Note

      Ensure the credentials used have sufficient permissions to access the data to be extracted.

    5. If desired, select Advanced Settings, and update these parameters as needed.

      Note

      The Advanced Setting > Validate Certificate parameter (Default: DISABLED) controls whether the extractor validates the server’s SSL/TLS certificate:

      • Disabled: Disables certificate validation (validateCertificate=false).

      • Enabled: Enforces certificate validation (validateCertificate=true).

      • Removed: Uses the driver’s default behavior. Check the driver documentation to confirm the default.

  7. Select the Test Connection button to confirm the extractor can connect to the host system. If the test fails, adjust the data in the configuration fields as needed.

  8. Once the test connection passes, select the Save button to continue. This returns you to the Data Integration window.