How to enable Auditing for Azure SQL Database

In many scenarios enabling Auditing could help DBA to find the root cause or protect their databases. For most companies Auditing is a “Must”! it could track who login to the database, which user modify the data and so many other benefits. I highly recommended to enable it if you haven’t done before. In this article I’ll explain how you can enable Auditing for Azure SQL Database step by step and how you can use it.

Data auditing could log in in Storage accounts, Log Analytics, or Event Hubs.

You can enable Audit for specifics database or in a server level for all databases.

Enable Auditing

Enabaling audit is very simple and you can do it in a few steps.

From Azure Portal go to SQL Server page then in the lest blade and from the “Security” choose “Auditing” like the following screenshot.

Then enable Azure SQL Auditing, as I mentioned earlier there are 3 options to log, Storage/Log Analytics and Event Log. Let’s do this with Storage.

I already have created an storage account but if don’t have you can do it from this page by clicking on “Create new”

From “Advanced properties” you’ll be able to choose the retention days, in this case I choose 30 days then save it.

There are two options for authenticating to Storage account, managed identity and storage access keys. For managed identity, system and user managed identity is supported. By default, the primary user identity assigned to the server is selected. If there is no user identity, then a system assigned identity is created and used for authentication purposes. After you have chosen an authentication type, select a retention period by opening. See more details here in the referral link.

For now, I choose “Storage Access Keys” then click on the save button on the top.

How can we see Audit Log for Azure SQL Database?

For checking the log go to SQL Database page in the portal then choose “Auditing” from the left blade:

As you see in the following screenshot there are some records and if click on any of them you can see the details like principal name, client IP and the statement that ran in the database. You also will be able to run this in Query Editor.