SQL Server connection error

[Anonymous]

We have 2 QDS nodes, One is working fine with SQL server connection and other throws the following error,

I have completely check the Drivers, registry settings, MMC certificates and all look similar.

Any idea on this please.

2017-06-02: 0019  OLEDB CONNECT*Provider*

2017-06-02:       Error: ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: [DBNETLIB][ConnectionOpen (SECCreateCredentials()).]SSL Security error.

2017-06-02:       General Script Error

2017-06-02:       Execution Failed

2017-06-02:      Execution finished.

[beth_lavey]

Were you able to figure this out?  We are getting the same error

[beth_lavey · Accepted solution]

So, the problem was that TLS 1.0 was disabled, and the cipher suite was limited to 16 ciphers as recommended by Microsoft.

We enabled 1.0, and added 3 additional (legacy) ciphers to the list. 

[grangerats]

I had the same issue, but I found a workaround.

We have to disable TLS 1.0 for PCI compliance. I was very surprised to do my usual TLS "hardening" setup only to find out that existing, working database connections no longer worked. And I did not setup the connections' OLEDB provider (64-bit) to the to use encrypted TDS, so I'm guessing perhaps Qlik's code is forcing it?

What I found is that you only have to leave TLS 1.0 enabled for "Client" (outbound) connections. E.g. in a .reg file:

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\Schannel\Protocols\TLS 1.0]

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\Schannel\Protocols\TLS 1.0\Client]

"Enabled"=dword:00000001

You can disable the "Server" counterpart. Yes, that means that the Sql Server you're connecting to is going to need to have TLS 1.0 (Server) enabled.

Oh, and in my case, I'm running Qlik Sense (April 2018 - 12.16.1) on Windows Server 2016 Standard.

Hopefully, the folks at Qlik can figure out how to allow for TLS 1.2 in the near future, since the OS has supported it since Windows 2008 R2.

[grangerats]

OK, I figured it out (I think).

The SQLOLEDB provider itself does not support anything better than TLS 1.0, nor will it ever ( oledb - Does Microsoft OLE DB Provider for SQL Server support TLS 1.2 - Stack Overflow ). I'm going to presume that since that provider is legacy/deprecated, everyone should stop using it as soon as possible.

I don't recall the UI before 2018, but in the April 2018 release has a new (to me) way to choose the connection's provider. For Sql Server, you can choose between "Microsoft SQL Server", "ODBC", and "OLE DB". When you choose "Microsoft SQL Server", your connection string ends up something like this:

CUSTOM CONNECT TO "provider=QvOdbcConnectorPackage.exe;driver=mssql;host={host};port={port};database={dbCatalog};Encrypt=false;TrustServerCertificate=false;Trusted_Connection=false;EnableQuotedIdentifiers=1;ReturnSpecificTypeAsOdbcType=yes;"

So my plan is to change all of my existing data connections to use the new (to me, at least) driver. I'll then expect to be able to disable TLS 1.0 completely.

[Troy1]

whats the work around if you are on older version of QlikView how can you connect?  I don't see option of MS SQL Server as an option. we are on version 12.10 SR8

[grangerats]

Addendum

We (still) cannot use the QvOdbcConnectorPackage.exe (driver=mssql) due to the horrible breaking change Qlik introduced years ago (and still has no workaround that I'm aware of).  We continue to be forced to use SQLOLEDB  for connections between QlikSense and MS Sql Server. (Which means Qlik has to have TLS 1.0 Client enabled and the database server needs TLS 1.0 Server enabled in the Windows registry.)

But QlikSense's problems go deeper. Recently, a security audit revealed that there are a set of cipher suites used in TLS 1.2 that are now considered "weak". However, disabling them all caused our data-loading tasks to fail. After a lot of testing, I discovered that the at least one of the following 2 ciphers are required when you use SQLOLEDB in QlikSense (so you need to leave at least one of them enabled on both the QlikSense and database servers):

• TLS_RSA_WITH_AES_256_CBC_SHA
• TLS_RSA_WITH_AES_128_CBC_SHA

Until Qlik acknowledges and corrects the horrible architecture failures they introduced with the QvOdbcConnectorPackage.exe (not just isolated to MS Sql Server), I don't know how people even could possibly use that driver in an Enterprise-level ETL.

[Jio]

Hi everyone,

Is there any folowing up by Qlik.

Due to company policy We need to disable TLS1.0 on our Qliksense servers.

How every when we disable TLS1.0 on the Clint server, the OLEDB connection to the sql server (version 2022).

we got this error.

Error: Connector connect error: IDataInitialize::GetDataSource. HRESULT = 80004005. ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: [DBNETLIB][ConnectionOpen (SECCreateCredentials()).]SSL Security error.
Execution Failed
Execution finished.

The dataconnection uses Microssoft OLEDB.

[MrBruno]

Would this be a solution ?

oledb - Does Microsoft OLE DB Provider for SQL Server support TLS 1.2 - Stack Overflow

[Jio]

This can be a solution but only you don't have other apps that use TLS1.0.

You can also use SQL server connection instead of MS OLEDB. This omit the check of TLS - versions.

try using: [ CUSTOM CONNECT TO "provider=QvOdbcConnectorPackage.exe;driver=mssql;xxxxx] 

This solves our problems.