Audit Logs for Azure SQL Database
Diagnostic settings
As a part of Diagnostic policies deployed over the subscription, the diagnostic settings are already getting enabled on all the Azure SQL databases. These diagnostic settings are on the database level and not on the server level.
Diagnostic settings deployed on the database level has all the categories of logs enabled as shown below:
Although all the log categories are enabled under the diagnostic settings, still while querying the LAW, we could not find any logs related to SQL Security Audit Event Category. For this, some addtional configuration needs to be done.
Auditing
- We have Auditing option under Security tab for both SQL server and SQL databases.
- While enabling the auditing, the destination can be selected from various optins like storage, event hub or log analytics workspace.
- If auditing is enabled on the SQL server, it will automatically get enabled on all SQL databases associated to that server. The database shown below inherited the auditing setting from the server.
- The default auditing settings include the following set of action groups, which will audit all the queries and stored procedures executed against the database, as well as successful and failed logins:
BATCH_COMPLETED_GROUP
SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP
FAILED_DATABASE_AUTHENTICATION_GROUP
- When Auditing is configured to a Log Analytics workspace or to an Event Hubs destination in the Azure portal or PowerShell cmdlet, a Diagnostic Setting is created with SQLSecurityAuditEvents category enabled, if it is not present already.
- After the auditing is enabled, we could see the logs of the category SQLSecurityAuditEvents going to the AzureDiagnostics table of Log Analytics Workspace.
- We can see the query against the database in statement_s column.
Note: This log category is generating a huge number of logs (one instance generated nearly 28000 records per minute) and that incurs a huge cost.
Clients need to decide if they want to send these logs inside the LAW.