Data Factory pipeline to Azure SQL Database got Tcp/IP error randomely

Question

TCP/IP error between pipeline to the sink (Azure Database) randomly, and no Audit log when the error happens.

We have a pipeline has been running good for a year, but it failed in random data flow several times a week, after enabled auditing for the Azure Database, I figured the auditing always missing logs for ~1mins when the errors happening.
It looks the SQL database was not accessible during the time, but how can I troubleshoot with it? And is there a way in Dataflow to handle it and retry?

Answer 1

There're tons of audit records, but totally no records at 1:51AM for the whole minute.

Answer 2

Hello @Tim Cui ,

Welcome to the MS Q&A platform.

I believe the TCP/IP error you see is from the Azure SQL database end. Could you please provide us with the detailed error message you are seeing?

Here is a troubleshooting document related to dataflow errors

Can you please go to resource health on the Azure SQL DB and see if you see any error while you are getting the TCP/IP errors on the dataflow?

Error code: DF-MSSQL-InvalidFirewallSetting
Message: 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.
Cause: The SQL database's firewall setting blocks the data flow to access.
Recommendation: Please check the firewall setting for your SQL database, and allow Azure services and resources to access this server.