azure-sql-databaseazure-data-factory

Unable to connect to Azure SQL from Azure Data Factory


I have setup a new Azure SQl and Azure Data Factory resource under the same Azure resource group.

However, I am unable to connect to Azure SQL from Azure Data Factory. I was able to do this in my previous place.

I am getting the error message below when I try to connect to Azure SQL from Azure Data Factory.

ErrorCode=SqlFailedToConnect,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Cannot connect to SQL Database. Please contact SQL server team for further support. Server: 'xxxxxxxxxx.database.windows.net', Database: 'xxxxxxxxx', User: 'xxxxxxx'. 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=Cannot open server 'sqlserver-represent-prod' requested by the login. Client with IP address '51.xxx.xxx.xxx' is not allowed to access the server. To enable access, use the Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect.

However, when I add the firewall rule for the IP address in Azure SQL Server connectivity, it works. After a while I am getting the same error message but with a different IP address. In my previous place, I ddint have to add the firwall rule and it worked fine.

What could be the problem?


Solution

  • Tick the check box "Allow Azure Services and Resources to access this server" under Exceptions.

    You can find this option under Firewall rules.