topic ODBC / SQL Server connection. in QlikView

ODBC / SQL Server connection.

stukennedy — Mon, 16 Apr 2018 11:07:04 GMT

Morning.

Please bear with me, I'm not a DBA or developer and going through this quite blind!

QV 12.20.20400.0 on Win 2012r2

QV 11.20.12129.0 on Win 2008r2

Trying to connect to SQL Server 9.0.1399 -sa on Server 2003SP2

We have a new 'Test' server set up with QV12 setup.
I copied documents and data to QV12 from our current QV11 environment.

Copied the QVS settings option by option and get our tasks all running as they should be, minus one last task.

I get this error.

2018-04-16 09:52:45 0073 OLEDB CONNECT TO *** (XPassword is ***)

2018-04-16 09:53:17      Error: Connector connect error: ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: [DBNETLIB][ConnectionOpen (SECDoClientHandshake()).]SSL Security error.

2018-04-16 09:53:17      Connector connect error: ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: [DBNETLIB][ConnectionOpen (SECDoClientHandshake()).]SSL Security error.

2018-04-16 09:53:17      Execution Failed

2018-04-16 09:53:17      Execution finished.

So, I presume this is a connection issue. (Glaringly)
The document calls a txt file, like so...

The connection string is within that file. That file is in the corresponding directory on the new server, as it is in the current live server. This is how it looks.

//SQL server SPSS Connect String

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=True;User ID=**;Data Source=<SERVER>;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=<A VERY OLD SERVER??>;Use Encryption for Data=False;Tag with column collation when possible=False] (XPassword is *********************);

This has not been changed between servers.
QV11 is working fine, connecting as it should

Have I missed a setting elsewhere?
Do I need to allow some form of access for the new server on the SQL server it's grabbing data? If so, how? It seems to allow remote connections from QV11 server.
Is there something missing in ODBC on the new server? It looks the same as ODBC on the QV11 server.

So nothing has changed from server to server in terms of files. I presume I'm missing something somewhere else that I can't see / not sure where I need to look.

Again, apologies, I'm neither a DBA or Developer..

Any help greatly appreciated.

Stu

Re: ODBC / SQL Server connection.

stukennedy — Wed, 18 Apr 2018 13:37:11 GMT

Still no further forward.

Disabled TLS 1.0 on new server as a few websites pointed at that. Here is the error message when I try to add a System DSN on the server (again, nothing visible on existing working server) on ODBC, to see if i can get ANY connection at all.

Re: ODBC / SQL Server connection.

Miguel_Angel_Baeyens — Wed, 18 Apr 2018 13:47:27 GMT

Did you check "Scramble Connect User Credentials" in the script editor > Settings (bottom part)? Also that the DSN is either 32bit (you need to check the Force 32 Bit) or 64 bit (unchecked).

I would recreate the connection in the script so the scrambling happens again.

Re: ODBC / SQL Server connection.

stukennedy — Wed, 18 Apr 2018 13:52:12 GMT

Thanks for the reply, Miguel.

Scramble Connect User Credentials is checked, yes.

The database I'm trying to hit is on 32bit 2003 server.
I'm guessing 32bit ODBC needs to be used on 2012?

Niether 32 or 64bit connect when testing. I get the error above.

Re: ODBC / SQL Server connection.

Miguel_Angel_Baeyens — Wed, 18 Apr 2018 13:58:08 GMT

It could be that the settings in the new server still use TLS 1.0? See for example Unable to connect to SQL Server 2008 using ODBC connection via system dsn on windows 2012 - Stack Overflow and Not able to connect to SQL server : SSL Security error

Re: ODBC / SQL Server connection.

sasiparupudi1 — Wed, 18 Apr 2018 14:03:31 GMT

Try this following

You are using OleDb Provider, which it appears doesn't support TLS 1.2. You need to use ODBC Driver or Native SQL Server Client:

See:

https://support.microsoft.com/en-us/kb/3135244 for the client package(s) you need to install (if not already installed)

and

https://blogs.msdn.microsoft.com/sqlreleaseservices/tls-1-2-support-for-sql-server-2008-2008-r2-2012-and-2014/

As always, it helps to show the actual error message, before jumping the gun and guessing what the problem is.

Re: ODBC / SQL Server connection.

stukennedy — Wed, 18 Apr 2018 15:05:09 GMT

mbaeyens I did see this earlier and tried with little luck.

I've disabled TLS 1.0, 1.1 and 1.2 on the new server to make sure and it still didn't work.

sasiparupudi1‌ Thanks. I'll take a closer look tomorrow. Will those drivers / clients be ok to connect to an SQL 2005 instance? No mention there.

On a 2008r2 server (working) SQLSRV32.DLL is 6.01.7601
The driver on the (NOT working) 2012r2 server SQLSRV32.DLL is 6.0.3.9600

So that should mean there's a server client already on, right?

Re: ODBC / SQL Server connection.

stukennedy — Mon, 23 Apr 2018 12:22:44 GMT

Installed a new ODBC driver and got a bit more of a clearer error.

When the QV task runs, on the SQL server, there is an error like below.

SSL 3.0 and TLS 1.0 enabled on both servers. 1.1 and 1.2 on the 2012 server too.

Re: ODBC / SQL Server connection.

stukennedy — Mon, 23 Apr 2018 13:39:28 GMT

Native Client 10.0 now on both servers and no luck.

Re: ODBC / SQL Server connection.

Bill_Britt — Thu, 31 Jan 2019 16:03:23 GMT

Hi,

The native SQL drivers on the servers only supports TLS 1.0

There are new drivers that you will have to download and install.

Bill