Question:x for SQL Server.
The certificate chain was issued by an authority that is not trusted
What is the fix for this error ?
Answer:
Step 1: Check if sql server Force Encryption is set to No . There are different wasys to check such as How to check SSL encryption is enabled on SQL Server with Powershell & How to check a SQL Server connection is encrypted with SSL
here is an example
DECLARE @force_encryption INT EXEC xp_instance_regread 'HKEY_LOCAL_MACHINE', 'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib', 'ForceEncryption', @force_encryption OUTPUT SELECT CASE WHEN @force_encryption = 1 THEN 'Force Encryption = True ' ELSE 'Force Encryption = False' END
Step 2: Check if the client connection string explcitly states an encryption property value , or DSN
If both Step 1 & Step 2 return No , than the likelihood is your hitting the new default behaviour of the client drivers mentioned in your question :Microsoft OLE DB Driver 19 for SQL Server & Microsoft ODBC Driver 18.x for SQL Server.
These new versions of drivers assume the data encryption is on by DEFAULT - therefore upon connection , there is an attempt to validate the certificate .
There's a few different options to fix the issue
1) Rollback to MS ODBC Driver 17 for SQL Server
2) Review the connection string - does it specify Yes for Encryption , change it to No.
If Data Souce Name (DSN) - review the encryption setting - is set at mandatory ? Change to Optional