0

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

Aileron79
  • 259
  • 1
  • 7

1 Answers1

1

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.

Sam Cogan
  • 38,158
  • 6
  • 77
  • 113
  • Thanks for the suggestion, I did not mention the endpoints - They actually are in place (will update question), the "Network interfaces to the SQL server in the same subnet" are actually attached to a private endpoint. I can then connect to the SQL server only if the FQDN resolves to that private IP. Adding it to the local hosts file works when testing. Do you mean something else? – Aileron79 Jun 11 '20 at 10:35
  • Service endoints and private link are two different things, which are you using? – Sam Cogan Jun 11 '20 at 10:36
  • Sry, quite new to Azure as you probably can tell. I am using what Azure refers to as "Private endpoint" which is attached to the same VNet as the VPN gateway I am connecting to, at the same time this private endpoint is linked to the SQL server (as its "Private link resource", meaning the endpoint is listed under the SQL server's "Private endpoint connections") – Aileron79 Jun 11 '20 at 10:46
  • 1
    Ok, your using private link, this has complications around the requirement for DNS resolution to get to the endpoint, so it might be easier for you to start with using Service Endpoints, which has no DNS requirement, it's not quite as secure as traffic still goes over the internet, but it is restricted to traffic over the network – Sam Cogan Jun 11 '20 at 10:49