topic Re: Data Connection to new SQL Server Issue in Connectivity & Data Prep

Data Connection to new SQL Server Issue

MarkRDavies — Wed, 14 Feb 2024 09:51:33 GMT

We are migrating our SQL Server Database to a new server. In attempting to test connectivity to the new Server from Qlik Sense I am running into issues creating a new OLE DB connection. (Our set up is on-Premise Enterprise, connecting to an on-premise SQL Server)

I am creating the connection in the same way that we have done to the current (old) server, using an OLE DB (Provider: Microsoft OLE DB Provider for SQL Server) and the same security details, which have been set up on the new SQL Server as on the old.

However when testing the connection there is a pause for maybe 30 seconds and then a message that the Test failed. No further error details are provided.

So, any ideas where I go from here? I don't have any Network guys to ask at the moment, but I thought perhaps there is a port that needs to be opened on the new SQL Server? Would anybody know if this is so?

Failing that, is there any information I can find as to why the connection is failing?

Appreciate your help

Re: Data Connection to new SQL Server Issue

henrikalmen — Wed, 14 Feb 2024 10:37:07 GMT

My first thought is related to ports, just as you say, or ip-addresses. But only your organisation can know, it depends on your setup. Maybe the sql server machine needs to whitelist the qlik server's ip address. It is likely that you need to investigate such things with the currently absent network guys.

I'd also reccommend to activate detailed logging (debug) in the repository settings in qmc, and then read the logs to try to find more detailed error message.

Re: Data Connection to new SQL Server Issue

NadiaB — Wed, 14 Feb 2024 21:25:55 GMT

Hi @MarkRDavies

If you test the connection from the ODBC Data Source Administrator does it work?

Perhaps you can get a more descriptive error.

Hope it helps!

Re: Data Connection to new SQL Server Issue

MarkRDavies — Thu, 15 Feb 2024 14:17:46 GMT

Hi @NadiaB

Yes I did try this and as you say it did give me a better description of the error ...

Connector connect error: [DBNETLIB][ConnectionOpen (SECDoClientHandshake()]

I can see this is not Qlik Sense related but more of an issue on the new SQL Server I think - I have passed this on to IT Support as it is nothing I can change.

Appreciate your response

Kind Regards

Re: Data Connection to new SQL Server Issue

MarkRDavies — Thu, 22 Feb 2024 10:54:05 GMT

Just to say that I got this resolved once I could get help from some Network resources. Many Thanks for those of you that offered help and advice.