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

Tom 1 Reputation point
2021-07-12T15:34:23.147+00:00

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 Virtual Network
Azure Virtual Network
An Azure networking service that is used to provision private networks and optionally to connect to on-premises datacenters.
1,175 questions
Azure SQL Database
Azure VPN Gateway
Azure VPN Gateway
An Azure service that enables the connection of on-premises networks to Azure through site-to-site virtual private networks.
838 questions
{count} votes

1 answer

Sort by: Most helpful
  1. RaviVarmanMSFT 616 Reputation points Microsoft Employee
    2021-07-13T05:14:57.267+00:00

    Hi @Tom

    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://learn.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.