Auditing Azure SQL Database

This is a part of series “Stairway to being an Azure SQL DBA“, where I will be covering all the topics that an Azure SQL DBA should know about.

Auditing is an important part of IT field, irrespective in which field you are in you must have either heard about it or have been a part of Audits.

In On-Prem SQL Server you need to create Audits, Database/Server specification and then enable them to capture the information.

When talking about Azure SQL Database you have the option to enable the auditing at SQL Server level, when doing this any existing database or any newly created database is automatically added for auditing, or can be enabled at database level. Make sure you do not enable auditing at both SQL Server and Database level as the same information is captured irrespective of from where you are starting it.

In On-prem environment we have the option of saving the Audits to a file, Windows Security event or the Windows Application event log. Here in Azure SQL Database you have the option to save in Storage Account, Log Analytics and Event Hub. The last two options are in Preview, but any or all the three options can be chosen at one time.

In some future blog I’ll cover Log Analytics in more detail.

Events Captured in Azure SQL Database Audits

The different action groups, apart from login failures or success, that can be captured are:

1. BATCH_STARTED_GROUP, BATCH_COMPLETED_GROUP,
2. APPLICATION_ROLE_CHANGE_PASSWORD_GROUP,
3. BACKUP_RESTORE_GROUP, DATABASE_LOGOUT_GROUP,
4. DATABASE_OBJECT_CHANGE_GROUP,
5. DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP,
6. DATABASE_OBJECT_PERMISSION_CHANGE_GROUP,
7. DATABASE_OPERATION_GROUP, AUDIT_CHANGE_GROUP,
8. DATABASE_PERMISSION_CHANGE_GROUP,
9. DATABASE_PRINCIPAL_CHANGE_GROUP,
10. DATABASE_PRINCIPAL_IMPERSONATION_GROUP,
11. DATABASE_ROLE_MEMBER_CHANGE_GROUP,
12. FAILED_DATABASE_AUTHENTICATION_GROUP,
13. SCHEMA_OBJECT_ACCESS_GROUP, SCHEMA_OBJECT_CHANGE_GROUP,
14. SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP,
15. SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP,
16. SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP,
17. USER_CHANGE_PASSWORD_GROUP

Configuring Auditing for Azure SQL Database

As stated above Audits can be enabled at Azure SQL Server level or Azure SQL Database level.

Go to SQL Server or Azure SQL Database and click on Auditing under the Security tab.

Viewing Audit logs for Azure SQL Database

There are different way to checking the Audit logs depending upon what is the destination for storing Audits.

The easiest way is to use the “View Audit logs” in Auditing section.

Enabling Azure SQL Database Audits using PowerShell

To Create Auditing Policy at Database or Server level you can use the
Set-AzSQLDatabaseAudit or Set-AzSQLServerAudit.

Enabling at SQL Server level.

If you want to enable Audits with the same configuration as did using GUI you need the below information.

1. Resource Group Name
2. SQL Server Name
3. Storage Account ResourceId

When you have all this information use the below script.

Set-AzSqlServerAudit -ResourceGroupName "4Blog" -ServerName "ssSQLServer1" -BlobStorageTargetState Enabled -StorageAccountResourceId "/subscriptions/xasjdhajshduasuhhhsad/resourceGroups/resourcegroup01/providers/Microsoft.Storage/storageAccounts/4blogdiag"

Summary

Auditing for Azure SQL Database can be enable at SQL Server or Database level, using GUI or PowerShell.