Connection Timeout Error when Connecting to SQL Server

[nabeelaslam1994]

Hello Team,

I am getting an error message when qlik replicate task runs and tries to pull the data. I see a successful connection when connecting from the server where qlik replicate is installed and using same credential. Here is the error below:

1. I had pinged the server and there are no drops. Tried traceroute and see no timeouts.

2. I already have another task that is pulling data from a different database within the same server but there is no issue with it. It is using a different set of credentials.

Here is the error below:

Failed to connect
RetCode: SQL_ERROR SqlState: 08001 NativeError: 258 Message: [Microsoft][SQL Server Native Client 11.0]TCP Provider: Timeout error [258].

Is there a resolution to solve this?

Nabeel

[PGN]

Have you verified the credential your trying to use for this task has access to the database?  You stated you have another task using a different credential.  Also, it may have connect access, but does it have datareader?  Also, if transactional replication is needed, it will need sufficient rights to create the SQL publication.

[nabeelaslam1994]

 It has dbreader and dbowner access. It runs for a little bit and show data getting pulled then after sometime it fails and gives timeout error.

Nabeel

[Heinvandenheuvel]

[update: - more information came in while i wrote my reply. It seems it connects to the endpoint fine.  So the  question as to 'where it is' is all the more important. If need be switch to LOGGING SOURCE_CAPTURE TRACE. Or ask SQL Server what the current query is. SELECT FROM FN_DUMP_DBLOG ? DN_DBLOG? - Maybe the cdcTimeout internal parameter needs to be increased from default 600 (10 minutes)  ]

>>> Failed to connect
RetCode: SQL_ERROR SqlState: 08001 NativeError: 258 Message: [Microsoft][SQL Server Native Client 11.0]TCP Provider: Timeout error [258].

At what stage do you get this message? In which log?

Can you successfully 'test' the endpoint connection under 'manage Endpoint Connections' ? - note: That just _has_ to be 'green' for a task to have a chance. If 'test' fails the detailed errors will be in the general 'repsrv.log'

If it fails during task activation can you share with us the 4 or 5 lines from the reptask_xxx.log just before the error to provide full context - WITH timestamps please to verify how long the timeout was.

Can you carefully compare the (source?) endpoint JSON from the  task exports for the task which does work with the one which doesn't? Perhaps share those endpoints (in TEXT, not Screenshot) here if it is too confusing? If everything looks the same, except the username/password, well then it must me a username credential issue on the endpoint db right?

>>  had pinged the server and there are no drops.

Can you use the failing credentials with a non-Replicate ODBC connection method, from the Replicate server. Maybe the SSMS?

Hein.

[PGN]

If it has dbowner, then dbreader is superfluous, but I digress.  What's the volume/complexity of the data?  Is it a straight table query or is a view or filtering involved?  In the case of filtering or a view, is there any resource governor enabled on the server?  

[Bill_Steinagle]

@nabeelaslam1994 

Hi, as per the updates to this post have you also checked on the Remote Query parameter on this Source SQL Server environment as per the one update with the resources? As noted the 600 / 10 minute timeout for this setting per the below info. See below for the 2 internal parameters which you can set to 3600 and rerun the use case. 

https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-remote-query-t...

As suggested increasing the cdcTimeout and executeTimeout to 3600 and rerun the Task. 

Thanks,

Bill

[nabeelaslam1994 · Accepted solution]

For this, I connected with Network Team and after disabling ARR, the problem went away. It was routing issue.