Configure Auditing for Azure SQL Database series - part1
Published Mar 28 2022 09:45 PM 2,230 Views
Microsoft

 

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

 

 

SravaniSaluru_0-1648528535379.png

 

 

SravaniSaluru_1-1648528535393.png

 

 

SravaniSaluru_2-1648528535401.png

 

 When you enable server level audit you can choose the log destination as Storage, log analytics or Event Hub.

 

SravaniSaluru_3-1648528535406.png

 

Enable Server audit to storage account

 

SravaniSaluru_4-1648528535422.png

 

Enable auditing to Log analytics

 

 

SravaniSaluru_5-1648528535430.png

 

Enable auditing to Event hub

 

SravaniSaluru_6-1648528535446.png

 

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.

 

SravaniSaluru_7-1648528535452.png

 

 

SravaniSaluru_8-1648528535469.png

 

For database level audit also, you can choose audit log destination as storage, Log analytics or Event hub.

 

SravaniSaluru_9-1648528535482.png

 

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 .

 

SravaniSaluru_10-1648528535488.png

 

View Audit Logs:

 

Once auditing is enabled the server logs and database logs can be viewed from the portal.

 

SravaniSaluru_11-1648528535496.png

 

 

SravaniSaluru_12-1648528535504.png

 

 

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.

 

 

 

 

 

 

 

 

 

Co-Authors
Version history
Last update:
‎Apr 13 2022 03:39 AM
Updated by: