Lesson Learned #505: Verifying IP Resolution in Azure SQL Database in Private Link

This last week, I worked on two service requests that our customers got the following error message: (Reason: An instance-specific error occurred while establishing a connection to SQL Server. Connection was denied since Deny Public Network Access is set to Yes (https://docs.microsoft.com/azure/azure-sql/database/connectivity-settings#deny-public-network-access). To connect to this server, use the Private Endpoint from inside your virtual network (https://docs.microsoft.com/azure/sql-database/sql-database-private-endpoint-overview#how-to-set-up-private-link-for-azure-sql-database).) using Private link connection.

In both situations, I have found that our customers are using custom DNS configurations. On rare occasions, the resolved IP is the public gateway IP instead of the private IP from the Private Link. While the root cause of this issue needs to be investigated, I would like to share this C# code that checks IP resolution before connecting to Azure SQL Database.

The idea is if the IP resolved is not the private one it tries to request again 5 time with a delay of 250 ms to be sure that the IP will be the expected one. If not, the application might be finished or reported an error. You can adapt this code according to your needs to improve the reliability of your connections.

This approach helps avoid unnecessary errors and ensures a reliable connection to the Azure SQL Database. Here is the C# script that implements this solution:

using System;
using System.Net;
using System.Threading;
using Microsoft.Data.SqlClient;

class Program
{
    static void Main(string[] args)
    {
        string serverName = "your-sql-server.database.windows.net";
        string resolvedIp = "";
        int maxAttempts = 5;
        int attempts = 0;
        bool isPublicIp = true;

        while (attempts < maxAttempts)
        {
            resolvedIp = ResolveDns(serverName);
            if (IsPublicIp(resolvedIp))
            {
                attempts++;
                Console.WriteLine($"Attempt {attempts}: Resolved to public IP {resolvedIp}. Retrying in 250ms...");
                Thread.Sleep(250); 
            }
            else
            {
                Console.WriteLine($"Resolved to private IP {resolvedIp}.");
                isPublicIp = false;
                break;
            }
        }

        if (isPublicIp)
        {
            Console.WriteLine("Failed to resolve to a private IP after 5 attempts. Exiting.");
            return;
        }

        string connectionString = $"Server={serverName};Database=dbname;User Id=your-username;Password=your-password;";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            try
            {
                connection.Open();
                Console.WriteLine("Connection successful.");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error connecting: {ex.Message}");
            }
        }
    }

    static string ResolveDns(string serverName)
    {
        var hostEntry = Dns.GetHostEntry(serverName);
        return hostEntry.AddressList[0].ToString();
    }

    static bool IsPublicIp(string ipAddress)
    {
        var ip = IPAddress.Parse(ipAddress);
        return !IsPrivateIp(ip);
    }

    static bool IsPrivateIp(IPAddress ipAddress)
    {
        byte[] bytes = ipAddress.GetAddressBytes();
        return (bytes[0] == 10) ||
               (bytes[0] == 172 && bytes[1] >= 16 && bytes[1] <= 31) ||
               (bytes[0] == 192 && bytes[1] == 168);
    }
}

Articles related:

Lesson Learned #256: Connection was denied since Deny Public Network Access and DNS resolution. - Microsoft Community Hub