Unable to connect to Azure SQL server through Point-to-Site VPN

Question

Is there any possibility to connect to an Azure SQL server through Azure's P2S VPN client?

More specifically, I want to force clients connected through Azure's S2P VPN client to use the VPN to connect to the DB server. One of the reasons is that the Clients' IP addresses change frequently and lots of (temporary) firewall rules need to be created to grant access to these clients. Assume that there are several employees with changing IP addresses around the globe who need to directly access the DB.

The problem is that when accessing a database server, Azure needs the connection to be made using the SQL server's FQDN otherwise, i.e. just by IP address, it will fail. (See [1])

This is the current configuration:

• Virtual Network with a 10.5.0.0/16 address space
• Network interface attached to private endpoint for the SQL server in the same subnet
• Virtual network gateway which also is the VPN endpoint (route-based VPN)
• Clients connecting through Azure's P2S VPN client get an address of the 172.16.0.0/24 space

There are three options I see here:

• Make the FQDN resolve to the SQL server's VN interface IP address when the Azure VPN client is connected.
• Route the connection through the VPN and use the VPN gateway's public IP address to connect to the SQL server.
• Create a DNS alias for the SQL server's private/VN IP address

The first solution works when I manually put a rule in the client's hosts file to resolve the SQL server's FQDN to its VN private IP address. Considering quite an amount of clients connecting, this is not a solution for obvious reasons. Is there any way to propagate such a DNS rule through Azure's VPN? Where would I need to configure the name resolution (some Azure DNS server?) and propagation (of this DNS server through VPN)? DNS propagation through the VPN client can be configured in the client's configuration XML file by adding a DNS server there (see [6]). The problem is that I don't have a DNS server for the Virtual Network in Azure, that's what the private DNS zones are supposed to be used for, but they don't have a DNS IP address.

Regarding the second idea I managed to set up a manual route as described here: [2] to route traffic to the SQL server through the VPN. The connection is clearly made through the VPN (as I can see on Wireshark) but it ends up at the SQL server with the client's public IP address - which in the end does not make any difference. The public IP address seems to be encapsulated while the connection is being made through the VPN. As far as I have seen it does not seem possible to route all or some internet traffic through the Azure VPN. But is there a way to configure routing in such a way that clients connected to the VPN client end up there with any static IP or range that can be filtered at the SQL server's firewall? Would I have to set up some kind of virtual network NAT (as described here [3]) This would be quite a weird setup though.

Also I have tried setting up a private DNS zone to override the public DNS entry for mydatabase.database.windows.net (not the privatelink.database.windows.net!) See [4], [5]. However, these private DNS records are not being propagated through the VPN client by any means.

[1] https://docs.microsoft.com/en-us/azure/azure-sql/database/private-endpoint-overview#check-connectivity-using-sql-server-management-studio-ssms

[2] https://docs.microsoft.com/en-us/azure/vpn-gateway/vpn-gateway-p2s-advertise-custom-routes

[3] https://docs.microsoft.com/en-us/azure/virtual-network/nat-overview

[4] https://docs.microsoft.com/en-us/azure/azure-sql/database/dns-alias-overview

[5] https://docs.microsoft.com/en-us/azure/private-link/private-endpoint-dns#virtual-network-workloads-without-custom-dns-server

[6] https://docs.microsoft.com/en-us/azure/virtual-wan/virtual-wan-about#how-do-i-add-dns-servers-for-p2s-clients

Answer 1

For anyone else who might run into this issue, I want to highlight the OP's "first solution" ("Make the FQDN resolve to the SQL server's VN interface IP address when the Azure VPN client is connected.") and observation that "The first solution works when I manually put a rule in the client's hosts file to resolve the SQL server's FQDN to its VN private IP address."

Ultimately, for your point-to-site connection to your Azure SQL Server to succeed, you need your Azure SQL Server's fully qualified domain name (FQDN) resolve to the correct private IP address for the Azure private endpoint. And of course your VPN connection to Azure must be active.

Since I found the steps below far from obvious, here's a breakdown of what to do.

First, get the FQDN of the Azure SQL Server instance. You can get this from the "Properties" page for the server in the Azure portal:

Let's assume the Azure SQL DB server's FQDN is myowndbserver.database.windows.net.

Next, you need the private IP address for the Azure Private Endpoint you (presumably) already created. To get this, within the Azure portal, navigate to the Private Endpoint's "DNS Configuration" page: In this example, the private IP address is 10.22.0.4.

From a command prompt (cmd.exe), you can ping the SQL Server's FQDN to see what IP address it resolves to. Chances are that it will (initially) resolve to the server's public IP address: ping myowndbserver.database.windows.net. (You can also use nslookup -q=A -recurse myowndbserver.database.windows.net 8.8.8.8 to get details of the DNS name resolution.)

If myowndbserver.database.windows.net resolves to 10.22.0.4 (and your VPN is active), your SQL database connection should work. If it resolves instead to a public IP address, the easiest way to force it to resolve to the desired private IP address is to add an entry to the computer's hosts file (located in C:\Windows\System32\drivers\etc\hosts (or %windir%\System32\drivers\etc\hosts) such as:

10.22.0.4 myowndbserver.database.windows.net 

Of course, this must be configured for each computer that will use the VPN connection. And if you use hosts, the name will resolve to the private IP address whether or not the VPN connection is active.

The OP wanted a way to resolve the name to the private IP address only when the VPN connection was active. Here's a way to do this from serverfault.com. Basically, this method dynamically modifies the user's hosts file, so it simply automates the manual step described above. And it too requires configuration for each computer that will use the VPN connection, so it's cumbersome to deploy for many users.

Answer 2

The simplest solution to this is to use service endpoints. Have your users connect to teh VPN to route traffic through the virtual network, then turn on Service Endpoints on your SQL server to only allow traffic from that virtual network.