Log Analytics query to create a alert for IP's blocked by firewall in Azure SQl Database.

[Syed_Aman]

Hi All,

I need a Help in writing a Query to create a alert to get IP's Blocked by Firewall in Azure SQL Database.

[#2]

@Syed_Aman 

I think we need to know more. 

Are you trying to see what Firewall deny messages have occurred and to write them into SQL? 

What Firewall are you using, as they store the logs in different tables in Log Analytics?

Azure Firewall - example 

AzureDiagnostics
| where ResourceType == "AZUREFIREWALLS"
| where Category == "AzureFirewallApplicationRule"
| where msg_s has "Deny"   // only see deny
| parse msg_s with * "from" ipa ":" *  // get just IP address 
| project ipa, msg_s

Cisco - for example 

CommonSecurityLog
| where DeviceVendor == "Cisco"

[#3]

Thanks you @CliveWatson .

Let me explain. i have azure SQL database (PaaS) in our environment and i have enabled the diagnostics setting and configured to send the logs to Log analytics.

I have whitelisted few IP ranges in SQL firewall to access the database.

I need to write a query in Log Analytics to trigger a alert if any external user is trying to access the database and got blocked by the SQL firewall.

I need to fetch those details by using the Log Analytics query.

I had created a sample query to collect 3 consecutive failed connection while access the database.

Query:

AzureDiagnostics
| where ResourceProvider == "MICROSOFT.SQL" | where ResourceType == "SERVERS/DATABASES" | where ResourceGroup == "AZRG-OC-TDS-STORAGE" | distinct LogicalServerName_s, event_time_t, action_name_s, client_ip_s, server_principal_name_s, application_name_s, host_name_s, TimeGenerated | where action_name_s == "DATABASE AUTHENTICATION FAILED" | where TimeGenerated > ago(5m) | summarize logoncount = count() by client_ip_s | where logoncount > 3

Need help to create a query to fetch the IP details which got blocked by SQL firewall in order to trigger a alert.

Thanks in Advance :)

[#4 · Accepted solution]

@Syed_Aman 

Ah! I think you need https://docs.microsoft.com/en-us/azure/sql-database/sql-database-firewall-configure 
"You can use SQL Database Auditing to audit server-level and database-level firewall changes."

It also might be worth looking in the AzureActivity log - sorry I don't have any DBs configured with Auditing or DB level firewalls.   If you get an example record we can help you parse for the  IP address if needed.

[#5]

@CliveWatson 

Adding to what Clive has suggested the following website article gives a little more detail on what to "query" for with regards to the "Denied Access"

https://www.sqlservercentral.com/articles/10-steps-to-securing-your-sql-server

This is a snippet from the article

I also like to turn on auditing of any type of permission denied

error, like #229. If you find all the items you’d like to audit, you can write a

script to update the sysmessages table (which holds all the SQL Server errors)

to turn on logging as shown below:

 — Error Message #229: %ls permission denied on object ‘%.*ls’,

database ‘%.*ls’, owner ‘%.*ls’.

UPDATE sysmessages SET dlevel = (dlevel | 0x80) WHERE error = 229