LLI Wiki — My Digital Knowledge Base

Who blocked my connection? Investigating denied Azure SQL connections with Azure Firewall logs in KQL

Find the rule and action denying a connection to Azure SQL using AZFWNetworkRule and AzureDiagnostics, handling time zones and ingestion lag.

"It's always the firewall." Every DBA has said it, and most of the time every DBA was right. The hard part is not suspecting it: it is proving it, with the rule, the action and the exact time. When an application can no longer reach an Azure SQL database, this is how I query the Azure Firewall logs in KQL to answer "who blocked my connection?".

The approach

flowchart TD
    A["Connection refused or timeout"] --> B["Note source IP, port, local time"]
    B --> C["Convert the time to UTC"]
    C --> D{"Structured table AZFWNetworkRule present?"}
    D -->|"yes"| E["Filter on source IP and port 1433"]
    D -->|"no"| F["AzureDiagnostics, network category, msg_s field"]
    E --> G{"Action = Deny?"}
    F --> G
    G -->|"yes"| H["Read Rule, RuleCollection, ActionReason"]
    G -->|"no, Allow"| I["Not the firewall: DNS, NSG, routing, SQL"]
    H --> J["Get the rule fixed by the firewall owner"]

Where the logs are

Azure Firewall can write in two formats, which coexist if you create two diagnostic settings:

Mode Table Benefit
Resource specific (recommended) AZFWNetworkRule, AZFWApplicationRule typed columns, faster queries, lower cost
Legacy AzureDiagnostics free-form message in msg_s, filter on Category

For an Azure SQL database, traffic to port 1433 (or the redirect ports) is network traffic, so start with AZFWNetworkRule. AZFWApplicationRule covers FQDN traffic (HTTP/HTTPS), for example a tool downloading a module or calling an API.

Useful columns of AZFWNetworkRule (checked against the Microsoft documentation): TimeGenerated, SourceIp, SourcePort, DestinationIp, DestinationPort, Protocol, Action, ActionReason, Policy, RuleCollectionGroup, RuleCollection, Rule.

The base query

let client = "10.0.0.25";          // application source IP (example)
let port   = 1433;
AZFWNetworkRule
| where TimeGenerated > ago(2h)
| where SourceIp == client and DestinationPort == port
| project TimeGenerated, SourceIp, SourcePort, DestinationIp, DestinationPort,
          Protocol, Action, ActionReason, Policy, RuleCollectionGroup, RuleCollection, Rule
| order by TimeGenerated desc

Three possible readings:

Summarise instead of reading rows

To see which rules deny what, aggregate:

AZFWNetworkRule
| where TimeGenerated > ago(24h) and Action == "Deny"
| summarize Hits = count(), First = min(TimeGenerated), Last = max(TimeGenerated)
    by SourceIp, DestinationIp, DestinationPort, Rule, ActionReason
| order by Hits desc

And for the legacy table, filtering is done on the text:

AzureDiagnostics
| where ResourceType == "AZUREFIREWALLS" and Category == "AzureFirewallNetworkRule"
| where msg_s has "10.0.0.25" and msg_s has ":1433"
| project TimeGenerated, msg_s
| order by TimeGenerated desc

Time zones and ingestion lag

Lessons learned

Mastodon LLIMastodon SocialFosstodon