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

[dineshkumarl]

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. 

[Dana_Baldwin]

Hi @dineshkumarl 

Which version of Qlik Replicate are you using?

Is SQL Server on prem or Azure?

Thanks,

Dana

[john_wang]

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:

1. Open SQLServer source endpoint
2. Go to the Advanced tab
3. Open Internal Parameters
4. Add a new parameter named provider
   
5. 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.

[dineshkumarl]

@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? 

[dineshkumarl]

@Dana_Baldwin 

Qlik replicate: Product Release: 2021.11.0.165

SQL Server: On-Prem

[Dana_Baldwin]

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

[SwathiPulagam]

Hi @dineshkumarl ,

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

Thanks,

Swathi

[dineshkumarl]

Hi All,

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

Thanks for your assistance!

[S_Ravi]

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. 

[john_wang]

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.