INFRA-0007: Nonprod Network Topology and SQL Private Access

Status

Accepted — both nonprod SQL private endpoints migrated to vnet-eshars-nonprod-cus (2026-04-23)

DNS/VPN sections superseded

The VPN Architecture and DNS Resolution for Private Endpoints sections below describe the pre-rebuild model (forwarder = vm-ops-nonprod at 10.1.0.12). That is no longer correct. The forwarder is now a dedicated VM (vm-eshars-dns-{tier}-cus at the hub vms subnet .4), and the P2S client-routing model is documented in INFRA-0009. This ADR is retained as a historical record of the 2026-04 topology and SQL PE migration.

Date

2026-04-23

Context

As part of the Defender remediation effort (HM-8334), the goal is to remove individual developer public IP firewall rules from Azure SQL servers and require all SQL access to route through the VPN. This document captures the current nonprod VNet topology and the work done to enable private endpoint access to db-eshars-nonprod.

Current VNet Topology (as of 2026-04-23)

Hub-and-Spoke (nonprod)

vnet-eshars-core-np      (hub, 10.20.0.0/16, centralus)
    ├── peered to rg-eshars-nonprod       (old spoke, 10.1.0.0/16, centralus) — workloads only, being phased out
    └── peered to vnet-eshars-nonprod-cus (new spoke, 10.21.0.0/16, centralus) — canonical nonprod workload VNet

vnet-eshars-nonprod-cus is the new canonical workload spoke. All nonprod private endpoints must live here. The old rg-eshars-nonprod VNet still hosts active workloads (webapp, VMs, App Gateway, Bastion) and will be retired once those are migrated.

Full VNet Inventory

VNet	Resource Group	Region	Address Space	Status	Purpose
vnet-eshars-core-np	rg-eshars-core-np	centralus	10.20.0.0/16	Active — Hub	VPN gateway, DNS, shared infra only
vnet-eshars-nonprod-cus	rg-eshars-nonprod	centralus	10.21.0.0/16	Active — Canonical Spoke	Nonprod private endpoints; peered to hub with gateway transit
rg-eshars-nonprod	rg-eshars-nonprod	centralus	10.1.0.0/16	Active — Old Spoke (retiring)	Legacy nonprod workloads (App Service, VMs, App Gateway, Bastion); to be migrated to vnet-eshars-nonprod-cus
rg-eshars-nonprod-asr	rg-eshars-nonprod-centralus	centralus	10.1.0.0/16	Special purpose	ASR failover VNet (auto-created by Site Recovery); overlaps with spoke — do not use for routing
vnet-eshars-core	rg-eshars-core-prod	centralus	10.10.0.0/16	Active — Prod Hub	Prod VPN gateway, prod private endpoints
rg-eshars-prod	rg-eshars-prod	centralus	10.0.0.0/16	Active — Prod Spoke	Prod workloads
vn-eshars-cus	rg-eshars-centralus	centralus	10.8.0.0/16	Legacy	Unknown — needs investigation
vm-eshars-dev-investigation-vnet	rg-eshars-dev	centralus	10.6.0.0/16	One-off	Dev investigation VM only
vnet-vpngw-nonprod	rg-eshars-nonprod	eastus2	10.9.0.0/26	Deleted 2026-04-23	Leftover from old VPN gateway; GatewaySubnet was empty; not peered to hub
rg-eshars-nonprod-sql-access	rg-eshars-nonprod	eastus2	10.0.0.0/16	Deleted 2026-04-23	Orphaned PE NICs removed; db-eshars-developeraccess PE needs recreating in vnet-eshars-nonprod-cus
rg-eshars-nonprod-storage-access	rg-eshars-nonprod	eastus2	10.0.0.0/16	Deleted 2026-04-23	Was completely empty
vm-keyvault-test-vnet	rg-eshars-nonprod	eastus2	10.4.0.0/16	One-off	Key Vault test VM only
rg-eshars-dev-vnet	rg-eshars-dev	eastus2	10.3.3.0/24	One-off	Dev VNet
rg-eshars-nonprod-vnet	rg-eshars-nonprod	southcentralus	10.5.0.0/24	Legacy	Old southcentralus VNet — predates hub-and-spoke
vn-eshars-prod	rg-veshars-prod	southcentralus	10.2.0.0/16	Legacy	Old prod VNet — predates hub-and-spoke
hisd-testing-vnet	hisd-testing	westus	10.3.1.0/24	One-off	HISD testing only
ops-esh-sbox-vnet	ops-esh-sbox	eastus	10.3.0.0/24	One-off	Sandbox
vm-chris-deploytest-vnet	vm-chris-deploytest_group	eastus	10.7.0.0/16	One-off	Deploy test VM
vm-sql-access_group-vnet	vm-sql-access_group	eastus2	10.3.2.0/24	One-off	SQL access test VM

Known Address Space Conflicts (blocks peering)

• rg-eshars-nonprod-asr and rg-eshars-nonprod both use 10.1.0.0/16
• rg-eshars-nonprod-sql-access, rg-eshars-nonprod-storage-access, and rg-eshars-prod all overlap on 10.0.0.0/16

VPN Architecture

• VPN Gateway: vpngw-eshars-core-np in rg-eshars-core-np
• VNet: vnet-eshars-core-np (10.20.0.0/16, centralus)
• Protocol: OpenVPN
• Client address pool: 172.16.1.0/24
• DNS pushed to clients: 10.1.0.12 (vm-ops-nonprod, running a DNS forwarder)
• DNS suffixes: .database.windows.net, .privatelink.database.windows.net

VNet Peerings

From	To	State	Notes
vnet-eshars-core-np	rg-eshars-nonprod	Connected	Gateway transit on — legacy spoke reaches VPN
vnet-eshars-core-np	vnet-eshars-nonprod-cus	Connected	Gateway transit on — new spoke reaches VPN
vnet-eshars-nonprod-cus	rg-eshars-nonprod	Connected	Transitional — remove once old spoke is retired

VPN clients terminate in vnet-eshars-core-np and can reach both spokes via peering. Any private endpoint that needs to be reachable from VPN must be in vnet-eshars-nonprod-cus (or the hub for truly shared services — not workload-specific endpoints).

The spoke-to-spoke peering (vnet-eshars-nonprod-cus ↔ rg-eshars-nonprod) is temporary, allowing resources to communicate across both spokes during the VM migration period (HM-8556). Remove both directions once rg-eshars-nonprod VNet is empty and retired.

DNS Resolution for Private Endpoints

• vm-ops-nonprod (10.1.0.12) acts as the DNS server for VPN clients
• It has a conditional forwarder for privatelink.database.windows.net → 168.63.129.16 (Azure DNS)
• The Private DNS Zone privatelink.database.windows.net lives in rg-eshars-nonprod-network-cus and is linked to vnet-vpngw-nonprod (needs review — should also be linked to vnet-eshars-core-np)

SQL Private Endpoint Pattern

The correct pattern for nonprod SQL servers:

• Private endpoint in subnet snet-private-endpoints-eshars-nonprod-cus in vnet-eshars-nonprod-cus
• Managed in resource group rg-eshars-nonprod-data-cus
• DNS zone group linked to privatelink.database.windows.net in rg-eshars-core-np

Do not place nonprod private endpoints in the hub (vnet-eshars-core-np). The hub is for shared connectivity infrastructure only (VPN gateway, DNS). Workload-specific private endpoints belong in the workload spoke.

Work Done (2026-04-23)

Problem

db-eshars-nonprod had two private endpoints (pe-db-eshars-nonprod and private-ep-non-prod-west), both in Disconnected state with no approved connections on the SQL server side. Developers were connecting via public IP firewall rules.

What Was Done

1. Deleted the broken pe-db-eshars-nonprod endpoint
2. Recreated it in vnet-vpngw-nonprod / sn-pe-nonprod (eastus2) — this was a mistake, this VNet is not peered to the hub and VPN clients cannot reach it
3. Discovered vnet-vpngw-nonprod is isolated (not peered to vnet-eshars-core-np)
4. Deleted again and recreated correctly in snet-private-endpoints-eshars-core-np within vnet-eshars-core-np (centralus), managed in rg-eshars-nonprod-data-cus — private IP 10.20.3.5
5. Updated stale A record in privatelink.database.windows.net zone (rg-eshars-core-np) from 10.9.0.36 → 10.20.3.5
6. Verified Entra groups (sg-eshars-db-nonprod-*) exist in master on db-eshars-nonprod
7. Confirmed VPN connectivity to db-eshars-nonprod.database.windows.net working
8. Added all three groups to all 15 databases with correct roles per decision 0006:
   • Data DBs (db-eshars-dev, db-eshars-hisddev, db-eshars-qa, db-eshars-trn, db-nursing-prod, eshars_portal): reader=db_datareader, writer=db_datareader+db_datawriter, dba=db_owner
   • Log/Audit DBs (db-eshars-hisddev-log, db-log-dev, db-log-qa, db-log-trn, db-eshars-dev-audit, db-eshars-hisddev-audit, db-eshars-qa-audit, db-eshars-regression-audit01, db-eshars-trn-audit): reader=db_datareader, writer=db_datareader (read-only), dba=db_owner

Outstanding Work

• Remove individual developer IP firewall rules from db-eshars-nonprod once all devs confirmed on VPN
• Clean up vnet-vpngw-nonprod — deleted 2026-04-23
• Clean up rg-eshars-nonprod-sql-access — orphaned PEs deleted, VNet deleted 2026-04-23
• Clean up rg-eshars-nonprod-storage-access — deleted 2026-04-23
• Create proper PE for db-eshars-developeraccess in vnet-eshars-nonprod-cus so devs can connect via VPN (currently using public IP firewall rules)
• Migrate workloads off old rg-eshars-nonprod VNet → vnet-eshars-nonprod-cus (App Service, VMs, App Gateway, Bastion)
• Retire old rg-eshars-nonprod VNet once empty

Work Done (2026-04-23, continued)

New canonical workload spoke

Created vnet-eshars-nonprod-cus (10.21.0.0/16, centralus, in rg-eshars-nonprod) as the correctly-named workload spoke to replace the poorly-named rg-eshars-nonprod VNet.

• Subnet snet-private-endpoints-eshars-nonprod-cus (10.21.3.0/24) for all nonprod private endpoints
• Peered to hub with gateway transit (VPN clients can reach the spoke)
• Private DNS zone privatelink.database.windows.net linked to new VNet

Migrated both nonprod SQL private endpoints from hub to new spoke:

Endpoint	Server	Old location	New IP
pe-db-eshars-nonprod	db-eshars-nonprod	hub (10.20.3.5)	10.21.3.4
pe-sql-eshars-nonprod-cus-001	sql-eshars-nonprod-cus-001	hub (10.20.3.x)	10.21.3.5

Hub subnet snet-private-endpoints-eshars-core-np is now clear of nonprod workload resources.

Consequences

• All SQL access for nonprod should route through VPN → private endpoint, eliminating the need for public IP firewall rules per developer
• New private endpoints for any nonprod SQL server must follow the established pattern (endpoint in vnet-eshars-core-np, not in ad-hoc VNets)
• The overlapping address spaces in eastus2 VNets need a cleanup plan before any additional peerings can be established in that region