SQL TCP/IP connection issue during Synapse/ADF pipeline

Question

We are moving our data from dataverse to SQL using Synapse Link (via Datalake) and Data Flow pipelines. When our pipeline runs, sometimes we will get the following error. This error seems to happen intermittently, during the DF process, maybe 1 out of every 10 runs.
It doesn't seem to be a firewall issue because it works most of the time. Our Integration Runtime is Azure IR type and our server allows Azure Resources/Services.

Has anyone encountered this and come up with a solution or workaround?

Error:
"message":"Job failed due to reason: The TCP/IP connection to the host has failed. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall."

The details say "shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host " but the 'host' changes between our provisioned SQL server (and port 1433) and a.worker.database server via a redirect from our SQL server (with port 11021)

Answer 1

Hi @Dakota Giltner

There are can be 2 types of timeouts that happens one during query execution another that happens during login or connection establishment, most of the time in my experience the login timeouts are the cause of such intermittent issues, to resolve this problem increase your login timeout and connection timeout values to 30 seconds from 8 or 10 seconds (usually those are default values depending on different clients) and see if that solves this problem for you.
I am repeating again look for login timeout value, not query timeout.

Regards,
Shivam

Answer 2

Hi @Dakota Giltner we are currently having the exact same situation and error as you described above. Can you please provide details of how you resolved it? Thank you!