Issue with Sink configuration/setup for Debezium Kafka Connect

[Ravinder Singh]

Hi 

I am using Docker setup for Debezium that has the following

1. Source - MS SQL Server

2. Kafka Connect Debezium

3. Kafka Queue

4. Sink - MS SQL Server.

When I register JDBC Sink Connector for SQL Server I get error 

No suitable driver found for jdbc:sqlserver://mssql_sink:11433/BluePrism?userName=sa&password=Sql_Server_Admin

My Sink Configuration is as follows:

{

    "name": "SQLServer-Sink-Connector",

    "config": {

        "connector.class": "io.confluent.connect.jdbc.JdbcSinkConnector",

        "tasks.max": "1",

        "topics": "BPASession",

        "connection.url": "jdbc:sqlserver://mssql_sink:11433;databaseName=BluePrism;userName=sa;password=Sql_Server_Admin",

        "connection.user": "sa",

        "connection.password": "Sql_Server_Admin",

        "auto.create": "true",

        "auto.evolve": "true",

        "insert.mode": "UPSERT",

        "pk.mode": "record_value",

        "pk.fields": "sessionid",

        "transforms": "unwrap",

        "transforms.unwrap.type": "io.debezium.transforms.ExtractNewRecordState"

    }

}

And my dockerfile for the  kafka connect (which follows debezium end-2-end example for postgres) is as follows:

ARG DEBEZIUM_VERSION=1.9

FROM quay.io/debezium/connect:$DEBEZIUM_VERSION

ARG KAFKA_JDBC_VERSION=5.3.1

ARG MSSQL_JDBC_VERSION=9.4.1

ARG MSSQL_JRE_VERSION=8

# Fetch and deploy SQL Server JDBC driver

RUN cd /kafka/libs \

    && curl -sO https://github.com/microsoft/mssql-jdbc/releases/download/v$MSSQL_JDBC_VERSION/mssql-jdbc-${MSSQL_JDBC_VERSION}.jre${MSSQL_JRE_VERSION}.jar

# Fetch and deploy Kafka Connect JDBC

ENV KAFKA_CONNECT_JDBC_DIR=$KAFKA_CONNECT_PLUGINS_DIR/kafka-connect-jdbc

RUN mkdir $KAFKA_CONNECT_JDBC_DIR

RUN cd $KAFKA_CONNECT_JDBC_DIR \

    && curl -sO https://packages.confluent.io/maven/io/confluent/kafka-connect-jdbc/$KAFKA_JDBC_VERSION/kafka-connect-jdbc-$KAFKA_JDBC_VERSION.jar \

    && curl -sO https://github.com/microsoft/mssql-jdbc/releases/download/v$MSSQL_JDBC_VERSION/mssql-jdbc-${MSSQL_JDBC_VERSION}.jre${MSSQL_JRE_VERSION}.jar

Please could you help if you have any information. I was able write to sink if I use Postgres but not able to do so with SQL Server. Any help is highly appreciated.

Thanks

[Ravinder Singh]

Hi 

An update,

When I modify the Dockerfile ENV KAFKA_CONNECT_JDBC_DIR and copy the JDBC driver to folder /connect/debezium-connector-sqlserver I get a different error as follows:

com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host mssql_sink, port 11433 has failed. Error: "Connection refused (Connection refused). Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall."

Either way there are issues. Any help please highly appreciated.

Thanks

Ravinder

[jiri.p...@gmail.com]

Could you please share the docker commands you use to start the database too? Is the port really 11433?

J.

[Ravinder Singh]

Hi J.

Thanks for coming back. Yes the port is correct. I am using docker compose file and have source DB running at port 1433 and sink at 11433.

I also resolved the issue. I updated the connection URL to jdbc:sqlserver://host.docker.internal:1432;databaseName=BluePrism, with this and auto.create="true", I see that the connector tries to create table. Hence Sink Connector is working. However, I get another error but that is an SQL error which I am solving at my end.

Thanks

Ravi

[George H]

Hi there,

My name is George form a recruitment consultancy in the UK.

We are recruiting for a Debezium Consultant for a remote working contract.

If you are interested or know anyone who has excellent Debezium experience please mail geo...@digisourced.com and rebecca...@recann.co.uk