Azure SQL Network Firewall & Private Endpoint

Question

I have a requirement in my project to pipeline the data from Azure Storage to Azure SQL database. We are using Azure Data Factory (ADF) V2 to perform this pipelining work.

In order to have secure access to Azure SQL DB, I have configured the following properties first.

1. Deny public network access: No
2. Connection Policy: Default
3. Allow Azure services and resources to access this server: No

With the aforementioned settings, the firewall rules have been configured to whitelist a few Public IPs and Virtual Network subnets.

This setting works fine when I use a DB client like SSMS inside a Virtual Machine (part of the subnet already whitelisted). However, it doesn't work when I am trying to create the linked service from ADF. I don't want to turn on this feature "Allow Azure services and resources to access this server" due to security risk to allow ADF connect to the DB. Instead, I have created a Private Endpoint from ADF to SQL Database and approved it but still kept "Deny public network access" --> No.

With the above setup, I have entered into an issue. i.e. The VM which has been successfully connecting to the DB Server doesn't connect to the database server now after enabling Private Endpoint for ADF.

Question:

May I please check what's wrong with this setup? According to MS documentation,

When "Deny public network access" setting is No (default), customers can connect by using either public endpoints (with IP-based firewall rules or with virtual-network-based firewall rules) or private endpoints (by using Azure Private Link), as outlined in the network access overview.

https://docs.microsoft.com/en-us/azure/azure-sql/database/connectivity-settings

I have configured Private Endpoint for ADF and VNet firewall for public endpoint access through DB client from a VM. But, only one works at time. To make VM client connect to the DB, I had to delete Private Endpoint of ADF whereas to make ADF work, I had to enable private endpoint which disables the DB connectivity from the VM.

Answer 1

Once you enable private end point for SQL then public network access stops working. Hence, that is the reason you are unable to connect from VM. Kindly consider using private end point to connect from your VM as well.

Now, Add both the (ADF and VM ) subnets to the SQL Server >> Firewalls & Networks >> Under the Add existing virtual Network

Create a private Endpoint in the Azure Data factory having the same subnet as the VM.

Hence, Could add the Azure SQL DB as a linked service in Azure Data Factory:

Here is my MyVNET where I have the created the Subnet(VM) and ADFSubnet which has been added to SQL DB existing Virtual networks via which it can be connected to the ADF or with the VM.

Hence, if you create 2 private endpoints for respective services in the same managed Virtual network you will be able to connect.