How to connect from an azure VPN to an azure SQL server from my PC?

Question

We want from our work computers using an azure VPN to be able to connect/access the databases we have in azure SQL/MySQL. I have read the documentation but I don't understand how it is possible to do this without having to connect from a virtual machine. Currently I can connect from an Azure Virtual Network Gateway to our Azure Virtual Network and enter a virtual machine that we have for other things but I can't simply access to the databases without having to do it from the virtual machine.

I have tried to disable public connections in the firewall from the azure portal and create a private access configuration using the virtual network to then connect to the VPN from my windows and access but the connection to the DB has not worked.

Answer 1

Hi,

Are you connecting to the Azure network through a site-to-site VPN?

If not, you can configure an Azure Point-to-Site VPN and connect to your Azure network directly through the VPN connection; you can authenticate with AAD or certificates.

If you have configured a private endpoint, then I half expect your issue is caused by DNS; it is trying to connect to the Public endpoint, not the private endpoint.

If you have a P2S VPN, you can configure a Private Resolver for DNS, and point your P2S client to the resolver. You have to edit the azurevpnconfig file.

Answer 2

Hi @Alejandro García García,

Adding to what @Luke Murray mentioned

I think you are using P2S VPN for the connection, which also works in our case.

However, we cannot use  FQDN for Hostname, we need to use IP directly instead of a hostname which can be found in the Private DNS Zone created for the server.

For Site to-Site VPN we can configure it as below figure:

For the Custom DNS Server in the Hub VNET add a conditional forwarder for DNS domain postgres.database.azure.com domain.This conditional forwarder must point to the Azure DNS IP address: 168.63.XXX.XX. as shown in Figure  below

As an alternative to a conditional forwarder, you can also configure a server-level forwarder to Azure DNS.

Add a virtual network link in the Private DNS Zone for Hub-VNET.

f you also have clients on-premises that must connect to the Flexible Server FQDN then you would need a conditional forwarder in the on-premises DNS server pointing to the IP address of the Custom DNS server in Azure.

Please let me know if you have any additional queries.

Regards

Geetha

Answer 3

Currently I still do not understand what my problem is.

I have a P2S connection to connect to a VPN that I have configured with openVPN, I have created the virtual network with three subnets (GatewaySubnet, default and dnsresolvesubnet), the virtual gateway connects me well and allows me to connect correctly to the VPN from my computer staff (without virtual machine).

Following the instructions of @lukemurraynz I have configured a private resolver for dns pointing to my virtual network and I have created an inbound endpoint that points to the subnet -> "dnsresolvsubnet".

I have added the IP address that the inboundendpoint interface returns to the configuration of my local azurevpnconfig.xml file to add the inboundendpoint IP and be able to connect, I have added to my azure SQL a private endpoint that points to the default virtual network subnet of my virtual network.

Answer 4

Assuming you have already created a private endpoint connection for your azure SQL Server and you have a working Virtual Network Gateway all within the same Virtual Network then follow the steps below to connect to your sql server using an Azure P2S VPN.

1. You need to first create an Azure DNS Private Resolver

• Has to also be in the same Virtual Network as your Private Endpoint and VNet Gateway
• Create a inbound endpoint(You will need to create a new subnet to hold this)
• Skip outbound endpoints and ruleset
• Create the resource

2. Once the Azure Private Resolver has been created go to the resource and click on 'Inbound Endpoints' and then copy the Private IP Address from the 'IP Address' column
3. Go to the virtual network that all of these resources are in and click on 'DNS Servers' and then select custom and then paste in the Private IP Address you copied from the Inbound Endpoint and then click save.

You should be good to go now. Connect to your VPN and do an nslookup on your_sqlserver_name.database.windows.net and make sure the Private Endpoint private ip address shows up.

Answer 5

It took me a while to figure this out as I am totally new to Azure. The solution proposed in the previous post uses Private Endpoint, the problem with private endpoint is that you incur in additional network costs: https://azure.microsoft.com/en-us/pricing/details/private-link/

Yes, MS decided that when consuming a PaaS service (Azure, AKS, whatever) that is by default exposed in a public IP you should pay to have it attached to a VNET with a private IP (both by the hour, and by the ammount of traffic).

In the case of Azure SQL, you can still secure you SQL without resorting to Private Link and without having to grant permissions to public IP's on the resource.

• Setup a VNet
• Setup a Subnet (GatewaySubnet - this naming is enforced by MS, I guess to prevent people to have more than one Gateway per VNET and so forcing them to use VNET peering to incur in then more costs) with permissions for service endpoint "Microsoft.Sql"
• Setup a Gateway VPN attached to GatewaySubnet
• Setup your managed SQL Instance, and in the Networking pane "attach" it to the GatewaySubnet

With this setup you might be thinking that a user connected to the VPN should be able to connect to the SQL Server on it's public IP. As traffic is leaving from an authorized subnet, you should not need to whitelist the client's pubilc IP. Indeed it is, the problem is with how are splitting traffic from within the VPN connected client considering that:

• Azure PaaS services are using public IP's
• Azure SQL can either Proxy or Redirect connections (https://learn.microsoft.com/en-us/azure/azure-sql/database/connectivity-architecture?view=azuresql) so even if you manually force traffic from the client machine to the SQL public IP through the VPN, this might end up in a redirect wich targets a different public IP and thus not going through the tunnel again.

You can force all your client traffic through the VPN and the issue will vanish, but I would not recommend that.

Easy setup

• Change your SQL networking settings to "Proxy" instead of the default "Auto".
• Add a routing rule for your PaaS server Host to go through the VPN:

$Hostname = "mydatabasesql.database.windows.net";
$VpnConnectionName = "vnetname";

$ip = [System.Net.Dns]::GetHostAddresses($Hostname)[0].IPAddressToString;
Add-VpnConnectionRoute -ConnectionName $VpnConnectionName -DestinationPrefix "$ip/32" -PassThru;

The problem with this setup is that MS clearly states that having the SQL traffic fully go through the proxy is NOT performance wise. Making this change will negatively impact you production workload performance.

Redirecting the whole Azure PaaS ranges setup

What happens during a redirection is that after initial connection attempt to the PaaS endpoint (the one resolved by "mysql.database.windows.net") the client is provided with a new public IP address where the actual connection needs to be made. That connection comes from a pool of ip ranges that are made public by Azure and regularly updated:

https://download.microsoft.com/download/7/1/D/71D86715-5596-4529-9B13-DA13A5DE5B63/ServiceTags_Public_20240115.json

So we basically just need to redirect traffic targeting those IP ranges through the VPN.

I made a small PS script to do so: https://gist.github.com/david-garcia-garcia/180978df54744511adae5e06f9e3d2bd