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.
- The zone must be linked to every virtual network whose clients need to resolve it (or to a network acting as the resolution point).
- One zone per service type. Do not mix several services in one zone, and do not attach the same zone to private endpoints of different services.
- Clients must use the normal name. Connecting by IP or by the
privatelinkname fails, by design.
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
- Azure networks using default DNS: the private zone just needs to be linked to those networks (or to the hub, depending on the design).
- Networks with custom DNS servers: those servers must forward queries to
168.63.129.16(Azure's DNS), an address reachable only from inside an Azure virtual network. - On-premises DNS: never point a forwarder straight at
168.63.129.16(unreachable from on-premises; resolution silently falls back to public DNS). You need a conditional forwarder to the inbound endpoint of an Azure DNS Private Resolver (or a DNS forwarder VM in a network linked to the zone). - And the forwarder is for
database.windows.net, notprivatelink.database.windows.net. Microsoft states it in black and white. The idea: the client asks for<server>.database.windows.net, so that is the name the local DNS must hand to Azure. Azure then follows the whole chain (the normal name, theprivatelinkCNAME, the A record in the private zone) and returns the private IP. Servers without a private endpoint keep answering with their public IP: nothing breaks for them.
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.
- Redirect: after the handshake on 1433 the client is redirected to another port. With a private endpoint the docs require allowing 1433 to 65535 (inbound to the private endpoint's network, outbound from the client's network). For public access or service endpoints the redirect range is 11000-11999. If those ports are blocked, the initial connection succeeds then times out.
- Proxy: everything goes through the gateway on 1433 only. Simpler for firewalls, higher latency.
- Default: Redirect from inside Azure, Proxy from outside.
- Only some drivers support redirect (ODBC, OLE DB, SqlClient, JDBC 9.4+); the others are proxied.
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
- Public access left open "just in case": the portal test succeeds and hides the private-path problem.
- Private zone deleted or recreated without re-creating the links: no private resolution any more.
- Connection policy switched to Redirect before opening the ports.
- DNS cache: after a fix, flush the client cache (
Clear-DnsClientCache) and the DNS server cache.
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.