JdbcOffsetBackingStore cannot be enabled when using Debezium Operator [DBZ-9152]

Migrated from [DBZ-9152](https://issues.redhat.com/browse/DBZ-9152)

h1. Bug report
h2. What Debezium connector do you use and what version?

Debezioum Operator Helm chart v 3.1.2

Makes use of Debezium 3.1.2-fina
h2. What is the connector configuration?

The DebeziumServer that is deployed with the operator:
{code:java}
apiVersion: debezium.io/v1alpha1
kind: DebeziumServer
metadata:
name: foo-server
# https://github.com/debezium/debezium-operator/blob/main/docs/reference.adoc#debezium-operator-schema-reference-debeziumserverspec
spec:
image: "quay.io/debezium/server:3.1.2-Final"
quarkus:
config:
kubernetes-config.enabled: true
kubernetes-config.secrets: db-credentials
runtime:
api:
enabled: true
sink:
type: pulsar
config:
client.serviceUrl: pulsar://pulsar-standalone:6650
source:
class: io.debezium.connector.sqlserver.SqlServerConnector
sourceClass: io.debezium.connector.sqlserver.SqlServerConnector
offset:
flush.interval.ms: 60000
l
# https://github.com/debezium/debezium/blob/main/debezium-storage/debezium-storage-jdbc/src/main/java/io/debezium/storage/jdbc/offset/JdbcOffsetBackingStoreConfig.java
# https://github.com/debezium/debezium/blob/main/debezium-storage/debezium-storage-jdbc/src/main/java/io/debezium/storage/jdbc/JdbcCommonConfig.java

# << PROBLEM HERE >>
# I should put:
# storage: io.debezium.storage.jdbc.offset.JdbcOffsetBackingStore
# But then the yaml is not really valid anymore, and below jdbc settings are ignored
storage:
jdbc:
connection: # <-- btw, no mention of this level in the documentation (https://github.com/debezium/debezium-operator/blob/main/docs/reference.adoc#debezium-operator-schema-reference-jdbcoffsetstore);
# however, if you ommit it, the server complains about missing url etc
url: jdbc:sqlserver://mssql-latest:1433;sendStringParametersAsUnicode=false;encrypt=false;databaseName=DICCO
user: '${DB_DDL_USER}'
password: '${DB_DDL_PASSWORD}'
wait.retry.delay.ms: 2000
retry.max.attempts: 20
offset:
table:
name: debezium_offset_table
config:
schemaHistory:
memory: { }
config:
# this doesn't help either; it's simply ignored
offset.storage: io.debezium.storage.jdbc.offset.JdbcOffsetBackingStore
{code}
Look at the comments I've put in the yaml above.

I cannot 'force' the `io.debezium.storage.jdbc.offset.JdbcOffsetBackingStore` as that yields the yaml invalid. I would have to put:
{code:java}
...
  offset:
      flush.interval.ms: 60000
      storage: io.debezium.storage.jdbc.offset.JdbcOffsetBackingStore
      storage:
        jdbc:
....{code}
I tried forcing the setting using the source's 'global' config, but te setting is not picked up.

The `DebeziumServer` deploys, but its output suggests that the `MemoryOffsetBackingStore` :

{"timestamp":"2025-06-17T14:38:19.202099434Z","sequence":768,"loggerClassName":"org.slf4j.impl.Slf4jLogger","loggerName":"io.debezium.connector.common.BaseSourceTask","level":"INFO","message":"Starting SqlServerConnectorTask with configuration:\n   connector.class = io.debezium.connector.sqlserver.SqlServerConnector\n   record.processing.shutdown.timeout.ms = 1000\n   header.converter.header = json\n   record.processing.order = ORDERED\n   topic.prefix = calendar-acl-cdc\n   decimal.handling.mode = double\n   offset.storage.file.filename = \n   {*}offset.storage.pulsar.client.serviceUrl = pulsar://pulsar-standalone:6650{*}\n   record.processing.threads = \n   errors.retry.delay.initial.ms = 300\n   schema.history.internal.pulsar.client.serviceUrl = pulsar://pulsar-standalone:6650\n   value.converter = org.apache.kafka.connect.json.JsonConverter\n   key.converter = org.apache.kafka.connect.json.JsonConverter\n   tenant = connect-evolution\n   header.converter.value = json\n   database.user = dicco_user\n   database.dbname = dicco\n   database.names = dicco\n   offset.storage = {*}org.apache.kafka.connect.storage.MemoryOffsetBackingStore{*}\n   header.converter.key = json\n   key.converter.value = json\n   value.converter.header = json\n   time.precision.mode = connect\n   key.converter.header = json\n   database.server.name = cdc\n   offset.flush.timeout.ms = 5000\n   errors.retry.delay.max.ms = 10000\n   snapshot.isolation.mode = read_committed\n   database.port = 1433\n   value.converter.value = json\n   offset.flush.interval.ms = 60000\n   internal.task.management.timeout.ms = 180000\n   schema.history.internal = io.debezium.relational.history.MemorySchemaHistory\n   record.processing.with.serial.consumer = false\n   errors.max.retries = -1\n   database.hostname = mssql-latest\n   database.password = ********\n   namespace = calendar-acl-cdc\n   name = pulsar\n   task.id = 0\n   table.include.list = ACERTA.GBTEMC01,ACERTA.GBTEMC02,ACERTA.GBTEMC06,ACERTA.GBTEMC03,ACERTA.GBTPAA30,ACERTA.GBTPAA31,ACERTA.GBTPAA32,ACERTA.GBTPAA01,ACERTA.GBTPAA02,ACERTA.GBTPAA10,ACERTA.GBTPAA12,ACERTA.GBTPAA51,ACERTA.GBTOAA01,ACERTA.GBTOAA03,ACERTA.GBTEMR01,ACERTA.GBTEMR02,ACERTA.GBTEMR20,ACERTA.GBTEMR23,ACERTA.GBTACD01,ACERTA.GBTSSR02\n   database.trustServerCertificate = true\n   value.converter.key = json\n   snapshot.mode = schema_only\n   key.converter.key = json\n","threadName":"pool-9-thread-1","threadId":45,"mdc":{},"ndc":"","hostName":"debezium-server-d4448678c-vj2z4","processName":"/usr/lib/jvm/java-21-openjdk-21.0.7.0.6-2.el8.x86_64/bin/java","processId":1}

I don't know why *offset.storage.pulsar.client.serviceUrl* ** is still being set, but I will ignore this :)

Another 'weird' thing is that I don't see any properties being logged that are part of the JDBC-config that I passed in the jdbc.connection.* section. But if I omit this config, the server complains, so I guess it must be there.

I looked at the official IT test [https://github.com/debezium/debezium/blob/cd75ed1c01d2245c93554aad25b09b19938b8dce/debezium-storage/debezium-storage-jdbc/src/test/java/io/debezium/storage/jdbc/JdbcOffsetBackingStoreIT.java#L133]

This IT test does NOT read an actual yaml config file, so there it actually can work. But IMHO, this is conflicting with the configuration that you need in the DebeziumServer CR.
h2. What is the captured database version and mode of deployment?

On-premises kubernetes; MS SQL Server container running in same namespace as debezium. Connection is not a problem, as the DebeziumServer starts streaming to Pulsar. But the offset is kept in memory. 
h2. What behavior do you expect?

io.debezium.storage.jdbc.offset.JdbcOffsetBackingStore should be instantiated and activated.
h2. What behavior do you see?

I see that the *org.apache.kafka.connect.storage.MemoryOffsetBackingStore* is used.
h2. Do you see the same behaviour using the latest released Debezium version?

This is the latest released version.

h2. Logs
Logs in attachment.

Start with the Debezium Operator schema reference for DebeziumServer and JdbcOffsetStore, then compare the JDBC configuration classes in debezium-storage/debezium-storage-jdbc with JdbcOffsetBackingStoreIT.java. Done means a valid DebeziumServer CR accepts the JDBC offset-store configuration and the connector uses JdbcOffsetBackingStore instead of MemoryOffsetBackingStore.

Written by the indexing model from the issue text.