5 Replies Latest reply: Apr 20, 2018 4:25 PM by Granger Godbold RSS

    SQL Server connection error

    Karthikeyan Subramaniam

      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.

        • Re: SQL Server connection error
          Beth Lavey

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

            • Re: SQL Server connection error
              Beth Lavey

              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. 

                • Re: SQL Server connection error
                  Granger Godbold

                  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.

                    • Re: SQL Server connection error
                      Granger Godbold

                      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.