question

TomConneely-5136 avatar image
0 Votes"
TomConneely-5136 asked RaviVarmanMSFT-5919 commented

Connection to Azure SQL database on Azure Private Link/Endpoint using Azure VPN Client not working

Hello,

I'm trying to setup an Azure SQL database using P2S VPN for users who are remote working. They are using some applications like SSMS and Visual Studio that require access to the database. We allow them to connect by white listing their IP addresses but we would like to stop this and to use the deny public network access option on the SQL server on Azure.

Whenever I try to connect using SSMS I get the following message:
113913-screenshot-2021-07-12-160539.png

I've followed the steps outlined in the documentation and tutorials on MS Docs but I have not been able to get the private endpoint to work with the database.

I have created the virtual network gateway and connected it to Azure Active Directory and I can see the sessions being created by the users as they log in.

I have created the virtual network using the address range = 10.1.0.0/16 and the subnet address range = 10.1.0.0/24. I have attached the private endpoint connection to the Azure SQL server and added the virtual network to the firewall.

Is there some setting required to allow the user to connect to the database from their PC without whitelisting IP addresses?

azure-sql-databaseazure-virtual-networkazure-vpn-gateway
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @TomConneely-5136


Could you please provide an update on this post?
Kindly let us know if the below helps or you need further assistance on this issue.


Please don’t forget to close the thread by clicking "Accept the answer" wherever the information provided helps you, as this can be beneficial to other community members.

0 Votes 0 ·

1 Answer

RaviVarmanMSFT-5919 avatar image
0 Votes"
RaviVarmanMSFT-5919 answered RaviVarmanMSFT-5919 commented

Hi @TomConneely-5136

Thanks for reaching us.

DNS plays a major role for the resolution of the FQDN.

Do nslookup for the FQDN and see is it resolving to the private IP of the private endpoint or not if it resolves to private Ip, then it takes point to site connection if not it will take internet route to reach SQL, if you see public IP then it is not reaching via private endpoint hence the error message.

  1. Testing : You can have host entry on your point to site client machine for the private endpoint with IP 10.1.0.4
    ex: 10.1.0.4 sqldbname.privatelink.database.windows.net

  2. Use your DNS forwarder : For on-premises workloads to resolve the FQDN of a private endpoint, use a DNS forwarder to resolve the Azure service public DNS zone in Azure. A DNS forwarder is a Virtual Machine running on the Virtual Network linked to the Private DNS Zone that can proxy DNS queries coming from other Virtual Networks or from on-premises. This is required as the query must be originated from the Virtual Network to Azure DNS.

Ref: https://techcommunity.microsoft.com/t5/azure-database-support-blog/azure-sql-db-private-link-private-endpoint-connectivity/ba-p/1235573

DNS scenarios : https://docs.microsoft.com/en-us/azure/private-link/private-endpoint-dns#dns-configuration-scenarios

Hope this was helpful. Please let us know in case of any additional questions or concerns.

Please "Accept the answer" if the information helped you. This will help us and others in the community as well.




· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @RaviVarmanMSFT-5919,

Thanks for the answer. I tried testing the connection using nslookup and it was not resolving to the private endpoint.

I used this template to create a DNS Forwarder VM. I tried connecting it to the virtual network that I had attached to the SQL server but the connection didn't work.

The template creates its own virtual network so I recreated the SQL server and attached the private endpoint to the DNS forwarder's virtual network. I created a virtual network gateway that was attached to the DNS forwarder's virtual network. I was able to connect from my local machine using the Azure VPN client but still was not able to connect to the database.

Should I be setting up the DNS forwarder VM in a different way? How can I set up the VPN client to work with the DNS forwarder? Should it handle the requests automatically?

0 Votes 0 ·

Hi @TomConneely-5136

Apologize For delay in response.

  • Create an windows VM inside your virtual network

  • Install DNS role on the VM

  • Under Virtual network-->DNS settings provide the private IP of the VM where you installed the DNS role

  • Restart the VM's under the virtual network for custom DNS to take effect

  • Download the VPN client from point to site configuration on Virtual network gateway and install on your client machine

  • You should be able to see the custom DNS provided on the Vnet will be reflecting now on your client as well as DNS server

  • When you do Nslookup still, it will be resolving to public IP with DNS server as custom DNS hosted in Azure, as no conditional forwarder defined on the custom DNS still it will resolve to public IP not private IP

  • Login to the DNS server in Azure, open server manager--> Go to DNS ---> Select server and Right Click---> Select DNS manager

  • Navigate to Conditional Forwarders , add new conditional forwarder with name DNS domain database.windows.net and IP as 168.63.129.16

  • Try to do nslookup and you should be able to see the private IP of the endpoint and you should be able to access

Note: Make sure the private DNS has link with the Virtual network, if not, then the resolution to private IP will not happen.

Hope this was helpful. Please let us know in case of any additional questions or concerns.

Please "Accept the answer" if the information helped you. This will help us and others in the community as well.


0 Votes 0 ·