Database Agent: Certificate Trust Issues

Problem

The database agent shows the following or similar certificate-related error message when connecting to SQL Server in PeopleSync 23.8 or higher:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 -
The certificate chain was issued by an authority that is not trusted.)

Solution

This is caused by the new “secure by default” options in the updated SQL Server database driver, which cause the driver to encrypt the connection by default and validate the server certificate.

Please see the following Microsoft documentation for details:

Release notes for OLE DB Driver - OLE DB Driver for SQL Server

Encryption and certificate validation - OLE DB Driver for SQL Server

In PeopleSync, this can be addressed either by adding the SQL Server’s certificate or root certificate to the PeopleSync backend server’s certificate store or by amending the connection string to trust the server’s certificate.

Make sure the server name in the PeopleSync database agent matches the server name in the SQL Server’s certificate. In general, the SQL server’s or availability group listener’s FQDN must be used in the database agent.

Prerequisite: Verify that a certificate is configured in SQL Server

Log in to the SQL Server and open SQL Server Configuration Manager
Go to SQL Server Network Configuration > Protocols for MSSQLServer and open its properties.
Switch to the Certifcate tab and check if a certificate is selected in the certificate list.
If no certificate is set, the SQL Server is using the self-signed fallback certificate. In that case, either configure a certificate as per Microsoft documentation or continue to solution 2.
If a certificate is set, click on View to see certificate details. Click on Certification Path to determine the issuing certificate authority. Then you can proceed with solution 1.

If you want to install a certificate in SQL Server, follow Microsoft documentation:
Certificate management (SQL Server Configuration Manager) - SQL Server

Solution 1: Adding the SQL server’s certificate or CA certificate

If the prerequisite check shows that a certificate is set in SQL server, you trust its certification authority by adding it to the Peoplesync backend server’s Trusted Root Certification Authorities folder in the Certificates (Local Computer) node.

Solution 2: Configure PeopleSync database agent to trust the certificate.

If no certificate is set in SQL Server or you just want to trust the server’s certificate, you can make PeopleSync trust the certificate by amending the connection string.

In PeopleSync Console, open the database agent in question and go to Data Source. Make a note of the connection template used.
Go to Database Providers and open the Database provider you noted beforehand:
In Database Providers, click on New and create a new database provider.
Copy all the values from the original database provider to the new one and append “trustservercertificate” to the name.
Also append ;trustservercertificate=true to the connection string template.
Go back to the database agent. Change the connection template to the new database provider and save.
Run the agent by clicking the Play symbol to see if it runs successfully.

PeopleSync Knowledge Base