topic Re: Qlik SQL Server much slower than OLE DB connection in Connectivity & Data Prep

Qlik SQL Server much slower than OLE DB connection

mjperreault — Thu, 23 Dec 2021 14:40:01 GMT

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-tls-1-2/46012912

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

Re: Qlik SQL Server much slower than OLE DB connection

fosuzuki — Wed, 05 Aug 2020 22:22:08 GMT

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)."

Re: Qlik SQL Server much slower than OLE DB connection

mjperreault — Wed, 05 Aug 2020 22:25:25 GMT

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-driver-for-sql-server

Re: Qlik SQL Server much slower than OLE DB connection

mgranillo — Thu, 28 Jul 2022 20:57:38 GMT

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?

Re: Qlik SQL Server much slower than OLE DB connection

ctroff — Wed, 12 Jul 2023 14:29:16 GMT

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.

Re: Qlik SQL Server much slower than OLE DB connection

mgranillo — Wed, 12 Jul 2023 19:49:34 GMT

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

Re: Qlik SQL Server much slower than OLE DB connection

ctroff — Thu, 13 Jul 2023 10:57:29 GMT

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

Re: Qlik SQL Server much slower than OLE DB connection

ctroff — Tue, 18 Jul 2023 08:53:10 GMT

If I understand the article, https://techcommunity.microsoft.com/t5/sql-server-blog/ole-db-driver-19-0-for-sql-server-released/ba-p/3170362, 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.