topic Qlik Replicate: Unable to test ms-cdc/sqlserver source endpoint in Qlik Replicate

Qlik Replicate: Unable to test ms-cdc/sqlserver source endpoint

dineshkumarl — Thu, 20 Apr 2023 12:17:15 GMT

I am trying to setup SQL Server as source in Qlik Replicate. However, I am receiving error 'Failed to load SQL Client' cannot load <msodbcsql17.dll, sqlncli11.dll, > Cannot load sqlncli.dll. The specified module cannot be found.

I was able to locate sqlncli.dll in WinSxS folder and setup environment path for the same.

- Restarted Qlik Replicate server and check. Still ended up receiving the same error.

I have tried installing odbc driver version 18.1, 17.6 and 11 but still issue persists.

I have attached error screenshot for reference.

Below are pre-requisites enabled at mssql source db:

> Preparing the MSSQL database for CDC

The following section explains how to set up the database for CDC.

To set up the database for CDC:

> Setup the database for MS-CDC by executing the following: exec source_db_name.dbo.sp_cdc_enable_db

where source_db_name is the name of the source database. (Skip the step if CDC is enabled for the source database)

> The replication task automatically enables MS-CDC for all the source tables.

However, if you prefer to enable MS-CDC manually, execute the following script (for each of the intended source tables):

exec sys.sp_cdc_enable_table

@Source_schema = N'schema_name',

@Source_name = N'table_name',

@role_name = NULL

where schema_name and table_name are the names of the source schema and the source table respectively.

> Required permissions

The user specified in the endpoint settings must have the following privileges on the source database:

Running without the db_owner role

If the user is not a member of the db_owner database role, the following permissions must be granted on the source database for CDCUSER:

- grant VIEW DATABASE STATE

- grant SELECT

Kindly assist.

Re: Qlik Replicate: Unable to test ms-cdc/sqlserver source endpoint

Dana_Baldwin — Thu, 20 Apr 2023 16:42:51 GMT

Hi @dineshkumarl

Which version of Qlik Replicate are you using?

Is SQL Server on prem or Azure?

Thanks,

Dana

Re: Qlik Replicate: Unable to test ms-cdc/sqlserver source endpoint

john_wang — Fri, 21 Apr 2023 00:59:44 GMT

Hello @dineshkumarl ,

Besides @Dana_Baldwin comment, in old Replicate versions, the default driver is SQL Server Native Client 11, in new versions the default driver is Microsoft ODBC Driver 17.6, or Microsoft ODBC Driver 18.1. From the error messages, it seems Replicate is trying to load Native Client 11.

If you want to Replicate use non-default version (if these versions can be installed side by side on the same server) or different driver to connect to SQL Server source:

Open SQLServer source endpoint
Go to the Advanced tab
Open Internal Parameters
Add a new parameter named provider
Press <Enter> and set the parameter's value to:

ODBC Driver 18 for SQL Server

A sample:

You can use any supported versions, the ODBC Driver name can be found in ODBC Manager.

Hope this helps.

Regards,

John.

Re: Qlik Replicate: Unable to test ms-cdc/sqlserver source endpoint

dineshkumarl — Mon, 24 Apr 2023 07:18:13 GMT

@john_wang I have added the mentioned parameter but still receiving the same error. Do you have to restart qlik replicate server after adding the parameter?

Re: Qlik Replicate: Unable to test ms-cdc/sqlserver source endpoint

dineshkumarl — Mon, 24 Apr 2023 07:18:46 GMT

@Dana_Baldwin

Qlik replicate: Product Release: 2021.11.0.165

SQL Server: On-Prem

Re: Qlik Replicate: Unable to test ms-cdc/sqlserver source endpoint

Dana_Baldwin — Mon, 24 Apr 2023 17:54:09 GMT

Hi @dineshkumarl

You do not have to restart the Replicate services after adding an internal parameter to an endpoint, but you do need to restart the services after each driver installation on the server.

Version 2021.11 requires driver ODBC Driver 17.6. I know you've tried this one before, and using the internal parameter should allow you to use a different driver, but we should probably focus on getting this version to work since it is the one recommended in the User Guide.

Prerequisites | Qlik Replicate Help

Is the driver listed in ODBC Data Source Administrator (64-bit) on the Drivers tab?

Thanks,

Dana

Re: Qlik Replicate: Unable to test ms-cdc/sqlserver source endpoint

SwathiPulagam — Fri, 28 Apr 2023 16:46:13 GMT

Hi @dineshkumarl ,

After restarting the Replicate service if the issue is not resolved then please open a support case.

Thanks,

Swathi

Re: Qlik Replicate: Unable to test ms-cdc/sqlserver source endpoint

dineshkumarl — Sat, 29 Apr 2023 12:10:40 GMT

Hi All,

The issue still persist. Hence, I have created a support ticket as suggested.

Thanks for your assistance!

Re: Qlik Replicate: Unable to test ms-cdc/sqlserver source endpoint

S_Ravi — Mon, 17 Jun 2024 14:07:01 GMT

Any update on this one - I am also seeing the same issue it is not allowing to save 18, but it allows to save 17. But I need 18.

Re: Qlik Replicate: Unable to test ms-cdc/sqlserver source endpoint

john_wang — Mon, 17 Jun 2024 14:27:59 GMT

Hello @S_Ravi ,

You may choose MS ODBC version if you installed multiple ODBC clients on the same server, see my previous comment (in 2023-04-21). MS ODBC version 17 and version 18 can be installed side by side, you may use internal parameter provider to decide which version should be used.

Hope this helps.

John.

Re: Qlik Replicate: Unable to test ms-cdc/sqlserver source endpoint

S_Ravi — Mon, 17 Jun 2024 14:38:31 GMT

I tested , it works now having both odbc 17 and 18 on qreplicate server , it picks odbc 18 correctly.

Re: Qlik Replicate: Unable to test ms-cdc/sqlserver source endpoint

john_wang — Mon, 17 Jun 2024 14:40:46 GMT

Glad to hear that, thank you so much for your great support @S_Ravi !

Re: Qlik Replicate: Unable to test ms-cdc/sqlserver source endpoint

dineshkumarl — Thu, 20 Jun 2024 05:37:58 GMT

Hello,

I was able to resolve the issue by :

We were able to succeed with test connection after installing ODBC driver 17.6 and removing internal parameter. 18 did not work for me.