Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

stukennedy
New Contributor

ODBC / SQL Server connection.

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

qv1.JPG

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

9 Replies
stukennedy
New Contributor

Re: ODBC / SQL Server connection.

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.

ODBC error.JPG

Re: ODBC / SQL Server connection.

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.

stukennedy
New Contributor

Re: ODBC / SQL Server connection.

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.

sasiparupudi1
Honored Contributor III

Re: ODBC / SQL Server connection.

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

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

stukennedy
New Contributor

Re: ODBC / SQL Server connection.

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?

stukennedy
New Contributor

Re: ODBC / SQL Server connection.

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

ODBC error 2.JPG

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

schannel.JPG

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

Highlighted
stukennedy
New Contributor

Re: ODBC / SQL Server connection.

Native Client 10.0 now on both servers and no luck.

Support
Support

Re: ODBC / SQL Server connection.

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

Bill - Designated Support Engineer at Qlik
If a post helps to resolve your issue, please mark the appropriate replies as CORRECT.