LLI Wiki — My Digital Knowledge Base

Private endpoints and DNS for Azure SQL: works from the portal, fails from the job

Private endpoint vs service endpoint vs public access, the privatelink zone, hub-and-spoke and on-premises DNS forwarding, then a troubleshooting checklist: Resolve-DnsName, Test-NetConnection, Redirect vs Proxy and port ranges.

"It works from the portal but not from the job." On Azure SQL, when a private endpoint is involved, this is almost always a DNS problem, then a ports problem, and very rarely a SQL problem. Here is how I tell the network options apart, how I set up DNS, and the checklist I run when a connection fails.

Three ways to expose Azure SQL

Public access + firewall Service endpoint Private endpoint
Scope The server, via IP rules The whole SQL service, at subnet level One specific instance (your server)
Public IP used Yes Yes (traffic leaves with the subnet's private IP) No
On-premises access Via an allowed public IP No Yes (VPN, ExpressRoute)
Can public access be disabled n/a No Yes
DNS change No No Yes
Built-in exfiltration protection No No Yes

Two details from the docs I keep in mind: with a service endpoint, Azure SQL is regional (virtual network and server in the same region); and adding a private endpoint does not block public access. You must tick Deny public network access on the server, otherwise you have two open paths.

DNS: the part that breaks

Clients keep using the usual name <server>.database.windows.net. Azure publishes a CNAME in public DNS pointing to <server>.privatelink.database.windows.net. A private DNS zone privatelink.database.windows.net holds the A record pointing to the private endpoint's private IP. A client only takes the private path if its resolver queries that zone.

flowchart LR
    OnPrem["On-premises client"] --> OPDNS["On-premises DNS"]
    OPDNS -- "conditional forwarder database.windows.net" --> Inbound["DNS Resolver inbound endpoint (hub)"]
    Inbound --> Zone["Private zone privatelink.database.windows.net"]
    Spoke["Job in a spoke"] --> AzDNS["Azure DNS 168.63.129.16"]
    AzDNS --> Zone
    Zone --> PE["Private endpoint : private IP"]
    OnPrem -- "VPN / ExpressRoute" --> PE
    Spoke -- "peering" --> PE
    PE --> SQL["Azure SQL gateway"]

Hub-and-spoke and on-premises

Checklist when the job fails

I run everything from the machine that runs the job, not from my workstation or the portal. The portal and the job do not share the same network path or the same DNS.

1. Which IP comes back?

Resolve-DnsName sql-contoso-prd.database.windows.net -Type A
nslookup sql-contoso-prd.database.windows.net

Expected: a private IP from the private endpoint subnet. Public IP: the job's resolver does not see the private zone (missing link, custom DNS without a forwarder, cache). NXDOMAIN or timeout: forwarding or DNS firewall issue (UDP/TCP 53).

2. Does the port answer?

Test-NetConnection -ComputerName sql-contoso-prd.database.windows.net -Port 1433

Check RemoteAddress: it proves the test used the private path.

3. The handshake works but the connection times out: connection policy.

4. Asymmetric routing. If traffic crosses an NVA, firewall or NAT, the return path must match. A UDR can only override the private endpoint's system /32 route with an equal or more specific prefix, and the NVA must SNAT.

5. Final check from SQL, once connected:

SELECT client_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID;

Pitfalls

Lessons learned

I always start with Resolve-DnsName from the right host. In the vast majority of cases the answer is already the cause. Ports come second, and changing the connection policy is the last resort, never the first reflex.

Mastodon LLIMastodon SocialFosstodon