Database Connect activity fails with: The certificate chain was issued by an authority that is not trusted

News Knowledge Base
, ,
1

Customer facing issue while establishing connection to database server with error as "Connect: 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.)"

Issue Description

After migrating to the Windows compatibility project from Windows - Legacy, the following error occurs when trying to connect to the Database server using Studio activities:

"Connect: 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.)"

Resolution

  1. Validate the certificate that is being used by the SQL server:
    • Open SQL Server Configuration Manager on the SQL server.

kb-0EMPa00000N29l7.png775×502 52.3 KB

    • Expand SQL Server Network Configuration in the left pane, right-click on Protocols for MSSQLSERVER, and click on Properties.

kb-0EMPa00000N2AHN.png383×255 20.5 KB

    • In the Properties window, navigate to the Certificate tab and gather the certificate details by clicking on View... (make sure to save the thumbprint for unique identification)

702×957 117 KB

  1. Export the Certificate:
    • Open Run on the SQL server, and type in certlm.msc and press enter. This will open the Local Machine Certificate Manager.

kb-0EMPa00000UqF6X.png455×268 7.62 KB

kb-0EMPa00000UqFPt.png780×554 117 KB

    • Locate the certificate viewed in the Validating certificate step, in the Personal Store, open it and verify the thumbprint.
    • Right-click on the certificate, select All Tasks->Export.

kb-0EMPa00000N2PrY.png613×283 23.5 KB

    • Save the Certificate to a local path.

  1. Copy the Certificate to the Client Certificate Stores:
    • Next, copy the certificate to the Robot machine's file system.
    • Open Run on the Windows server, and type in certlm.msc and press Enter. This will open the Local Machine Certificate Manager.

kb-0EMPa00000UqF6X.png455×268 7.62 KB

kb-0EMPa00000UqFPt.png780×554 117 KB

    • Once the window has opened, navigate to Personal->Certificates, right-click on the empty space, select All Tasks->Import...

kb-0EMPa00000UqG4D.png825×530 124 KB

    • Perform the steps in the Certificate Import Wizard to import the certificate of your choice.
    • Repeat the above steps for the Trusted Root Certification Authorities->Certificates.
    • Repeat the above on all Robot machines where you want to connect with the database.

Root Cause

The issue had been occurring because the certificate that was being used by the SQL server was not trusted by the Robot machine.