Azure Data Factory - Site-to-Site VPN Connection

Question

I have 2 cloud environments AWS and Azure. In AWS, I have a SQL Server instance inside a VPC (192.168.0.0/24). In Azure, I have a VNet (10.0.0.0/24). Both these 2 Virtual Network are connected through Site-2-Site VPN. I have tested connectivity to SQL server on AWS (192.168.0.234) from Azure VM inside Azure VNet. In Azure I have Azure Data Factory instance. From ADF, i need to access the SQL server instance within that AWS VM to run a Data Flow. How should I achieve this?

I already created a site-2-site VPN and test the SQL server conenctivity from Azure to AWS via SQLCmd utility running on Azure VM inside Azure VNet. Created internal load balancer (frontend IP: 10.0.0.4) while pointing traffic to AWS VM as a backend pool. Load balancer doesn't route the traffic to AWS VM SQL server.

This what I'm trying to achieve similarly based on Microsoft Document. Instead On-prem SQL server, I have AWS SQL server behind a VPC. https://learn.microsoft.com/en-us/azure/data-factory/tutorial-managed-virtual-network-on-premise-sql-server

What's already being tried:

1. Azure Self-Hosted Integration Runtime - This way i can install self-hosted IR on the AWS environment, which allow me to securely connect ADF to SQL Server DB in AWS VNet through SQL connector. But it only support simple data movements. Copy Tasks. ADF data flows does not support.
2. WhiteListing Azure IPs on AWS VNet - This way i can allow all the Azure ADF used IP ranges in AWS VNet. With this ADF IR environment can access the AWS SQL server DB. But i don't think enabling such number of Azure Public IPs in Prod environment is practical and secure.

Answer 1

Agreed with Nick comment. You won't be able to achieve such a design with an Azure LB and backend outside of that vnet.

A schema as asked by Nico would be great because i don't see the point of pointing an LB on Azure side to take care of a VM on the AWS side.

I'm not an expert of ADF but is this what you are trying to achieve : https://learn.microsoft.com/en-us/azure/data-factory/connector-sql-server?tabs=data-factory

Worth looking at the integration runtime ? https://learn.microsoft.com/en-us/azure/data-factory/create-self-hosted-integration-runtime?tabs=data-factory

Answer 2

It seems like you have created a load balancer in Azure.

The backend pool in Azure has some limitations, one being that backend resources must be in the same VNET as the LB.

I suggest instantiating the load balancer on the AWS side, which should solve your connectivity issue.