question

KanhaiArora-9745 avatar image
0 Votes"
KanhaiArora-9745 asked ·

SQL Connection Error - Firewall - Integration Runtime

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=,'

azure-data-factoryazure-sql-database
· 1
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

KranthiPakala-MSFT avatar image
0 Votes"
KranthiPakala-MSFT answered ·

Hi @KanhaiArora-9745,

Following up to see if the above details from @SSengupta-4080 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://docs.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
    71994-image.png

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.



image.png (19.3 KiB)
· 6 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi all,

As per our initial investigation we have got to know that the destination Azure SQL Server is switching its IP address to two different IPs. if the destination resolves to one of them , it works because the IP is within internet Firewall range to allow traffic by network team but if it resolves to other IP the request fails.
Hence, need to check with Microsoft, if any IP allocation change has happened.

0 Votes 0 ·

Hi @DeshpandeAditiU-3510 ,

Could you please confirm if it is SQL Server on Azure VM or Azure SQL Database?

0 Votes 0 ·

Hi @KranthiPakala-MSFT , This is an Azure SQ DB.

0 Votes 0 ·
Show more comments
KranthiPakala-MSFT avatar image
0 Votes"
KranthiPakala-MSFT answered ·

Hi @DeshpandeAditiU-3510 , @KanhaiArora-9745,

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


· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@DeshpandeAditiU-3510 ?

Just checking to see if the above information was helpful to resolve your issue? Let us know if you have further query.

Thank you

0 Votes 0 ·