Skip to main content
Announcements
UPGRADE ADVISORY for Qlik Replicate 2024.5: Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
DanCarvin
Contributor II
Contributor II

TrustServerCerticate in SQL connection String

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

Labels (1)
2 Solutions

Accepted Solutions
john_wang
Support
Support

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:

john_wang_0-1712017023667.png

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 ";")
    john_wang_1-1712017317547.png

     

    Hope this helps.
    John.

 

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!

View solution in original post

Dana_Baldwin
Support
Support

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

View solution in original post

6 Replies
john_wang
Support
Support

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:

john_wang_0-1712017023667.png

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 ";")
    john_wang_1-1712017317547.png

     

    Hope this helps.
    John.

 

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
DanCarvin
Contributor II
Contributor II
Author

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
Support
Support

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
Support
Support

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.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
DanCarvin
Contributor II
Contributor II
Author

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
Support
Support

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

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!