Auditing, monitoring and alerting are security & performance tools that track activities inside a database and generates log events and alerts when a predefined event occur.
- Auditing
Auditing is used to log certain database activities to meet regulatory requirements in order to understand anomalies and security violations. Database auditing in Azure are written to Azure Storage account, Log Analytics workspace or Event Hub. During very high activity or network load, Azure SQL database allows operations to proceed without capturing a trail in order not to degrade operation.
Azure auditing policy can be defined in database or server level, if server level auditing is enabled, it will automatically audit all databases within it regardless of the database level audit setting, therefore it is recommended to enable database level auditing only when specific database level event type is required to be edited on specific database.
SQL database auditing is used
- Retain an audit trail of selected database events
- Report on database activities in a dashboard
- Analyze audit trail to find suspicious events and unusual activities
Audit logs are collected and analyzed in three ways
- Storage Account
- Log Analytics
- Event Hub
- Azure Storage: Audit logs in Azure Blog Storage are written to “Append Blobs” in .xel format, this file can be views in SQL Server Management Studio through Extended Event Files “sys.fn_xe_file_target_read_file“
- Log Analytics: Log Analytics is an Azure application used to run query against Azure Monitoring logs in order to analyze it interactively. In order to write audit logs to a Log Analytics, you need to create Log Analytics Workspace prior to enabling audit log.
- Event Hub: Azure Event Hub is a data streaming platform and event ingestion service. Audit data will be processes and transferred to a third party security monitoring application such as Splunk, QRadar & Elastic Search in real-time in order to detect anomaly .
2. Monitoring
Azure SQL provides multiple tools to monitor database performance and associated resource utilization using Azure monitoring metrics, SQL Analytics, Query Performance Insight, as well as database specific features such as DMV’s, Extended Events, Query Store & Query Profiling.
Monitoring Metrics: Azure Monitoring Metrics provides the ability to monitor various database resource metrics on resource utilization such as CPU, MEMORY, IO, DTU, Connection, Deadlock etc
Azure SQL Analytics: provides monitoring capability through Log Analytics by collecting and visualizing database performance metrics such as errors, timeouts, blocks, deadlocks, waitstats etc with the ability to create custom monitoring rules across multiple databases
Query Performance Insight: provides intelligent query analysis to help identify long running queries & top resource consuming queries in workload.
Intelligent Insights: Intelligent insights is a proactively monitors & analyzes database performance by comparing the database workload from the last hour with the past 7 days baseline workload. When performance degradation issue is detected from multiple observed metrics by using artificial intelligence, analysis is performed automatically, and log is generated with an intelligent insight on what is happening with your database. It makes it easier to track the database performance issue by tracking detected issues from its lifecycle from detection to verification and resolution/recommendation.
3. Alerting
Alerts are generated when an imports event, condition reaches a threshold value and created an action such as email notification, Azure function or other third party activity. Alerts in Azure SQL database are triggers through database diagnostic telemetry such as diagnostic logs. are advanced threat protection inside Azure Security Center.
Filtering Azure Audit Events
Azure database audit is based on audit action items on the database level and audit action groups on the server level. Out of the box, Azure audit will capture SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP, FAILED_DATABASE_AUTHENTICATION_GROUP & BATCH_COMPLETED_GROUP events. These events will audit almost all activities in the database server and generate large number of log file.
The best practice in auditing is to capture relevant events only as it makes processing the data much easier, so how do you do that as Azure Audit doesn’t provide a GUI based audit event configuration on the Azure portal?
Microsoft Azure provides a PowerShell cmdlet to change the audit action group items and action items for the events that need to be captured. Get-AzSqlServerAudit lists the current server level audit configuration where as Get-AzSqlDatabaseAudit lists the current database level audit items.
With Set-AzSqlServerAudit and Set-AzSqlDatabaseAudit cmdlets provide to change the audit action item groups and action items in the database server and database level.
