Qlik SQL Server much slower than OLE DB connection

[mjperreault]

Hi All,

Currently I have an OLE DB connection in my Qlik Sense site to a Microsoft SQL Server Database using the Microsoft OLE DB Provider for SQL Server.  Pretty soon on the database server we are going to be upgrading from TLS 1.0 to TLS 1.2 at which point in time the OLE DB connection will no longer be supported and will not work.  

https://stackoverflow.com/questions/46011694/does-microsoft-ole-db-provider-for-sql-server-support-t...

In anticipation of this I have created another Data Connection using the Microsoft SQL Server connector provided in the ODBC Connector Package.  

I have noticed significant load time impacts of using the ODBC connection, I have a query that takes roughly 3 minutes using the OLE DB connection but takes 11 minutes using the ODBC connection.  I was wondering if anyone else has seen this significant of a load discrepancy between OLE DB and ODBC and any solutions that may have.  As OLE DB is no longer going to be supported by SQL Server what are people planning on doing about it?

Thanks in advance for any thoughts or insights ! 

Mark

[fosuzuki · Accepted solution]

This may not really answer your question, but I'd say that your understanding "As OLE DB is no longer going to be supported by SQL Server" may be is wrong. Probably you just need to use a newer OLEDB driver.

The stackoverflow thread you shared has this hint: "The new OLE DB driver, MSOLEDBSQL, has been released. This new driver includes the support for the latest TLS 1.2 standards and is backwards compatible with SQL Server Native Client 11 (SQLNCLI11)."

[mjperreault]

Thanks @fosuzuki for your response, yes you are right my understanding was wrong! After some more reading I found the below and installed the Microsoft OLE DB Driver for SQL Server which is working.

https://docs.microsoft.com/en-us/archive/blogs/sqlnativeclient/announcing-the-new-release-of-ole-db-...

[mgranillo]

I installed the Microsoft OLE DB Driver 19 for SQL Server but the connection doesn't work.  Did you update your provider to MSOLEDSQL and does it work?  Did you install 32 or 64 bit? Which provider did you select when setting up the connection?

[ctroff]

Hi
Have you solved your problem with the connection and OLE DB Driver 19?
We also installed the same driver as you and get errors when we try to set up a connection.

[mgranillo]

We never made the switch but I think we started getting a working connection by adding "Use Encryption for Data=Optional" to the end of the connection string. You'll have to edit it in the QMC.  I also don't think Qlik will make the connection if it doesn't successfully connect so try making a connection with the old drivers and update the Provider part of the connection string to "Provider=MSOLEDBSQL19" or maybe "Provider=MSOLEDBSQL19.1" in the QMC

[ctroff]

Hi
Thanks for your suggestions! We tried them but couldn't get them to work.
We used ODBC 18 instead which worked.

[ctroff]

If I understand the article, https://techcommunity.microsoft.com/t5/sql-server-blog/ole-db-driver-19-0-for-sql-server-released/ba..., correctly, you must specify in the connectionstring that the data should not be encrypted because it is set to yes by default, Use Encryption for Data=False. Now the data connection works with OLE DB 19.