Azure Data Factory IP Address Issue

Question

In the application we are using Azure SQL Server to Store Enterprise data, we have 2 databases let’s say DB1 and DB2, as per requirement we have to copy some data from DB1 to DB2 using Azure Data factory. This ADF we have configure in such a way that it gets the DATA from DB1 from View and storing it in DB2 Table. Now as the ADF is PAAS service it don’t have any static IP, so every time it runs it fails saying not able to connect to SQL Server, then based on that Error message we get IP Address of ADF and allow that in SQL Server firewall and it execute correctly. Later in next schedule it fails again because the ADF IP we allowed in SQL Server firewall settings is changed, then we need to allow all big range of IPs to make the ADF run correctly.

As this is enterprise client we can not allow a big range of IP in SQL Server Firewall else it will be a big security hole.

Any workaround to set the static IP of ADF and only allow one IP in SQL Server is needed.

Answer 1

The IP address of a logical Azure SQL database server is not static as it can change at any time. All connections should be made using the fully qualified DNS name rather than the current IP address of the SQL Azure server.

To avoid adding firewall rules you will have to configure your SQL Azure servers to “Allow access to Azure services” although it essentially opens up access to anything hosted in Azure.

Currently people is voting for adding static IP address to ADF as you can see here.

Both services, SQL Azure and ADF, use dynamic IP addresses at this time.

Hope this helps.

Answer 2

If you have Azure App/Web Service running on your portal there is a way to trace all IP addressing landing at your web page/app. Same way to trace ADF IP - if you send a GET request from Azure Data Factory (using Azure Web Activity) to your website you will be able to read from what Public IP azure data factory is coming from.

To do this -

1. Create Azure App Service on the portal ( almost any spec will do for now).

2. Go to your Azure App Service and look for Diagnostic Log or App Logging Settings then enable that. Now go the Logs view. At this point, you will be able to pick up the public IP address of anyone requesting your page.

3. Go to Azure Data factory use the Web Activity and type your web app/site address under the setting. Change the Web Activity request type to GET. Then Publish the pipeline and trigger the Activity.

4. if you go to the Logs view window of the App Service, you should see the IP of the ADF from which the GET request was sent.

Answer 3

I am not sure when this came out, but in ADF V2 when you try to make a new MySQL Linked Connection it will tell you the IP in the error message now.