Auditing is a very important feature and should be configured for every database or server in your deployment in Azure. It’s important to know how to configure, view and maintain auditing data for your SQL database. In this blog we will talk about how to configure audit for Azure SQL Database. Like SQL Server, Azure SQL database also supports both server level auditing and database level auditing.
When server level auditing is enabled, it’s enabled for all existing databases and new databases which will be created in future. So, it’s important to understand when to enable server audit and database audit.
Enable Server audit when you must audit all databases for that logical server, enable database level audit when you want audit different action groups for a specific database or write to different target for a specific database.
If you are enabling both server and database level audit for a database, then you can choose predicate expression to filter the events to ensure you are not capturing duplicate data.
Server level and database level auditing can be enabled from portal and when you enable from portal the below audit action groups will be enabled by default for both server and database audits.
Default Action groups for Azure SQL database:
BATCH_COMPLETED_GROUP This event is raised whenever any batch text, stored procedure, or transaction management operation completes executing. It is raised after the batch completes and will audit the entire batch or stored procedure text, as sent from the client, including the result.
FAILED_DATABASE_AUTHENTICATION_GROUP Indicates that a principal tried to log on to a contained database and failed. Events in this class are raised by new connections or by connections that are reused from a connection pool
SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP Indicates that a principal successfully logged in to a contained database.
For more audit action groups please refer Audit action groups.
Server level auditing:
From Azure portal, go to the server security blade -> auditing and select Enable Azure SQL auditing. This will enable the server level audit for all databases
When you enable server level audit you can choose the log destination as Storage, log analytics or Event Hub.
Enable Server audit to storage account
Enable auditing to Log analytics
Enable auditing to Event hub
Database Level auditing:
To enable database level audit, In the azure portal go to database security blade -> auditing and select enable Azure SQL auditing. This will enable database level auditing. In the below screen shot you can see server level audit is disabled. but for your server if server level audit is enabled then you must check the reason why you still need database level audit.
For database level audit also, you can choose audit log destination as storage, Log analytics or Event hub.
Auditing Microsoft support operations:
You can also audit Microsoft support operations as we maintain your databases. Any action that’s performed by devops can be audited and written to the audit log for your reference.
From portal , server auditing blade you can select enable auditing Microsoft support operations and use different audit log destinations .
View Audit Logs:
Once auditing is enabled the server logs and database logs can be viewed from the portal.
In this blog we discussed enabling default server and database audit for Azure SQL database and view audit logs using azure portal. In the next blog we will discuss about using PowerShell cmdlets to modify, overwrite audit settings to use more precise action groups or filter actions /filter schemas based on your business needs.
Microsoft
