We are excited to announce a significant update to the server audit feature for Azure SQL Database. We have re-architected major portions of SQL Auditing resulting in increased availability and reliability of server audits. As an added benefit, we have achieved closer feature alignment with SQL Server and Azure SQL Managed Instance. Database auditing remains unchanged.
In the remainder of this blog article, we cover
- Functional changes
- Changes Affecting customers
- Sample queries
- Call for action
- Implementation and Notification
- Time-based Filtering
Functional Changes
In the current design when server audit is enabled, it triggers a database level audit and executes one audit session for each database.
With the new architecture, enabling server audit will create one extended event session at the server level that captures audit events for all databases.
This optimizes memory and CPU and is consistent with how auditing works in SQL Server and Azure SQL Managed Instance.
Changes Affecting Customers
- Folder Structure change for storage account
- Folder structure change for Read-Only replicas
- Permissions required to view Audit logs
One of the primary changes involves the folder structure for audit logs stored in storage account containers. Previously, server audit logs were written to separate folders, one for each database, with the database name serving as the folder name. With the new update, all server audit logs will be consolidated into a single folder which is ‘Master’ folder. This behavior is the same as Azure SQL Managed Instance and SQL Server
For Read-Only database replicas, which previously had their logs stored in a read-only folder, those logs will now also be written into the Master folder. You can retrieve these logs by filtering on the new column ‘is_secondary_replica_true’.
Please note that the audit logs generated after deployment will adhere to the new folder structure, while the existing audit logs will stay in their current folders until their retention periods expire.
Sample Queries
To help you adopt these changes in your workflows, here are some sample queries:
Current |
New |
To Query audit logs for a specific database called "test" |
|
SELECT * FROM sys.fn_get_audit_file |
SELECT * FROM sys.fn_get_audit_file |
To query audit logs for test database from read only replica |
|
SELECT * FROM sys.fn_get_audit_file |
SELECT * FROM sys.fn_get_audit_file |
Permissions |
|
Control database |
Control Server |
Implementation and Notifications
We are rolling out this change region-wise. Subscription owners will receive notifications with the subject “Update your scripts to point to a new folder for server level audit logs” for each region as the update is implemented. It is important to update any scripts that refer to the folder structure to retrieve audit logs based on the database name for the specific region. Note that this change applies only to server-level auditing; database auditing remains unchanged.
Call for Action
These actions apply only to customers who are using storage account targets. No action is needed for customers using Log Analytics or Event hubs.
- Folder references: Change the reference for audit logs from the database name folder to the Master folder and use specific filters to retrieve logs for a required database.
- Read -Only Database Replicas: Update references for audit logs from the Read-Only replica folder to the Master folder and filter using the new parameter as shown in the examples.
- Permissions: Ensure you have the necessary control server permissions to review the audit logs for each database using fn_get_audit_file.
Manual Queries
This update also applies to manual queries where you use fn_get_audit_file to retrieve audit logs from the storage account
Time-based filtering
To enhance your ability to query audit logs using filters, consider using efficient time-based filtering with the fn_get_audit_file_v2 function. This function allows you to retrieve audit log data with improved filtering capabilities. For more details, refer to the official documentation here.
Updated Jan 15, 2025
Version 2.0sravani-saluru
Microsoft
Joined October 22, 2018
Azure SQL Blog
Follow this blog board to get notified when there's new activity