Welcome to the Microsoft Q&A Platform. Thank you for reaching out & I hope you are doing well.
I understand that you would like to connect to your SQL server using Azure VPN P2S.
Please note that:
- Azure VPN P2S Connection is used to provide connectivity from remote clients to a resources located in a Virtual Network.
- This means, your SQL server should
- either be created as a VM connected to this Virtual Network
- or use a Private Endpoint in this Virtual Network (if the SQL server is a PaaS service)
You informed you were using a Private EndPoint.
From your ping results, we can see that,
- <yourSqlServerName>.database.windows.net is resolving to the Public IP of the SQL server
- This is not ideal, we need it to resolve to the Private EndPoint's IP of the SQL server
If you are trying to access a PaaS service from OnPrem via S2S or from a remote client via P2S, it is mandatory that you configure the DNS resolution at your end to resolve to the Private IP of the Private EndPoint
- Modifying the Windows Host file is one of the ways to do the above.
- For scale and production, you can consider using one of the below
#1 If you have a custom DNS server at the P2S Remote Client
- Make sure you create a DNS Zone with "database.windows.net"
- And add the entry "<yourSqlServerName>" and make it resolve to the Private IP of the Private EndPoint
#2 If you have a custom DNS server in Azure,
- Consider : On-premises workloads using a DNS forwarder
- In the P2S VPN Client configuration file, you should
Please let us know if we can be of any further assistance here.
Please Accept an answer if correct.
Original posters help the community find answers faster by identifying the correct answer.