Data Factory Pipeline to Azure SQL Server - fails on firewall IP

Question

Hi Team,

I'm not sure what I've done wrong here.

• I've got an Azure Data Factory pipeline that copies data from an on-prem SQL Server (using the Self-Hosted Integration Runtime) up into a database running on an Azure SQL Database.
• I can grab the source data fine, and I can see the sink data tables.
• And yes, I do have the "Allow Azure services and resources to access this server" option enabled on the Firewall Settings of the Azure SQL Server.

When I run the Data Factory pipeline, it fails with the following error:

Operation on target Copy Audit Table failed:
ErrorCode=SqlFailedToConnect,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Cannot connect to SQL Database: 'MYDATABASE.database.windows.net', Database: 'THERIGHTDATABASENAME', User: 'THECORRECTUSER'. 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 'SERVERNAME' requested by the login. Client with IP address 'WRONGIP' is not allowed to access the server. To enable access, use the Windows 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

When I add that IP Address specifically to the firewall on the Azure SQL Server, everything works - the pipeline executes as expected.

However I don't think I should need this - if I've got the Allow Azure Services option enabled, then the Data Factory should be able to talk to the Azure SQL Database directly without needing a specific exemption in the server firewall, correct?

It's worth noting that when I configure my Datasets in Data Factory, they test fine without the specific firewall rule in place. I select the Dataset that is the Linked Service of the Azure SQL database, and click Test connection, and it returns straight away with Connection successful. I can retrieve the list of tables within the database - it's all good.

It's as if the Azure SQL Server isn't respecting the "Allow Azure services and resources to access this server" setting. However if I disable this option on the server's firewall tab, the Datasets start to fail their Test connection. Turn it back on, and the datasets test successfully again. So clearly that option on the server is doing something.

As soon as I put the IP address that the Data Factory is currently using (from the error message) into the SQL Server firewall, it works perfectly.

Have I misunderstood how the pipeline execution happens wrt the Azure SQL Server firewall? Do I need to create a Private Endpoint on the SQL Server?

Cheers,
Matt

Answer 1

Hi @Matt Russell ,

Thanks for your patience.

I have discussed with Product team and got the details. As per below snap from documentation, the copy activity uses SHIR for connecting to both Source and Sink even though source is only connected to SHIR. Hence, the IP address error you are receiving is your host machine IP and that needs to be added to Azure SQL Firewall. When using test connection, only the integration runtime selected specific to dataset is used and hence it is showing as successful.

Product team agreed to enhance the error message to make it more easier to understand the issue and you will be able to see updated error messages in such scenarios in future.

Hope this helps! Please let us know for further queries and we will be glad to assist.