Azure Synapse Analystic pipeline: The TCP/IP connection to the host has failed

Trinh Quang Dat 91

I have a pipeline that calls to execute pipeline activity each date from a date range which is input as parameters. But I don't know why I got an issue below while my SQL pool still queries successfully in SSMS.

Operation on target DF_XX failed: {"StatusCode":"DF-MSSQL-InvalidFirewallSetting","Message":"Job failed due to reason: at Source 'sourceXXX': 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.","Details":"java.lang.RuntimeException: Cannot connect to SQL database: 'jdbc:sqlserver://xxx-001.sql.azuresynapse.net;database={xxx_001}', 'Managed Identity (factory name): xxx-001'.[SQL Exception]Error Code:0, Error Message: The TCP/IP connection to the host xxx-001.sql.azuresynapse.net, port 1433 has failed. Error: \"connect timed out. Verify the connection properties. 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.\"., error stack:shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:234)\nshaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerException.ConvertConnectExceptionToSQLServerException(SQLServerException.java:285)\nshaded.msdataflow.com.microsoft.sqlserver.jdbc.SocketFinder.findSocket(IOBuffer.java:2466)\nshaded.msdataflow.com.microsoft.sqlserver.jdbc.TDSChannel.open(IOBuffer."}

RunID: 4ba2d4bb-c810-4d88-9337-e852b91fbc92

KranthiPakala-MSFT 46,462 Reputation points Microsoft Employee

2022-09-16T00:09:39.847+00:00

Hello @Trinh Quang Dat ,

Thanks for the question and using MS Q&A platform.

From the error message it seems like a SQL firewall networking issue. Since you mentioned that the connection from SSMS is fine I don't think it is an issue with port 1433.

Please refer to this troubleshooting guide: https://learn.microsoft.com/en-us/azure/data-factory/data-flow-troubleshoot-guide#error-code-df-mssql-invalidfirewallsetting

As per the above recommendation from the documentation, please make sure to allow Azure resources to access your SQL server.

Alternate 1: In case if you are using Default IR (auto resolve) in your dataflow with default IR and your organization/networking team doesn't allow you to enable Allow Azure Services to access your SQL server in networking settings, then please try creating an Azure IR with specific region and whitelist that region Azure IPs to resolve the issue.

Alternate 2: If you can't whitelist the Azure IP rage for your Azure IR region, then other alternate would be to create private endpoints and use them to establish secure connection to your Azure SQL.

Hope this info helps. Let us know how it goes.

Thank you
Trinh Quang Dat 91 Reputation points

2022-09-16T03:48:00.563+00:00

Hi @KranthiPakala-MSFT ,
Thank you for your answer, but my issue is not your mentioned above. Because other pipelines run successfully, and in for each loop, some pipeline run success, and some failed with that error.
And in the same loop, after some pipelines get that error, the next pipeline still runs successfully.
KranthiPakala-MSFT 46,462 Reputation points Microsoft Employee

2022-09-22T06:23:06.583+00:00

Hello @Trinh Quang Dat ,

Thanks for your response. It is strange that it works for few iterations and fails for few. I would recommend filing a support ticket for deeper investigation of the root cause. If you have already filed a support ticket, could you please share the SR# with us for tracking it.

In case if you don't have a support, plan and blocked because of this issue, please let us know so that we can check on alternate options to unblock you.

We look forward to your confirmation.
KranthiPakala-MSFT 46,462 Reputation points Microsoft Employee

2022-09-27T21:27:32.443+00:00

Hello @Trinh Quang Dat ,

Just checking to see if you have got a chance to file a support ticket for deeper investigation. In case if you have already filed a support ticket, could you please share the SR# with us here for tracking it.

Thanks

Share via

Azure Synapse Analystic pipeline: The TCP/IP connection to the host has failed

Your answer