I'm having issues with my Azure Data Factory (ADF) pipeline where it fails to connect to my Azure SQL database when triggered on a schedule. The error indicates a connection problem, specifically stating it cannot connect to the SQL database. Interestingly, when I manually trigger the same pipeline, it runs successfully. This scheduled run failure is confusing, especially since I didn't change any configurations that would block access for the user the pipeline uses. I checked everything from the linked service configuration to the SQL Database firewall settings and all seems fine. Just to give some context, this specific part of my pipeline involves a lookup that retrieves information from a table in Azure SQL, which identifies the tables and source systems for staging. The only noteworthy change I made recently was adding new users to the database, but it shouldn't affect the user credentials ADF is using. I also switched to an S0 pricing tier; while this allowed the pipeline to run, execution time has drastically increased from 5 minutes to 60. I'm wondering if the firewall rules could be the culprit or if there might be any other insights into why the scheduled runs are failing while manual runs succeed.
1 Answer
Have you checked the health status or logs of your Azure SQL DB? If it's in a serverless tier, it might go to sleep and cause these issues when the pipeline is scheduled. Manually triggering it could wake it up just before execution, making it work. Also, verify if your subscription limits are impacting the connection during scheduled runs.
Yes, it's serverless, and I checked the logs that indicated I reached my free tier offer budget. I resolved that earlier by switching to a paid plan. It's strange because other parts of the pipeline ran without issues using the same database and user.