Issue:
Database Connection String: Encrypt=True;TrustServerCertificate=False
If you recently upgraded your SQL Server Native Client 11.0 (Provider=SQLNCLI11) application to use Microsoft OLE DB Driver 19 for SQL Server (Provider=MSOLEDBSQL19), you might receive error messages that resemble the following messages:
[Microsoft OLE DB Driver 19 for SQL Server]: Client unable to establish connection
[Microsoft OLE DB Driver 19 for SQL Server]: SSL Provider: The certificate chain was issued by an authority that is not trusted.
Cause
These errors occur if both the following conditions are true:
- The Force encryption setting for the SQL Server instance is set to No.
- The client connection string doesn’t explicitly specify a value for encryption property, or the Encryption option wasn’t explicitly set or updated in the DSN.
The error occurs because of a change in the default behavior of the client drivers. Older versions of client drivers are designed to assume that data encryption is OFF by default. The new drivers assume this setting to be ON by default. Because data encryption is set to ON, the driver tries to validate the server’s certificate and fails.
Solutions
- Solution 1: Use Microsoft OLE DB Driver for SQL Server 18.x. You can download the driver from Release notes for the Microsoft OLE DB Driver for SQL Server.
- Solution 2: If the application connection string property already specifies a value of Yes or Mandatory for the Encrypt/Use Encryption for Data setting, change the value to No or Optional. For example, Use Encryption for Data=Optional. If the connection string doesn’t specify any value for Encrypt/Use Encryption for Data, add Use Encryption for Data=Optional to the connection string. For more information, see Encryption and certificate validation.
- Solution 3: Add
;Trust Server Certificate=trueto the connection string. This will force the client to trust the certificate without validation.
Database Connection String: Encrypt=False;TrustServerCertificate=False
Comments