CODEWITHSUNDEEP
sqlexcelvbaconnection-string
BDra
BDra

Reputation: 506

MSOLEDBSQL Causes Error 3706: Provider Cannot Be Found

A client has for many years been using an Excel VBA application to interact with a SQL database. Recently the database has been upgraded from TLS 1.0 to TLS 1.2, which caused the connection from VBA to fail, as the SQLOLEDB driver has been deprecated. (Background here and here.)

To resolve the issue, I am trying to change the SQL driver from SQLOLEDB to MSOLEDBSQL. However, attempting to connect to the DB triggers run-time error 3706: "Provider cannot be found. It may not be properly installed."

Here is the relevant code:

Dim conn As ADODB.Connection
Dim sConnString As String
 
sConnString = "Provider=MSOLEDBSQL;" & _
  "Data Source=MyServerName;" & _
  "Initial Catalog=MyDBName;" & _
  "User ID=SomeID;" & _
  "Password=SomePassword;" & _
  "DataTypeCompatibility=80"

Set conn = New ADODB.Connection
conn.Open sConnString

All instructions I have found online say to simply install the MSOLEDBSQL driver from Microsoft and change Provider=SQLOLEDB to Provider=MSOLEDBSQL in the code. But as I keep getting error 3706, presumably I must have missed a step.

Steps taken so far:

  1. Installed 32-bit MSOLEDBSQL driver from Microsoft.
  2. Confirmed that DLL files have been added to the default location, C:\Windows\System32.
  3. Restarted Windows.
  4. From the command line, ran REGSVR32 "C:\Windows\System32\msoledbsql19.dll". Windows confirmed that the resource had been correctly registered.
  5. In Visual Basic Editor, added a reference to Microsoft ActiveX Data Objects 6.1 Library (and removed a reference to the older v. 2.8 library).

Is it conceivable that the driver has not been properly installed, as claimed in the error description, when Windows also confirms that it has been correctly registered? How do I even troubleshoot this? Any thoughts on what is going on here?

Upvotes: 6

Views: 10336

Answers (2)

BDra
BDra

Reputation: 506

With invaluable help from the DB manager we were able to pinpoint and solve the error. It turned out to be surprisingly simple.

The most recent version of the MSOLEDBSQL driver, v. 19, introduces a couple of major changes, one of which is to turn on encryption by default. Microsoft admits this is a "backwards-compatibility-breaking" change. They discuss the changes, and the reasons for them, here.

This revised version of the connection string works:

Dim conn As ADODB.Connection
Dim sConnString As String
 
sConnString = "Provider=MSOLEDBSQL19;" & _
   "Data Source=MyServerName;" & _
   "Initial Catalog=MyDBName;" & _
   "User ID=SomeID;" & _
   "Password=SomePassword;" & _
   "Use Encryption for Data=False;" & _
   "DataTypeCompatibility=80"

Set conn = New ADODB.Connection
conn.Open sConnString

The only significant change was that Use Encryption for Data=False needed to be specified.

Upvotes: 8

Brian P
Brian P

Reputation: 21

We had a similar issue with one of our old ASP web apps being unable to access SQL Server after we migrated to an Azure Managed Instance. My answer may seem unrelated but it may actually help you.

We discovered web server was having trouble talking to the SQL server because it needed to handle TLS 1.2 (but we had a generic error until we setup a DSN and ran a test there. ODBC Data Sources app in Windows)

We had to switch to ODBC 13.1 (there is also a v18 available). Here's the link to 13.1 https://www.microsoft.com/en-us/download/details.aspx?id=53339 It was able to handle TLS 1.2

We had to install ODBC 18 for our internal .Net 4.8 apps to work with Azure. https://learn.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver16

We had a TON of obscure issues with our apps when trying to connect to our Azure managed instance using the OLEDB 19. Sometimes it worked great and other times it would fail miserably. Hence me recommending ODBC 13.1 (or higher).

Another thing that was helpful was setting up a test DSN on the machine with the issue (ODBC Data Sources app in Windows). Once we setup a test DSN, that's where we found the TLS error whereas before it was just a basic Can Not Connect error from our app.

Upvotes: 2

Related Questions