How do I connect to an Azure SQL Server via an Azure VPN Gateway?

Question

I have setup an Azure SQL Server with an Elastic Pool into which I have created a Test database.

I have also setup an Azure Virtual Network and a Point-to-Site VPN. The Virtual Network has 2 subnets - one for the GatewaySubnet and another into which I have placed a Windows Virtual Machine.

I have successfully configured Remote Desktop access to the VM and have verified that I can only connect to Remote Desktop by using the server's internal IP when I am connected to the VPN (I have also disallowed RDP access via the external IP - but that's not relevant to my question).

I'd like to restrict and control access to the SQL Server as far as possible. I have set "Allow access to Azure services" to "OFF". I have also added both of my Virtual Networks' subnets to the SQL Server's firewall settings and enabled the "Microsoft.Sql" endpoint.

I have verified that I can connect to the SQL Server from a copy of SQL Server Management Studio installed on the Virtual Machine.

However - I can't connect using SQL Server Management Studio from my desktop machine, even when I'm connected via the VPN. I'd like to be able to do this without adding my client IP directly to the firewall. We have a number of remote developers (all on dynamic IPs) who will need to access the servers and I don't want the overhead of managing these firewall rules. I'd much rather just give them the VPN client.

Thanks in advance...

Solution 1

I have just received a response from Azure Support on this issue (03/03/2018) and they have confirmed that what I'm trying to achieve isn't currently possible.

Solution 2

This is more workaround rather than using the VPN gateway.

You can try setting up a VPN server with NAT on your VM, allow the VM IP address on Azure SQL, and then your developers will connect to the VPN server on the VM.

The NAT will trick Azure SQL to think that the clients are the VM.

Comments

[aquib.qureshi]

This feature is in preview as of now. Azure Private Link for PaaS services docs.microsoft.com/en-us/azure/sql-database/…

[Bruno Faria]

So from my understanding, you want to restrict the access to SQL DB to the private network only, including access from On-Prem workstations and VMs connected through S2S, but that is not possible even with the newly added feature called "Service Endpoints". Is that correct? Because I thought that was what "Service Endpoints" were supposed to do.

[GregGalloway]

@BrunoFaria Service Endpoints in their current incarnation work great for resources on a VNET connecting to Azure SQL. But they don't help at all with an on prem client trying to connect to Azure SQL. That connection still happens via the on prem client's public IP. You might look at ExpressRoute public peering (instead of site-to-site VPN) or Azure SQL DB Managed Instance (which does support connecting over site-to-site VPN).

[Roberto Bonini]

Any changes for the updates on this functionality?

[Hilarion]

If I'm understanding you right, for Point-to-Site VPN this would mean, that you need to mess with your client-side routing settings each time you connect to and disconnect from the VPN.