2

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: enter image description here

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?

Tom
  • 365
  • 3
  • 19

1 Answers1

0

WAY 1:

You may Use domain name instead of IP directly from your virtual network. So, you need some service in Azure which can translate domain name to IP.

It is necessary to properly configure your DNS settings to resolve the private endpoint IP address to the fully qualified domain name (FQDN) of the connection string.

  • Use a DNS forwarder for on-premises workloads to resolve the FQDN of a private endpoint, 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.

.

  • Use the host file on a virtual machine to override the DNS: Azure creates a canonical name DNS record (CNAME) on the public DNS. The CNAME record redirects the resolution to the private domain name (privatelink.database.windows.net). You can override the resolution with the private IP address of your private endpoints. See azure-provided-name-resolution.

References:

WAY 2 :

You may go for SQL managed instance which is another Azure SQL PaaS offering .It is deployed with in VNet with no public service endpoints and uses root and client certificates to authenticate in azure.

(Go for this when one prefers not to use Private endpoint:)

Other references:

  1. DNS-Client-Configuration-Options
  2. DNS-Integration-Scenarios
  3. DNS-Scenario-Using-AD
kavyaS
  • 8,026
  • 1
  • 7
  • 19
  • Thanks for the response. I have tried to setup the [DNS forwarder VM using this template](https://azure.microsoft.com/en-us/resources/templates/dns-forwarder/) but I wasn't able to connect to the database. I tried following [the documentation you mentioned](https://learn.microsoft.com/en-us/azure/private-link/private-endpoint-dns#azure-services-dns-zone-configuration) but it also didn't work. – Tom Jul 15 '21 at 15:06