2

I am trying to connect an on-premises laptop with dynamic external IP to our Azure SQL Server. To do this, I created a virtual network gateway and connected the laptop to the gateway. Also, I added a private endpoint to the SQL server. After this, I can successfully connect to the SQL server IP using telnet, and if I resolve the SQL server FQDS in hosts file, I can connect to the server via SSMS. But without hosts file, the laptop always tries to connect to the SQL server via its public endpoint/address.

I found the following article: https://techcommunity.microsoft.com/t5/azure-database-support-blog/azure-sql-db-private-link-private-endpoint-connectivity/ba-p/1235573 The article is great. It recommends using your own DNS server to resolve the SQL server FQDN to the local IP. Unfortunately, the laptop does not have access to any custom DNS, so this solution does not suit. There are two questions:

  1. Is there any possibility to establish connection between an on-premises computer with dynamic IP and an Azure SQL server using a private endpoint but without own DNS server?

  2. If the answer to the first question is "No", is there another way to connect an on-premises computer with dynamic IP to an Azure SQL server using any other Azure application(s)?

Oleksii Serik
  • 71
  • 8
  • If you have an internet connection that uses dynamic IP addressing and you have trouble getting through the firewall, try one of the following solutions: 1. Ask your internet service provider for the IP address range that's assigned to your client computers that access the server. Add that IP address range as an IP firewall rule. 2. Get static IP addressing instead for your client computers. Add the IP addresses as IP firewall rules. – Leon Yue Jun 08 '20 at 05:33
  • Ref:https://learn.microsoft.com/en-us/azure/azure-sql/database/firewall-configure#troubleshoot-the-database-firewall – Leon Yue Jun 08 '20 at 05:34
  • 1
    That does not really answer original question. Sometimes you do not have choice. Think travelling, airport Wi-Fi and so on.After all, that's what laptops were invented for :) – Dmytro Gokun Jun 08 '20 at 06:33

1 Answers1

1

First of all, you can not use FQDN without DNS service. So you indeed need a custom DNS server in using FQDN of the server in connection strings for your clients to connect from on-premise VM to the Azure SQL server.

Since you are using a laptop, the DNS servers used by your computer are most likely specified by your ISP. You have no more control over it or ask your ISP to configure the DNS forwarder. Otherwise, you need to deploy a DNS server in your internal network. Currently, in this scenario, the best method is to use the HOSTS file on the local machine to override the Public DNS.

However, if you don't like using the HOSTS file, you can provision an Azure VM as the DNS server in the same Azure virtual network as the virtual network gateway.

Main steps:

  1. Deploy an Azure VM, and RDP to that VM and run the PowerShell commands to install the DNS server role.

    Install-WindowsFeature -Name DNS -IncludeManagementTools Get-WindowsFeature *DNS*

  2. Add Azure DNS (168.63.129.16) as a forwarder on your custom DNS server according to the step 5 in this blog. If you do not want to use forwarder you can also create a forward lookup zone and added manually the host to match the FQDN. You could read On-premises workloads using a DNS forwarder for more details. enter image description here

  3. After you have configured the DNS server and set the DNS forwarder. You could change the DNS server of Azure VNet to your Azure VM's private IP address. enter image description here

  4. Restart your Azure VM and re-download the VPN client package and re-connect the VPN connection to make the networking update. Check the DNS server on the local VPN client machine and set the DNS server to the custom DNS server in the TCP/IP settings. Then you will look up your private IP address via the default FQDN of Azure service.

    In my example, I am using Azure storage account but it works the same with Azure SQL database when using a private endpoint on the Azure and P2S VPN connection. In this way, it requires that there are not any other VPN connections except the P2S VPN connection on the local machine. enter image description here

Then you could resolve the Azure SQL server FQDN to the private IP address of the private endpoint. However, it perhaps does not have a better performance to connect to Azure SQL Server with a VPN connection than directly connect to it through the public Internet and public DNS sevice.

Nancy
  • 26,865
  • 3
  • 18
  • 34
  • 1
    Thanks for your detailed answer, @NancyXiong Yep, I was able to solve my problem the way you've suggested. Actually, the last step (when the local DNS server address is added to the VPN connection settings) was not required for me, everything works good even when "Obtain DNS server address automatically" is selected. – Oleksii Serik Jun 15 '20 at 13:20
  • 1
    Also, I am to note that I had to create a VM with Windows Server instead of Windows 10. If someone cannot do it for any reason, there is a good open source free DNS server for windows ([Technitium DNS Server](https://technitium.com/dns/)). It was working for me as well. – Oleksii Serik Jun 15 '20 at 13:22