How to set encrypt false in Camel Debezium SQL server connector for JDBC connection

user_4602091 0
Viewed 112

I am facing an issue while trying to use Camel Debezium SQL server connector. I am trying to capture data changes in SQL server db table using camel Debezium SQL server connector and sink them to message broker. I know the JDBC SQL server connection has the option to make encrypt false to prevent this issue. But I can't find a similar way in Camel Debezium SQL server connector.

To use Camel Debezium SQL server connector, I was following this documentation:

https://camel.apache.org/components/3.18.x/debezium-sqlserver-component.html#_samples

When I run the app it shows me following error:

ERROR io.debezium.embedded.EmbeddedEngine - Error while trying to run connector class 'io.debezium.connector.sqlserver.SqlServerConnector'

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target".

My POM is as follows:

<dependencyManagement>
  <dependencies>
    <dependency>
      <groupId>org.apache.camel</groupId>
      <artifactId>camel-parent</artifactId>
      <version>3.18.1-SNAPSHOT</version>
      <scope>import</scope>
      <type>pom</type>
    </dependency>
  </dependencies>
</dependencyManagement>
<dependencies>
  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter</artifactId>
  </dependency>
  <dependency>
    <groupId>org.apache.camel</groupId>
    <artifactId>camel-core</artifactId>
  </dependency>
  <dependency>
    <groupId>org.apache.camel</groupId>
    <artifactId>camel-main</artifactId>
  </dependency>
  <dependency>
    <groupId>org.apache.camel</groupId>
    <artifactId>camel-debezium-sqlserver</artifactId>
  </dependency>
  <dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <version>11.2.0.jre11</version>
  </dependency>
  <dependency>
    <groupId>org.apache.camel</groupId>
    <artifactId>camel-jackson</artifactId>
  </dependency>
  <dependency>
    <groupId>org.apache.camel</groupId>
    <artifactId>camel-kafka</artifactId>
  </dependency>
</dependencies>

I am using:
spring-boot:2.7.2
SQL Server:docker image: mcr.microsoft.com/mssql/server:2022-latest
Kafka image: confluentinc/cp-zookeeper:latest

Can anyone help me to resolve this issue?

apache-kafka spring-boot apache-camel sql-server java
2 Answers

When dealing with Debezium connectors, to register a new SQL Server connector we might normally POST a JSON configuration like the following:

curl -H "Content-Type: application/json" -XPOST http://127.0.0.1:8083/connectors --data @- << EOF
{
    "name": "local-hub-connector",
    "config": {
        "connector.class": "io.debezium.connector.sqlserver.SqlServerConnector",
        "database.hostname": "mssql-2019",
        "database.port": 1433,
        "database.user": "Debezium",
        "database.password": "StrongPassw0rd",
        "database.dbname": "DebeziumTest",
        "database.server.name": "DebeziumTestServer",
        "table.include.list": "dbo.tb_CDCTab1",
        "database.history.kafka.bootstrap.servers": "broker:29092",
        "database.history.kafka.topic": "dbhistory.DebeziumTestServer"
    }
}
EOF

This works fine when the connector is using JDBC versions prior to 10.2, but JDBC Driver 10.2 for SQL Server introduced breaking changes, in particular:

BREAKING CHANGE - Default Encrypt to true

This is generally problematic because, by default, SQL Server is installed with a self-signed X.509 certificate so it doesn't appear in any trust stores.

If you're using a new connector container that has JDBC Driver 10.2 for SQL Server (or later) installed you'll need to modify the connector configuration:

  • Do you not need encryption? Turn it off with encrypt=false in the connection string options.
  • Do you need encryption? Add trustServerCertificate=true to the connection string options.

We can do this by way of pass-through configuration properties, i.e.: Debezium SQL Server connector pass-through database driver configuration properties:

The Debezium connector provides for pass-through configuration of the database driver. Pass-through database properties begin with the prefix database.*. For example, the connector passes properties such as database.foobar=false to the JDBC URL.

To turn off encryption we would POST the following JSON configuration:

curl -H "Content-Type: application/json" -XPOST http://127.0.0.1:8083/connectors --data @- << EOF
{
    "name": "local-hub-connector",
    "config": {
        "connector.class": "io.debezium.connector.sqlserver.SqlServerConnector",
        "database.hostname": "mssql-2019",
        "database.port": 1433,
        "database.user": "Debezium",
        "database.password": "StrongPassw0rd",
        "database.dbname": "DebeziumTest",
        "database.server.name": "DebeziumTestServer",
        "table.include.list": "dbo.tb_CDCTab1",
        "database.history.kafka.bootstrap.servers": "broker:29092",
        "database.history.kafka.topic": "dbhistory.DebeziumTestServer",
        "database.encrypt": false
    }
}
EOF

To keep encryption and trust SQL Server's self-signed certificate we would POST the following JSON configuration instead:

curl -H "Content-Type: application/json" -XPOST http://127.0.0.1:8083/connectors --data @- << EOF
{
    "name": "local-hub-connector",
    "config": {
        "connector.class": "io.debezium.connector.sqlserver.SqlServerConnector",
        "database.hostname": "mssql-2019",
        "database.port": 1433,
        "database.user": "Debezium",
        "database.password": "StrongPassw0rd",
        "database.dbname": "DebeziumTest",
        "database.server.name": "DebeziumTestServer",
        "table.include.list": "dbo.tb_CDCTab1",
        "database.history.kafka.bootstrap.servers": "broker:29092",
        "database.history.kafka.topic": "dbhistory.DebeziumTestServer",
        "database.encrypt": true,
        "database.trustServerCertificate": true
    }
}
EOF

If you can't POST configuration changes then perhaps the camel.component.debezium-sqlserver.additional-properties can provide similar functionality.

user_3901220 
<dependency>
  <groupId>com.microsoft.sqlserver</groupId>
  <artifactId>mssql-jdbc</artifactId>
  <version>9.2.1.jre11</version>
</dependency>

Finally I was able to solve the issue by downgrading the mssql-jdbc driver to the above one.

user_46020910
Related