I'm looking for advice on how to effectively audit a SQL database hosted in Azure. Specifically, I'd like to track events like failed logins and database locks. I've noticed there's an option for Azure SQL Auditing, with choices on where to store the logs, such as a storage account, log analytics workspace, or event hub. Since we're new to Azure logging, I'm curious about the most cost-effective solution for storing logs. Additionally, is it possible to forward these logs to an on-premises Splunk server? And can Azure generate email alerts based on certain events?
3 Answers
Definitely, a Storage Account is your most economical option. It just saves the traditional audit logs in blob storage, and you can access them with tools like SSMS. Log Analytics offers more sophisticated features for querying and alerting but can get pricey if you're generating a lot of logs. For serious security tracking, like failed login attempts, look into using Microsoft Defender for Azure.
If you're after the most budget-friendly option, go with a Storage Account. It will store logs in a basic format, and unless you have Splunk do the parsing, they won't be easy to search. For alerts, you can set that up using Azure Monitor along with log analytics and alert rules.
You're on the right track looking into Azure SQL Auditing. The cost and effectiveness of your logging really depend on where you decide to send the logs. Generally, using a Storage Account is the cheapest option for basic retention of raw logs, while Log Analytics gives you better insights and the ability to create alerts. Event Hub is best for streaming logs to an external SIEM like Splunk. Decide whether you need long-term logs, real-time alerts, or both to figure out your best setup.
Just a note: if you're using Log Analytics for alerting purposes, you can send logs to both a storage account and Log Analytics. Make sure to filter carefully to avoid unnecessary costs and keep a short retention period. If it’s just for triggering alerts in your SIEM, you should focus your logs there.