Are you a guardian of your domain? Lean how to leverage your aptitude in security to protect Microsoft Azure technologies, with a goal of earning the Microsoft Certified: Azure Security Engineer Associate certification!
Auditing in Azure SQL captures database events such as logins, schema changes, and data modifications. These logs help meet security and compliance requirements and provide insight into performance and operational monitoring. You can enable auditing at both the server level and the database level to record actions across your environment. The audit events are written to a secure Azure Storage container, which you can later analyze for suspicious activity or compliance reporting.
You configure auditing through the Azure portal, PowerShell, CLI, or ARM templates. In the portal, navigate to your SQL server or database, select Auditing, turn it on, and choose the storage target. You can also use T-SQL or SSMS by specifying the container URL and a generated SAS token or by assigning a managed identity. These methods keep the audit logs secure without exposing the storage account to public access. The chosen storage account must be in the same region as the SQL server to avoid cross-region latency and costs.
When selecting a storage target, create a private blob container with no public access. Optionally, configure an immutable storage policy to make logs tamper-proof. If the storage account sits behind a Virtual Network or firewall, enable the Allow trusted Microsoft services setting and assign the Storage Blob Data Contributor role to the server’s managed identity. This setup allows Azure SQL to write audit logs while keeping the storage private. Note that if you move a storage account behind a firewall after auditing is already enabled, you must resave the audit settings to restore write access.
Before enabling auditing, verify these prerequisites:
StudyCerts
StudyCertsMicrosoft.Authorization/roleAssignments/write permission on the storage accountTo balance performance, compliance, and operational monitoring, tune the audit settings. Define specific audit action groups and categories to record only the events that matter. Set a retention period that matches your compliance requirements while controlling log volume. Apply log-filtering rules to exclude low-value statements, which reduces storage usage and minimizes performance overhead. By fine-tuning these settings, you maintain a detailed audit trail without unnecessary cost or slowdown.
Prepare and test your skills
Prepare and test your skills
The target must be a general-purpose v2 or premium BlockBlobStorage account located in the same tenant and region as the SQL server. Administrators must possess the Microsoft.Authorization/roleAssignments/write permission on the storage account, and firewall or Virtual Network settings must allow trusted Microsoft services.
When a storage account sits behind a Virtual Network or firewall, administrators must enable the Allow trusted Microsoft services setting and assign the Storage Blob Data Contributor role to the SQL server's managed identity. If an existing storage account target is moved behind a firewall after auditing is enabled, the audit settings must be resaved to restore write access.
Azure SQL audit settings can be tuned by defining specific audit action groups and categories to record only necessary events. Applying log-filtering rules to exclude low-value statements and establishing appropriate retention periods also reduce storage usage and minimize performance overhead.