3

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

3

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.

  • 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. – Bruno Faria Mar 3 '18 at 23:20
  • @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). – GregGalloway Mar 6 '18 at 6:26
0

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.

0

Microsoft released a new feature in September to do that.

Please have a look to the documentation.

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-vnet-service-endpoint-rule-overview

  • Welcome to serverfault. When the link breaks, your answer becomes worthless later even if the linked material answered the question. Please include at least a summary so the answer can stand on its own. Thank you! – marsh-wiggle Dec 3 '18 at 16:18
0

Azure SQL uses gateways to figure it out which clusters the client wants to connect: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-connectivity-architecture.

As the gateways have fixed IP addresses I simply added a static route in the VPN interface for the specific sql gw.

For example if you get and ip address range from the VPN of 192.168.1.0/24 and you need to connect to a SQL in AustraliaEast region, execute the following commands:

add route 191.238.66.109 mask 255.255.255.255 192.168.1.1
add route 13.75.149.87 mask 255.255.255.255 192.168.1.1

The same can be done in powershell with Add-VpnConnectionRoute, it will add a static route.

It is currently working for me for AustraliaEast and SouthEastAsia regions

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.