Azure SQL Private Endpoints: Why Your VPN Users Can't Connect (And What Actually Works)

We deployed an Azure VPN Gateway to give remote users secure access to Azure SQL databases through private endpoints. The setup looked textbook perfect: private endpoints approved and connected, VNet peering configured, routes in place, TCP connectivity verified. Everything worked except the one thing that mattered—users couldn’t authenticate.

Here’s what we learned fighting Azure’s DNS architecture.

The Setup That Should Have Worked

Standard Azure private endpoint architecture for SQL Server:

Azure VPN Gateway (P2S) in transit VNet
SQL Server in separate VNet with private endpoints
VNet peering with gateway transit enabled
Private DNS zone privatelink.database.windows.net with A records

The Microsoft documentation shows this exact setup. Connect to VPN, DNS resolves the SQL FQDN to private IP, authentication succeeds through the private endpoint. Clean, secure, no public internet exposure.

What Actually Happened

Users on VPN tried connecting to my-sqlserver.database.windows.net:

sqlcmd -S my-sqlserver.database.windows.net -G

Error:

Cannot open server 'my-sqlserver' requested by the login.
Client with IP address '68.12.6.102' is not allowed to access the server.

That IP (68.12.6.102) is the user’s home internet connection, not their VPN IP (10.99.50.2). The VPN wasn’t routing SQL traffic.

First Wrong Turn: “It’s the Private Endpoints”

We verified TCP connectivity to the private endpoint IPs worked:

nc -zv 172.16.0.5 1433
# Connection succeeded

But authentication failed:

sqlcmd -S 172.16.0.5 -U testuser -P 'password'
# Error: The login failed

Same failure with Azure AD authentication. Same failure on all three private endpoints (including a freshly created test endpoint). Public endpoint worked fine with identical credentials.

We checked everything:

Private endpoint connection state: Approved
NSG rules: None blocking traffic
SQL Server connection policy: Changed from Default to Proxy (eliminates port 11000-11999 requirement)
VPN routes: Correctly advertising peered VNet CIDRs

Still failed. The private endpoints looked perfect in the portal but refused all authentication.

Why Connecting Directly to IPs Fails

I haven’t found Microsoft documentation explicitly stating this, but based on testing, my theory: Azure AD authentication tokens are scoped to the FQDN, not the IP address.

When you authenticate with Azure AD, you request a token for a specific resource identity. For Azure SQL, that’s the server’s FQDN like my-sqlserver.database.windows.net. The token includes an audience claim tied to that identity.

When SQL Server receives the authentication attempt, it validates the token’s audience matches the endpoint being accessed. Connecting to 172.16.0.5 presents a token scoped to my-sqlserver.database.windows.net—an audience mismatch. SQL Server rejects the authentication before it reaches the database layer.

Even enabling “Trust server certificate” to bypass TLS validation doesn’t help. The token audience validation happens independently of certificate validation.

This would explain why:

Both Azure AD and SQL authentication fail to private IPs
TCP connectivity works fine (authentication fails, not connectivity)
The same credentials work immediately against the FQDN
Creating new private endpoints doesn’t fix it (the issue is token scoping, not endpoint configuration)

Microsoft’s documentation on Azure AD authentication mentions tokens are scoped to the database server’s identity, but doesn’t explicitly call out that IP-based connections will fail token validation. Every tutorial shows FQDN-based connections, which work because DNS resolves to the private IP behind the scenes.

The Actual Problem: DNS Resolution

The private endpoints worked fine. The problem was DNS.

Azure VPN Gateway doesn’t push DNS configuration to clients. macOS continued using the home router (192.168.86.2) for DNS queries:

nslookup my-sqlserver.database.windows.net
Server: 192.168.86.2
Address: 192.168.86.2#53

Name: my-sqlserver.database.windows.net
Address: 20.40.228.130  # Public IP, not private

The FQDN resolved to the public IP. macOS routed that traffic out the default gateway (home internet), never touching the VPN tunnel. Azure SQL firewall saw the request from the user’s home IP and blocked it.

Proof:

We added the private IP to /etc/hosts:

sudo sh -c 'echo "172.16.0.5 my-sqlserver.database.windows.net" >> /etc/hosts'

Immediate success:

sqlcmd -S my-sqlserver.database.windows.net -G
1>  # Connected successfully

This proves the theory. The FQDN resolves locally to 172.16.0.5, so:

Client requests Azure AD token for my-sqlserver.database.windows.net (correct audience)
Client connects to 172.16.0.5 (private endpoint IP)
SQL Server validates token audience matches the FQDN being accessed
Authentication succeeds

The private endpoint authentication worked perfectly when DNS resolved the FQDN to the private IP. The entire problem was getting clients to resolve the hostname correctly.

Why Azure DNS Doesn’t Help

Azure provides a magic DNS resolver at 168.63.129.16 that’s supposed to resolve Private DNS zones. We linked the privatelink.database.windows.net zone to the transit VNet:

az network private-dns link vnet create \
  --resource-group my-resource-group \
  --zone-name privatelink.database.windows.net \
  --virtual-network transit-vnet \
  --registration-enabled false

The A record existed:

az network private-dns record-set a list \
  --resource-group my-resource-group \
  --zone-name privatelink.database.windows.net
  
Name          IP
-----------   ----------
my-sqlserver  172.16.0.5

But querying Azure DNS still returned the public IP:

dig @168.63.129.16 my-sqlserver.database.windows.net +short
# Returns public IP chain, not 172.16.0.5

Azure DNS ignored the Private DNS zone entirely, even with the VNet link configured and the DNS zone group attached to the private endpoint.

Solutions That Don’t Scale

Option 1: Manual /etc/hosts entries

Works for testing. Doesn’t work for 50 users across Windows, macOS, and Linux with multiple private endpoints.

Option 2: Tell users to add Azure DNS manually

Users could configure 168.63.129.16 as a DNS server in network settings. But it only works while on VPN (not routable from internet) and Azure DNS wasn’t resolving the private zone correctly anyway.

Option 3: Deploy a DNS forwarder VM

Microsoft’s documented solution: deploy a VM running DNS that forwards to 168.63.129.16. Configure VPN clients to use that VM as DNS. The VM can reach Azure DNS from inside the VNet, where Private DNS zones supposedly work.

This adds another VM to patch, monitor, and maintain. For a “zero maintenance” VPN solution, it’s not great.

What Actually Works: Embrace the Public Endpoint

Instead of fighting Azure’s DNS architecture, work with it:

az sql server firewall-rule create \
  --resource-group my-resource-group \
  --server my-sqlserver \
  --name AllowVPNClients \
  --start-ip-address 10.99.50.0 \
  --end-ip-address 10.99.50.63

This whitelists the entire VPN client pool (10.99.50.0/26) on the SQL Server firewall.

How it works:

User connects to VPN, gets IP 10.99.50.2
User connects to public FQDN my-sqlserver.database.windows.net
DNS resolves to public IP (doesn’t matter—traffic doesn’t route through VPN anyway)
Connection goes direct from user’s internet to Azure SQL public endpoint
Azure SQL firewall sees source IP 10.99.50.2 (from VPN), allows connection

Why this is fine:

Traffic is TLS encrypted regardless of private vs public endpoint
Azure SQL public endpoints are hardened and maintained by Microsoft
No DNS complexity
No additional VMs to manage
Works identically on all client operating systems
VPN IP whitelisting provides the access control you need

Why you might still want private endpoints:

Compliance requirements mandate no public endpoint access
You need guaranteed routing through Azure backbone (lower latency for some scenarios)
You’re willing to deploy and maintain DNS infrastructure

The Missing Documentation

Microsoft’s private endpoint tutorials show Bastion-connected VMs testing connectivity from inside Azure VNets. That works because VMs inherit the VNet’s DNS configuration automatically.

What’s missing: clear documentation that P2S VPN clients do NOT inherit Azure DNS configuration. You must either:

Deploy DNS forwarding infrastructure (Azure Private Resolver or custom DNS VMs)
Manually configure clients to use Azure DNS (not practical at scale)
Use the public endpoint with IP whitelisting

The third option isn’t mentioned in the private endpoint docs because it’s not technically using the private endpoint. But it’s often the right answer.

Key Takeaways

Private endpoints require FQDN-based connections. Based on testing, Azure AD tokens are scoped to the server’s FQDN identity, not IP addresses. Connecting directly to private IPs fails token audience validation.
Azure VPN Gateway doesn’t manage client DNS. Unlike some VPN solutions, Azure doesn’t push DNS configuration to connected clients.
Azure Private DNS zones require Azure DNS. Clients must query 168.63.129.16 to resolve private zones, and that only works reliably from inside Azure VNets.
Public endpoints with IP whitelisting are valid. TLS encryption provides security. VPN IP ranges provide access control. DNS complexity disappears. Token scoping works because clients use the FQDN.
Read the fine print. Microsoft’s tutorials test from Azure VMs because that’s where Private DNS actually works without additional infrastructure.

When you deploy P2S VPN for user access, budget time and resources for DNS infrastructure or accept that public endpoints with IP whitelisting might be the pragmatic choice.

The Setup That Should Have Worked#

What Actually Happened#

First Wrong Turn: “It’s the Private Endpoints”#

Why Connecting Directly to IPs Fails#

The Actual Problem: DNS Resolution#

Why Azure DNS Doesn’t Help#

Solutions That Don’t Scale#

What Actually Works: Embrace the Public Endpoint#

The Missing Documentation#

Key Takeaways#