How can I configure an Azure VM to act as a proxy for connecting to SQL Database over a Service Endpoint?

Question

Currently, Azure does not allow for SQL Databases to be accessed through a Service Endpoint over a VPN Gateway. My idea to circumvent this limitation is to set up an Azure VM to function as a proxy so that all communication with the SQL Database happens through this instance (whose traffic to and from the SQL Database can then be routed over a Service Endpoint). However, I have been unable to get this solution working and could use some guidance.

My Setup:

I have an AWS environment set up with VPN connectivity established to my Azure environment. I have a Private Hosted Zone set up using Route53 to resolve the domain name of my SQL Database to the public IP address of the corresponding Microsoft.Sql regional endpoint (this is the IP address the domain name of my SQL Database resolves to when I access the SQL Database from my proxy VM). My route table is configured to forward traffic to this IP address through my Virtual Private Gateway.

On the Azure side, I have the route table of my gateway subnet configured to forward traffic destined for the Microsoft.Sql regional endpoint public IP address to my proxy VM as if it were a virtual appliance. The network interface of the proxy VM is set to allow IP Forwarding. The route table attached to the subnet of the proxy VM is configured to route traffic destined for the private CIDR of my AWS VPC back through the VPN Gateway.

For simplicity, the security groups in both the AWS and Azure environments are set to allow all inbound and outbound traffic between the private IP addresses of both environments and the Microsoft.Sql regional endpoint public IP address.

What currently works:

My EC2 instance in the AWS VPC can ping and ssh into my proxy VM over the VPN using its private IP address. The proxy VM can access my SQL Database over the Service Endpoint using its private IP address.

What doesn't work:

I can not successfully ping or ssh into the proxy VM from my EC2 instance by attempting to connect to the Microsoft.Sql regional public IP address (the one I've configured to be forwarded to the proxy VM) nor the domain name of my SQL Database (the record I set up in Route53 for the Microsoft.Sql regional public IP address). When I perform a packet capture on the proxy VM, I see no inbound traffic from my EC2 instance. The traffic shoes up as accepted in my VPC flow logs.

I understand that this is what SQL Database Managed Instances are for, however I don't have the option of using that service.

I currently have not configured any forwarding with iptables nor done any special host setup on the proxy VM, as I first wanted to see the network traffic show up in the packet capture and then verify that I can successfully connect to the proxy VM instance before attempting to set up any kind of forwarding to the SQL Database.

Moreover, is this type of solution to circumvent the limitations of Service Endpoints in Azure even possible? Is there a simpler method?

Thanks and best wishes.

Answer 1

My route table is configured to forward traffic to this IP address through my Virtual Private Gateway.

I don't think there is a possibility of this specific approach working, because...

I have the route table of my gateway subnet configured to forward traffic destined for the Microsoft.Sql regional endpoint public IP address to my proxy VM as if it were a virtual appliance.

This route table applies to traffic originating in that subnet, and that isn't where the traffic originates.

You need to tunnel this traffic between the two machines, not just route it, because the source and destination addresses that the network sees need to be the addresses of the individual VMs. That's part of what a tunnel does -- speaking generically and over-simplified, a tunnel wraps traffic from/to hosts with addresses a and b inside outer packets with from/to hosts with addresses x and y so that intermediate networks and gateways need not understand the "real" source and destination. Platform limitations probably make it impossible to simply route and forward this traffic as unencapsulated IP traffic with source and destination intact. (Or, without a proper tunnel, you'd need to NAT or double-NAT the traffic on the instances on both ends.)

There are a number of tunnel solutions operating at different layers, including openvpn and HAProxy, but the simplest -- for proof-of-concept purposes, at least -- is an SSH tunnel.

From the EC2 instance, assuming the database is using TCP port 1433:

$ ssh -L 1433:private-ip-of-db:1433 -i keyfile.pem username@ip-of-azure-vm

With this SSH connection open, there is also a socket listening to TCP port 1434 on the EC2 instance, and any connection to the private IP of the EC2 instance on that port will be tunneled across the open SSH connection and relayed to the database. The database will see the source IP of the connection as being the IP of the azure VM... so security settings need to allow traffic accordingly, and you wouldn't use the public IP address you discussed -- you'd use the EC2 instance's IP and control access via the EC2 security group, and you'd need to give the Azure VM access to the database. From the perspective of anything accessing the database, the EC2 instance appears to be the SQL Server.

SQL Server may offer one or more gotchas that requires more ports or a different port than I've shown above, but the general idea here is solid -- I've used this strategy for other protocols like RDP (TCP port 3389) and MySQL (TCP port 3306) but don't recall ever trying it with MSSQL.