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:
Action == "Deny"withRulefilled in: an explicit rule refuses. You have the rule and collection names, now get it fixed.Action == "Deny"withActionReason == "Default Action": no rule matches, the default deny applies. An allow rule is missing.Action == "Allow": the firewall let it through. The problem is elsewhere (DNS, network security group, routing, a SQL-side rule).
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
TimeGeneratedis UTC. The user says "it broke at 2:05 pm" in local time: convert before searching. For display:datetime_utc_to_local(TimeGenerated, 'Europe/Brussels').- Logs arrive with a delay of a few minutes (plan for 5 to 15 minutes in practice; the documentation even mentions up to 30 minutes after first enabling). "Nothing in the last minutes" does not mean "nothing happened".
- Widen the window by half an hour on each side of the incident, then narrow it down.
Lessons learned
- One lost connection shows up several times. TCP retransmissions produce several rows for the same 5-tuple (source IP and port, destination IP and port, protocol). Group on that 5-tuple within a window of a few dozen seconds rather than counting per day: port reuse inflates the numbers.
- Check the table exists and has data before concluding: a missing diagnostic setting gives an empty result, not an error.
- No Deny does not mean no firewall in the path. If you see neither Allow nor Deny, the traffic probably does not go through it (routing), which is information in itself.
- The firewall is guilty most of the time, but not always. Hence the "Allow" branch of the diagram: clearing the firewall saves as much time as convicting it.
- Keep the query that worked in a
.kqlfile: next time it takes seconds, and the joke stays the same.