Unable to connect to Azure SQL

Question

Looking at some of the runs in ADF there are some fails where its states it is unablet to connect to the Azure SQL database and its advising me to check the password. I know the password is correct as you can see on subsequent runs that the pipeline runs. Why is this happening and being cynical is this Microsoft forcing users to re-run something adding additonal cost to the user?

Answer 1

I think that it’s just the sql gw dropping the auth hop for a sec and u can add a tiny retry on that ADF step and it’ll smooth out.

Answer 2

Hi Andy Roberts,
Thankyou for reaching microsoft Q&A!
you’re absolutely right , if the password or authentication were incorrect, the pipeline would fail consistently rather than randomly. Since later executions succeed using the same credentials, this indicates that authentication is working correctly and the failures are more likely caused by intermittent connectivity or transient platform conditions, not an invalid password.

In Azure Data Factory, when a connection attempt cannot be completed, the service may return a generic authentication or password-related error even if the real cause is different. Temporary network interruptions, firewall refresh, public network access restrictions (such as error 47073), brief Azure SQL resource pressure, token or session refresh, or short-lived backend events can all interrupt the connection momentarily. When the transient condition clears, subsequent runs succeed, which matches the behaviour you’re observing.

This is expected behaviour in distributed cloud environments and is not designed to force reruns or increase customer cost. As a best practice, configuring a retry policy on ADF activities helps absorb these short-lived failures automatically and reduces the need for manual reruns. If needed, we can also review firewall configuration, integration runtime connectivity, authentication method, and SQL resource utilization to identify any intermittent factors contributing to the issue.

Answer 3

There are several reasons why you might encounter connection issues with Azure SQL Database in Azure Data Factory (ADF), even if the password is correct. Here are some potential causes and recommendations:

1. Firewall Settings: Ensure that the Azure SQL Database firewall allows the ADF integration runtime to access the database. If the error message indicates that the client IP address isn't allowed, you may need to enable the Allowed Azure services and resources to access this server option in the Azure SQL Server firewall configuration.
2. Public Network Access: If the error message contains "SqlErrorNumber=47073", it means that public network access is denied. You should set the Deny public network access option to No in the Azure SQL firewall settings.
3. Transient Issues: Sometimes, connectivity issues can be transient due to network instability. In such cases, consider adding a retry policy in your ADF activity to handle temporary failures.
4. SQL Error Codes: If you encounter specific SQL error codes, consult the Azure SQL troubleshooting guide for more detailed recommendations based on the error code.
5. Service Principal Permissions: If you are using service principal authentication, ensure that the service principal has the necessary permissions in the database to connect successfully.

These issues can lead to intermittent connection failures, and while it may seem like a cost-increasing factor, they are typically related to configuration settings rather than intentional design by Microsoft.

---

References:

• Troubleshoot the Azure Synapse Analytics, Azure SQL Database, SQL Server, Azure SQL Managed Instance, and Amazon RDS for SQL Server connectors in Azure Data Factory and Azure Synapse
• Troubleshoot the Azure Synapse Analytics, Azure SQL Database, SQL Server, Azure SQL Managed Instance, and Amazon RDS for SQL Server connectors in Data Factory in Microsoft Fabric