Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

Highlighted
karthiks_dwbi
Contributor III

SQL Server connection error

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.

Labels (1)
4 Replies
Partner
Partner

Re: SQL Server connection error

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

Partner
Partner

Re: SQL Server connection error

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. 

Partner
Partner

Re: SQL Server connection error

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.

Partner
Partner

Re: SQL Server connection error

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.