TrustServerCerticate in SQL connection String

[DanCarvin]

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

[john_wang · Accepted solution]

Hello @DanCarvin ,

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:

1. Open SQL Server source endpoint
2. Go to the Advanced tab
3. Open Internal Parameters
4. Add a new parameter named additionalConnectionProperties
   
5. Press <Enter> and set the parameter's value to:
   TrustServerCertificate=Yes; (do not forget the last ";")
   Hope this helps.
   John.

[DanCarvin]

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. 

[Dana_Baldwin · Accepted solution]

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

[john_wang]

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.

[DanCarvin]

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.

[john_wang]

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