Reach Azure SQL database with dynamic IP addresses

Question

I have an Azure SQL database on which my data resides. This data should be retrieved from a system (e.g. Dynamics or another server). However, this system does not have a static IP address. How do I grant these systems access to my Azure SQL database? So far I have only successfully included static IP addresses.

Answer 1

For the OP: if your Microsoft Dynamics CRM is running in an Azure VM and you are connecting to an Azure SQL DB, then Azure's default networking configuration should allow access. (This may be different if you're connecting to an Azure SQL Managed Instance.) Within the Azure portal, if you navigate to the logical SQL Server hosting your Azure SQL DB and go to the Networking section, you should see a default firewall rule named "AllowAllWindowsAzureIps" with Start IP 0.0.0.0 and End IP 0.0.0.0. That's what should allow any Azure-to-Azure access.

However, if you're trying to connect from a public computer with a dynamic IP address that is not hosted on Azure, it's much trickier. In my case, I was connecting with a Windows 10 workstation with a dynamic public IP. And to be clear, this is for Azure SQL Database, not Azure SQL Managed Instance. The solution below uses a "Private Endpoint" (or "Private Link") for the Azure SQL Database logical server. An overview is here. A different solution is needed for a Managed Instance.

Briefly, I was able to make this work via the following steps:

1. I set up an Azure VPN Gateway with a Point-to-Site (P2S) configuration.
2. On my client computer, I created a VPN configuration to connect to the Azure VPN Gateway. (I used the native Windows 10 VPN client. But there are options for Mac and Linux clients too.)
3. I created a Private Endpoint for the Azure SQL Server.
4. I configured my Windows "hosts" file with the Azure SQL Server Fully Qualified Domain Name (FQDN) and the private IP address assigned to the Azure SQL Server Private Endpoint. This is the simplest method for DNS resolution of the Azure SQL Server FQDN.

After configuring the above and connecting via the Windows 10 VPN client, I was able to connect to the Azure SQL DB with SSMS and with C#/.Net apps using standard SQL Server connection strings.

Below, the steps are summarized with references to Microsoft documentation and a few of my own comments. The details are quite lengthy, and it doesn't make sense to repeat all that Microsoft has documented. But I think this summary may be of help to some. It took me several hours to make this work.

The steps below should be nearly identical for any Windows server or workstation. Mac and Linux client computers can also connect using this method, but the VPN details need to be adjusted.

Microsoft has two articles that describe how to set up the Azure VPN Gateway. The first utilizes Azure PowerShell:

(1) https://learn.microsoft.com/en-us/azure/vpn-gateway/vpn-gateway-howto-point-to-site-rm-ps

That is what I followed. The second describes how to do this via the Azure portal:

(2) https://learn.microsoft.com/en-us/azure/vpn-gateway/vpn-gateway-howto-point-to-site-resource-manager-portal

I referred to the second in order to better understand and troubleshoot some of the Azure PowerShell steps.

The instructions in (1) worked well when I read and followed them carefully. Below are a few observations.

• Unless you're very comfortable with IP networking, CIDR notation, and subnets, it's best to use or closely follow the IP address ranges and subnet configurations given in (1). It is necessary to have a "gateway" subnet and what's referred to as a "front end" subnet in the VPN Gateway configuration.
• When actually creating the VPN Gateway (using the New-AzVirtualNetworkGateway PowerShell command), you must choose a GatewaySku. These cost money! Pricing is here. The lowest cost Basic SKU costs about $27 per month (currently) but only supports Windows VPN clients via SSTP. For Mac or Linux clients, you need at least the VpnGw1 SKU which is about $139/month, and you must include the IKEv2 and/or OpenVPN protocols.
• Actually running the New-AzVirtualNetworkGateway command takes a while to complete... in my case, 35 minutes.
• For some reason, I had to run the command that assigns the $VPNClientAddressPool twice for it to work.

After the Azure VPN Gateway is created, you need to switch to the detailed instructions in (3) https://learn.microsoft.com/en-us/azure/vpn-gateway/vpn-gateway-certificates-point-to-site to create a self-signed root certificate to install on the VPN Gateway, and a client certificate (signed by the same root certificate) for your local computer. The instructions in (3) are lengthy but quite good.

After creating the self-signed root and client certificates, you continue with instructions in (1) to upload the self-signed root certificate to the Azure VPN Gateway (using the Add-AzVpnClientRootCertificate command). Again, for some reason I had to run this twice before it worked.

After the above, you run the PowerShell commands:

$profile=New-AzVpnClientConfiguration -ResourceGroupName $RG -Name $GWName -AuthenticationMethod "EapTls"
$profile.VPNProfileSASUrl

and use the URL displayed by VPNProfileSASUrl to download the VPN configuration files. For Windows, after installing the VPN client configuration, you should be able to open the VPN client and successfully connect to the Azure VPN Gateway.

Next, to create the Private Endpoint for my Azure SQL Database logical server, I used the Azure portal rather than Azure PowerShell, since this was quick and straightforward. (This more or less follows Microsoft's instructions starting here.)

• Since I had an existing Azure SQL Database logical server, I simply navigated to its "Networking" section and clicked the "Private access" tab, which has a "Create a private endpoint" option.
• This takes you through a 6-step wizard. On the "Virtual Network" step #3, it's important to choose the "front end" subnet that you created for the VPN Gateway.
• On the "DNS" step #4, I chose "Yes" for "Integrate with private DNS zone".
• Once the Private Endpoint was created, you must take note of the private IP address assigned to the Endpoint. If you use the IP network and subnet addresses given in (1), this will probably be 10.1.0.4.

For an Azure SQL Database logical server named myazuresqlserver, the Private Link FQDN will be myazuresqlserver.privatelink.database.windows.net. But waht you really need is the normal FQDN, which will be myazuresqlserver.database.windows.net.

As mentioned above, to avoid additional DNS server configuration steps, I simply added an entry to my Windows hosts file:

10.1.0.4 myazuresqlserver.database.windows.net

Once this was done, I was able to connect via SSMS using the myazuresqlserver.database.windows.net SQL Server name and a regular SQL Server login user ID and password. With my .Net apps, I was also able to connect using standard SQL Client connection strings with the myazuresqlserver.database.windows.net server name.

Yes, it would be nice if it was easier to connect to an Azure SQL DB from a client computer with a dynamic IP address. If there are other, better solutions, I hope someone will post them here!

A final note: If you want to automatically launch your VPN connection to your Azure VPN Gateway (via a command line script when booting a server, for example), that's also not easy! The rasdial.exe command won't work out of the box. But instructions here describe how this can be done. Note that once connected, rasdial.exe {yourVPNconnectioName} /disconnect can disconnect the VPN via the command line with no extra steps needed.