Apr 11 2019
- last edited on
Apr 07 2022
I need a Help in writing a Query to create a alert to get IP's Blocked by Firewall in Azure SQL Database.
Apr 15 2019 07:00 AM
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"
Apr 15 2019 11:16 AM
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.
| 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 :)
Apr 15 2019 12:14 PMSolution
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.
Apr 15 2019 04:46 PM
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"
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