topic TrustServerCerticate in SQL connection String in Qlik Replicate

TrustServerCerticate in SQL connection String

DanCarvin — Mon, 01 Apr 2024 22:43:46 GMT

It is there a way in Microsoft SQL Server endpoints to set TrustServerCertificate=True?

Re: TrustServerCerticate in SQL connection String

john_wang — Tue, 02 Apr 2024 00:24:35 GMT

Thanks for reaching out to Qlik Community!

Not sure is it SQL Server source endpoint, or target endpoint.

In SQL Server target endpoint, we may add TrustServerCertificate=Yes; (do not forget the last ";") to Additional ODBC connection properties in GUI directly, a sample:

For SQL Server source endpoint, we need below steps:

Open SQL Server source endpoint
Go to the Advanced tab
Open Internal Parameters
Add a new parameter named additionalConnectionProperties
Press <Enter> and set the parameter's value to:
TrustServerCertificate=Yes; (do not forget the last ";")

Hope this helps.
John.

Re: TrustServerCerticate in SQL connection String

DanCarvin — Tue, 02 Apr 2024 21:27:15 GMT

Hi. The connection test to all SQL endpoints still fails, although tasks that have been running for weeks are still running, so I assume some change to network security has occurred.

The Message says "ODBC Drive 18 for SQL Server Network Interfaces: The login attempt failed" Is there another log that may give more clues on what is happening?

Thanks.

Re: TrustServerCerticate in SQL connection String

Dana_Baldwin — Wed, 03 Apr 2024 00:33:15 GMT

On the Server page, Logging, Set Logging Levels - you can increase logging for component Utilities to verbose, test the connection, then put logging back to info and check the log by clicking view logs from the server page.

Please also confirm the regular things such as the user password or permissions being changed.

FYI, endpoint settings are only read during task startup - so edits to the endpoint will not affect tasks that are currently running.

Hope this helps.

Dana

Re: TrustServerCerticate in SQL connection String

john_wang — Wed, 03 Apr 2024 00:47:50 GMT

Hello @DanCarvin ,

Besides @Dana_Baldwin comments, I'd like suggest opening a 64-bit ODBC DSN, and input various parameters include TrustServerCertificate=True in the DSN, then test the connectivity issue by the DSN via 3rd party ODBC Test Tools.

Hope this helps.

John.

Re: TrustServerCerticate in SQL connection String

DanCarvin — Wed, 03 Apr 2024 18:51:49 GMT

It turns out someone changed the service account password, which I wasn't expecting. Sorry for the trouble and thanks for information about adding connection options.

Re: TrustServerCerticate in SQL connection String

john_wang — Thu, 04 Apr 2024 01:12:05 GMT

Thank you so much for your feedback @DanCarvin ! It's valuable for all of us.