Forum Discussion
Solved
Log Analytics query to create a alert for IP's blocked by firewall in Azure SQl Database.
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.
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.
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 :)
CliveWatson
Microsoft
Microsoft
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.
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
