OLE DB Provider Update

[mgranillo]

I'm using an OLE DB connection to SQL Server and my provider in the connection string is  [Provider=SQLOLEDB;...]. This is deprecated and Microsoft has release new drivers. I've updated my drivers on the Qlik server and changed my connection string to MSOLEDBSQL19 but the connection does not work. Anyone else dealt with this?  Any other steps needed besides installing the drivers?

[NadiaB]

Hi @mgranillo 

What is the error that you are getting, could you provide the description or an screenshot ?
Thanks, 

[mgranillo]

@NadiaB, Thank you for responding.  I've tried several different things and get a few different errors. I tried setting up a new connection and get a test failed error (screenshot provided). I've also tried modifying the provider of an existing connection string and get a few different errors depending on the provider I set.  If I set provider to MSOLEDBSQL19 I see a client connection error (screenshot provided). If I set the provider to MSOLEDBSQL I see a class not registered error (screenshot provided). I'm changing the provider per the recommendation from this article: https://docs.microsoft.com/en-us/archive/blogs/sqlreleaseservices/released-microsoft-ole-db-driver-f...

It recommends updating connections to MSOLEDSQL (screenshot provided)

[mgranillo]

Also, i have installed 32 and 64 bit drivers and neither work. 

[NadiaB]

Hi @mgranillo 

It seems there are different errors:

1. It seems its not possible to connect to the SQL server from where you are attempting to connect (either the server or your computer if using desktop).

2. The class registered error is usually due to a driver missing. 

3. For the issue with the password, could you use "Specific user name and password" and provide your credentials, try domain\user format when placing the credentials if you get an error with user only.

Hope it helps. 

[mgranillo]

Nadia,

We've made progress on this issue since my last posting.  The install was not the problem.  We received the class register error because on some of our test we were using the wrong provider in the connection string (MSOLEDBSQL instead of MSOLEDBSQL19).  We still have a few connections not working but most of our problems were solved by adding a parameter to our connection string: "Use Encryption for Data=Optional;" 

If we get the other connections working, i'll add the solution here. 

Thanks for all your help,

Mike