8000 Incident: AppGW Unhealthy Backend — SQL Public Network Access Disabled by Azure Policy (agw-v2rvvka7akn4m) · Issue #12 · dohl-ms/grubify · GitHub
[go: up one dir, main page]
Skip to content
10000

Incident: AppGW Unhealthy Backend — SQL Public Network Access Disabled by Azure Policy (agw-v2rvvka7akn4m) #12

New issue
New issue
Open
Open
Incident: AppGW Unhealthy Backend — SQL Public Network Access Disabled by Azure Policy (agw-v2rvvka7akn4m)#12
Labels
bugSomething isn't workingincidentinfrastructurerecurringseverity-high

Description

@dohl-ms
dohl-ms
opened on May 21, 2026

Incident Report: AppGW Unhealthy Backend — SQL Public Network Access Disabled by Azure Policy

  • Incident ID: 37e98e6b-c2d1-41fe-954d-4ea43d4ef000
  • Service: Azure Application Gateway — agw-v2rvvka7akn4m (rg: rg-sredemo2)
  • Subscription: b32ee9e1-20dc-49b8-a81a-a0fbd25bd27a
  • Backend Web App: app-v2rvvka7akn4m
  • SQL Server: sql-v2rvvka7akn4m (FQDN: sql-v2rvvka7akn4m.database.windows.net)
  • Database: sredemodb
  • Alert Rule: alert-appgw-unhealthy-backend
  • Severity: Sev1

Summary

Azure Policy deployIfNotExists silently disabled SQL Server public network access on sql-v2rvvka7akn4m at ~19:02 UTC on 2026-05-21. This caused the Web App's /Health/Probe endpoint (in HealthController.Probe) to fail with Microsoft.Data.SqlClient.SqlException: "Deny Public Network Access is set to Yes", returning HTTP 503. The Application Gateway agw-v2rvvka7akn4m health probe detected the 503 responses and marked the backend pool as unhealthy, triggering the Sev1 alert at 19:07:42 UTC.

Impact

Timeline (UTC)

Time (UTC) Event
~19:02:28 Azure Policy deployIfNotExists action started on sql-v2rvvka7akn4m
~19:02:28 Azure Policy auditIfNotExists action started on sql-v2rvvka7akn4m
~19:02:28 Microsoft.Sql/servers/write initiated by admin@MngEnvMCAP559583.onmicrosoft.com (policy-triggered)
~19:02:45 First SqlException — "Deny Public Network Access is set to Yes" (HealthController.Probe)
~19:02:52 Microsoft.Sql/servers/write succeeded — publicNetworkAccess set to Disabled
~19:02:00 Health/Probe transitions: 4× 200 + 3× 503 in same minute
~19:03:00 Health/Probe 100% failure — 6× 503/min continuously
~19:07:42 AppGW unhealthy backend alert fires (Sev1)
~19:10:19 Last recorded SqlException
~19:13:23 Remediation applied — SQL publicNetworkAccess re-enabled to Enabled

Evidence

Health Probe Responses (App Insights — requests table)

Health/Probe returned steady 200 OK (6 req/min) from 18:10–19:01 UTC. At 19:02, the first 503 errors appeared (3 out of 7 requests). From 19:03 onward, 100% of probe requests returned 503.

KQL: requests | where name contains "Health" or name contains "Probe" | summarize count() by bin(timestamp, 1m), resultCode

SQL Exceptions (App Insights — exceptions table)

Type Problem ID Message Count First Seen Last Seen
Microsoft.Data.SqlClient.SqlException HealthController+Probe Deny Public Network Access is set to Yes 47 19:02:45 19:10:19
Microsoft.Data.SqlClient.SqlException TdsParser.ThrowExceptionAndWarning Deny Public Network Access is set to Yes 1 19:06:08 19:06:08
Microsoft.Data.SqlClient.SqlException HomeController+Index Deny Public Network Access is set to Yes 1 19:06:08 19:06:08

SQL Dependency Failures (App Insights — dependencies table)

Target Failures Avg Duration First Seen Last Seen
tcp:sql-v2rvvka7akn4m.database.windows.net,1433 | sredemodb 48 162.3ms 19:02:45 19:10:19

Activity Log — Policy Actions

Time (UTC) Operation Status Caller
19:02:28 Microsoft.Authorization/policies/deployIfNotExists/action Started admin@MngEnvMCAP559583.onmicrosoft.com
19:02:28 Microsoft.Authorization/policies/auditIfNotExists/action Started admin@MngEnvMCAP559583.onmicrosoft.com
19:02:28 Microsoft.Sql/servers/write Accepted → Started admin@MngEnvMCAP559583.onmicrosoft.com
19:02:52 Microsoft.Sql/servers/write Succeeded (system)

SQL Server State (at time of investigation)

{
  "publicNetworkAccess": "Disabled",
  "state": "Ready",
  "fqdn": "sql-v2rvvka7akn4m.database.windows.net"
}

Timeline Chart

Incident Timeline — Health Probe 503s and SQL Exceptions

Root Cause

Azure Policy with deployIfNotExists effect periodically evaluates the SQL Server sql-v2rvvka7akn4m and sets publicNetworkAccess to Disabled. This severs the Web App's SQL connectivity, causing HealthController.Probe to throw SqlException and return HTTP 503. The Application Gateway health probe detects the 503 and evicts the backend from the pool, resulting in total traffic failure.

This is a recurring issue. The Azure Policy continues to fire periodically, re-disabling public access each time. Manual re-enablement is not a durable fix. Previous occurrences were documented in Issues #9 and #10 in related environments, and no long-term remediation was applied.

Remediation

Immediate (Applied)

  • SQL public network access re-enabled at ~19:13 UTC via az rest --method patch setting publicNetworkAccess: Enabled
  • Health probes expected to recover within the next probe interval cycle (~30s)

Permanent (Required — pick at least one)

  • Code: Make HealthController.Probe resilient to transient SQL failures — return degraded health (200 with warning) instead of 503 when SQL is temporarily unreachable, or implement a separate liveness probe that doesn't depend on SQL
  • Policy: Add a policy exemption for sql-v2rvvka7akn4m to prevent the deployIfNotExists policy from toggling publicNetworkAccess, OR change the policy effect from deployIfNotExists to audit
  • Platform: Implement a Private Endpoint for SQL Server connectivity, eliminating the dependency on public network access entirely
  • Observability: Add an Azure Monitor alert on Microsoft.Sql/servers/write Activity Log events targeting publicNetworkAccess property changes, to detect policy-driven changes within minutes

Action Items

# Action Priority
1 Add Azure Policy exemption for sql-v2rvvka7akn4m OR change policy effect to audit Critical
2 Implement Private Endpoint for SQL Server to eliminate public network dependency High
3 Make HealthController.Probe resilient to transient SQL connectivity failures High
4 Add Activity Log alert for SQL publicNetworkAccess property changes Medium
5 Review all SQL servers in subscription for same policy exposure Medium
6 Consider separate liveness vs. readiness probes in the AppGW configuration Low

References

  • Application Gateway: /subscriptions/b32ee9e1-20dc-49b8-a81a-a0fbd25bd27a/resourceGroups/rg-sredemo2/providers/Microsoft.Network/applicationGateways/agw-v2rvvka7akn4m
  • SQL Server: /subscriptions/b32ee9e1-20dc A332 -49b8-a81a-a0fbd25bd27a/resourceGroups/rg-sredemo2/providers/Microsoft.Sql/servers/sql-v2rvvka7akn4m
  • Web App: /subscriptions/b32ee9e1-20dc-49b8-a81a-a0fbd25bd27a/resourceGroups/rg-sredemo2/providers/Microsoft.Web/sites/app-v2rvvka7akn4m
  • App Insights: /subscriptions/b32ee9e1-20dc-49b8-a81a-a0fbd25bd27a/resourceGroups/rg-sredemo2/providers/Microsoft.Insights/components/appi-v2rvvka7akn4m
  • Log Analytics Workspace: /subscriptions/b32ee9e1-20dc-49b8-a81a-a0fbd25bd27a/resourceGroups/rg-sredemo2/providers/Microsoft.OperationalInsights/workspaces/log-v2rvvka7akn4m
  • Alert: /subscriptions/b32ee9e1-20dc-49b8-a81a-a0fbd25bd27a/resourceGroups/rg-sredemo2/providers/Microsoft.Insights/metricAlerts/alert-appgw-unhealthy-backend
  • Azure Portal Alert: View Alert

This issue was created by sre-agent-ubqf2fbkeowr4--a4798a0c
Tracked by the SRE agent here

Activity

  1. dohl-ms
    added
    bugSomething isn't working
    severity-high
    incident
    infrastructure
    recurring
    on May 21, 2026

  2. dohl-ms commented on May 21, 2026

    @dohl-ms
    dohl-ms
    on May 21, 2026
    OwnerAuthor

    🤖 Azure SRE Agent

    Runbook: AppGW Unhealthy Backend — SQL Public Network Access Disabled by Policy

    A comprehensive troubleshooting runbook has been prepared for this recurring incident. The runbook is committed locally on branch docs/appgw-sql-policy-runbook at path docs/runbooks/appgw-sql-policy-unhealthy-backend.md but could not be pushed due to missing git credentials in the automation environment.

    Quick Diagnostic Steps (for future occurrences)

    Step 1 — Check App Insights Exceptions (fastest signal):

    exceptions
| where timestamp > ago(1h)
| where type == "Microsoft.Data.SqlClient.SqlException"
| summarize Count=count(), FirstSeen=min(timestamp), LastSeen=max(timestamp)
  by type, problemId, outerMessage
| order by Count desc

    If you see "Deny Public Network Access is set to Yes" — confirmed. Skip to remediation.

    Step 2 — Verify SQL Server State:

    az rest --method get \
  --url "https://management.azure.com/<sql-server-resource-id>?api-version=2023-08-01-preview" \
  -o json --query "{publicNetworkAccess:properties.publicNetworkAccess, state:properties.state}"

    Step 3 — Immediate Remediation:

    az rest --method patch \
  --url "https://management.azure.com/<sql-server-resource-id>?api-version=2023-08-01-preview" \
  --body '{"properties":{"publicNetworkAccess":"Enabled"}}'

    Permanent Fix Required

    This is the 3rd occurrence of this exact incident. Manual re-enablement is not durable — the Azure Policy will re-fire. Implement at least one:

    # Fix Priority
    1 Add Azure Policy exemption for the SQL server Critical
    2 Implement Private Endpoint for SQL Server High
    3 Change policy effect from deployIfNotExists to audit High
    4 Make HealthController.Probe resilient to transient SQL failures Medium

    🔧 Status: Immediate remediation applied — permanent fix still required

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugSomething isn't workingincidentinfrastructurerecurringseverity-high

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions
      0