SQL connection open failed when using MS OLE DB Driver 19 for SQL Server

Question

Hi Team,

Uninstalled the older version of "Microsoft® OLE DB Driver 18 for SQL Server®" and installed the latest "OLE DB Driver 19.0 for SQL Server". Testing the connection using OLE DB Driver 19 driver then getting the following connection error:

---

OLE DB Driver 19 for SQL Server Data Link Error

[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.

Looks like opening a connection got failed. When it calls Open() method then it throws the above error.

My Code Snippet:

string connStr = string.Empty;
if (rbUseMSOLEDB.Checked)
connStr = "provider=MSOLEDBSQL19;";
else
connStr = "provider=SQLOLEDB;";
connStr += "Data Source=" + txtServer.Text + "; Initial Catalog=" + txtDBName.Text + "; User Id=" + txtUserName.Text + "; Password=" + txtPassword.Text + ";";
using (OleDbConnection con = new OleDbConnection(connStr))
{
con.Open();
con.Close();
}

Please help me to find out the root cause of this error.

My Configuration Details as below:
SQL Server Management Studio 15.0.18338.0
SQL Server Management Objects (SMO) 16.100.41011.9
Microsoft Analysis Services Client Tools 15.0.19205.0
Microsoft Data Access Components (MDAC) 10.0.19041.1
Microsoft MSXML 3.0 6.0
Microsoft .NET Framework 4.0.30319.42000
Operating System 10.0.19044

Note: With older version of Microsoft® OLE DB Driver 18 for SQL Server®" (18.6.3.0) connection is working using SQLOLEDB provider.

Regards,
Suresh Kaushik

Answer 1

Hi @Suresh Kaushik ,

There is a breaking change in the Microsoft OLE DB Driver 19 for SQL Server. It requires a certificate.

Though it is possible to disable it via additional setting in the connection string:
Use Encryption for Data=False

Here is a sample of the entire connection string:
Provider=MSOLEDBSQL19.1;Integrated Security=SSPI;Persist Security Info=False;User ID="";Initial Catalog=AdventureWorks2019;Data Source=.;Use Encryption for Data=False;

Answer 2

Hi @Suresh Kaushik ，

There a couple breaking changes in 19.0, like changing the default value of the 'Encrypt' connection option from 'no' to 'yse/mandatory'
If you are affected by the Encrypt change:
Install a trusted certificate on your server
Change your client's Encrypt connection string setting(or connection property) to optional no

If you are using a self-signed certificate and the Force Encryption setting on the server to ensure clients connect with encryption:
Change to a certificate that is trusted as part of the client's trust chain
Add the self-signed certificate as a trusted certifiicate on the client
Change your client's TrustServerCertificate connection string setting(or connection property) to yes

like this article says:
https://techcommunity.microsoft.com/t5/sql-server-blog/ole-db-driver-19-0-for-sql-server-released/ba-p/3170362

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.