Connecting to an Azure SQL server with a P2S VPN connection

Question

I'm trying to connect to an Azure SQL server that has a private endpoint over a P2S VPN connection, but it doesn't seem to be working

My default VNet: 10.0.0.0/24
The server's private IP: 10.0.0.4
The VPN Address pool: 10.1.0.0/24

Using azure data studio I've tried to connect to the server at 10.0.0.4, and I get the not very helpful Cannot open server "10.0.0.4" requested by the login. The login failed. message. If I enable public internet access for the server/add my local public IP into the firewall rules I can then connect to it using the public IP, so I know that the credentials/server is fine, there's just something not working right with the VPN connection. (Similarly if I turn the VPN off I get error messages about not connecting to the server, so it seems like traffic is going to the right places)

Any ideas on what I need to do in order to allow connections over the VPN link to the server?

Answer 1

I believe you need to connect using the public FQDN as otherwise you will be routed through the regional SQL gateway. Just make sure your DNS resolution resolves to the private IP address.

https://learn.microsoft.com/en-us/azure/azure-sql/database/private-endpoint-overview#check-connectivity-using-sql-server-management-studio-ssms

Answer 2

Hi @Chris Thornton Thank you for posting your Question on Microsoft Q&A and for using Azure services.
It is my understanding that you cannot connect to the DB that has private endpoint when using P2S.
When dealing with P2S and Private Link, issue is with the DNS resolution because Private Link DNS Resolution is limited to the VNET in Azure. So, there are 2 options
Either create a DNS Server in the VNET and make sure P2S Client uses it or create a host file in the PS2 Machine.

Please let us know if this works

Regards,
Oury

Answer 3

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.