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.
[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