SQL Connection Error - Firewall - Integration Runtime

Question

We are getting below error while trying to establish the connection with Azure SQL DB. Error is as below. the error is intermittent in nature.

ErrorCode=SqlFailedToConnect,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Cannot connect to SQL Database: '*****.database.windows.net', Database: 'Database Name', User: ''. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Data.SqlClient.SqlException,Message=A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.),Source=.Net SqlClient Data Provider,SqlErrorNumber=10061,Class=20,ErrorCode=-2146232060,State=0,Errors=[{Class=20,Number=10061,State=0,Message=A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.),},],''Type=System.ComponentModel.Win32Exception,Message=No connection could be made because the target machine actively refused it,Source=,'

Answer 1

Hi @Kanhai Arora ,

Following up to see if the above details from @S.Sengupta was helpful? If that doesn't help, could you please let us know what is the Integration runtime being used - SHIR or Azure IR ? Also please check if your Azure SQL has firewall enabled? If yes, then you will have to whitelist the Integration Runtime (SHIR) machine IP address. If it is Azure IR you can do 2 things.

1. Please try whitelisting Azure Integration Runtime IP addresses : https://learn.microsoft.com/azure/data-factory/azure-integration-runtime-ip-addresses#azure-integration-runtime-ip-addresses-specific-regions
2. Allow Azure services and resources to access the SQL server
   

Hope this helps. Let us know how it goes.

----------

Thank you
Please do consider to click on "Accept Answer" and "Upvote" on the post that helps you, as it can be beneficial to other community members.

Answer 2

Hi @Deshpande, Aditi U , @Kanhai Arora ,

Based on your inputs that the IP Address change was on the Azure SQL Database service, and I believe you are using SHIR in which the firewall of the SHIR was open to a specific IP address for which your connection was successful, and when that address changes on the Microsoft side, you are seeing this connectivity issue.

Based on this info, I did some investigation internally and found that as Azure infrastructure improves, Microsoft will periodically refresh hardware to ensure we provide the best possible customer experience. In the coming months, product team plan to add gateways built on newer hardware generations, migrate traffic to them, and eventually decommission gateways built on older hardware in some regions.

Customers will be notified via service health notifications well in advance of any change to gateways available in each region. Customers can use the Azure portal to set up activity log alerts.

The most up-to-date information will be maintained in the Azure SQL Database gateway IP addresses table.

Related MS document: Azure SQL Database traffic migration to newer Gateways

Resolution Recommendation/workaround:

In order to overcome the issue you are experiencing, it is recommended to try opening SHIR firewall using domain name of SQL rather than specific IP address and let us know how it goes.

Hope this information helps

Answer 3

This is still an issue. Using "AutoResolveIntegrationRuntime" is not a viable option.