Get started with SQL database auditing

8 min to read Contributors

Azure SQL Database Auditing tracks database events and writes them to an audit log in your Azure Storage account.

Auditing can help you maintain regulatory compliance, understand database activity, and gain insight into discrepancies and anomalies that could indicate business concerns or suspected security violations.

Auditing enables and facilitates adherence to compliance standards but doesn't guarantee compliance. For more information about Azure programs that support standards compliance, see the Azure Trust Center.

Azure SQL Database Auditing overview

SQL Database Auditing allows you to:

  • Retain an audit trail of selected events. You can define categories of database actions to be audited.
  • Report on database activity. You can use preconfigured reports and a dashboard to get started quickly with activity and event reporting.
  • Analyze reports. You can find suspicious events, unusual activity, and trends.

There are two Auditing methods:

  • Blob auditing - logs are written to Azure Blob Storage. This is a newer auditing method, which provides higher performance, supports higher granularity object-level auditing, and is more cost effective.
  • Table auditing - logs are written to Azure Table Storage.

You can configure auditing for different types of event categories, as explained in the Set up auditing for your database section.

An auditing policy can be defined for a specific database or as a default server policy. A default server auditing policy applies to all existing and newly created databases on a server.

Set up auditing for your database

The following section describes the configuration of auditing using the Azure Portal.

Blob Auditing

  1. Launch the Azure Portal at https://portal.azure.com.
  2. Navigate to the settings blade of the SQL Database / SQL Server you want to audit. In the Settings blade, select Auditing & Threat detection.

    Navigation pane

  3. In the database auditing configuration blade, you can check the Inherit settings from server checkbox to designate that this database will be audited according to its server's settings. If this option is checked, you will see a View server auditing settings link that allows you to view or modify the server auditing settings from this context.

    Navigation pane

  4. If you prefer to enable Blob Auditing on the database-level (in addition or instead of server-level auditing), uncheck the Inherit Auditing settings from server option, turn ON Auditing, and choose the Blob Auditing Type.

    If server Blob auditing is enabled, the database configured audit will exist side by side with the server Blob audit.

    Navigation pane

  5. Select Storage Details to open the Audit Logs Storage Blade. Select the Azure storage account where logs will be saved, and the retention period, after which the old logs will be deleted, then click OK at the bottom. Tip: Use the same storage account for all audited databases to get the most out of the auditing reports templates.

    Navigation pane

  6. If you want to customize the audited events, you can do this via PowerShell or REST API - see the Automation (PowerShell / REST API) section for more details.
  7. Click Save.

Table Auditing

Note

Before setting up Table auditing, check if you are using a "Downlevel Client". Also, if you have strict firewall settings, please note that the IP endpoint of your database will change when enabling Table Auditing.

  1. Launch the Azure Portal at https://portal.azure.com.
  2. Navigate to the settings blade of the SQL Database / SQL Server you want to audit. In the Settings blade, select Auditing & Threat detection (see screenshot in Blob Auditing section).
  3. In the database auditing configuration blade, you can check the Inherit settings from server checkbox to designate that this database will be audited according to its server's settings. If this option is checked, you will see a View server auditing settings link that allows you to view or modify the server auditing settings from this context.

    Navigation pane

  4. If you prefer not to inherit Auditing settings from server, uncheck the Inherit Auditing settings from server option, turn ON Auditing, and choose Table Auditing Type.

    Navigation pane

  5. Select Storage Details to open the Audit Logs Storage Blade. Select the Azure storage account where logs will be saved, and the retention period, after which the old logs will be deleted. Tip: Use the same storage account for all audited databases to get the most out of the auditing reports templates (see screenshot in Blob Auditing section).
  6. Click on Audited Events to customize which events to audit. In the Logging by event blade, click Success and Failure to log all events, or choose individual event categories.

    Customizing audited events can also be done via PowerShell or REST API - see the Automation (PowerShell / REST API) section for more details.

    Navigation pane

  7. Once you've configured your auditing settings, you can turn on the new Threat Detection (preview) feature, and configure the emails to receive security alerts. Threat Detection allows you to receive proactive alerts on anomalous database activities that may indicate potential security threats. See Getting Started with Threat Detection for more details.
  8. Click Save.

Analyze audit logs and reports

Audit logs are aggregated in the Azure storage account you chose during setup.

You can explore audit logs using a tool such as Azure Storage Explorer.

See below specifics for analysis of both Table and Blob audit logs.

Blob Auditing

Blob Auditing logs are saved as a collection of Blob files within a container named "sqldbauditlogs".

For further details about the Blob audit logs storage folder hierarchy, Blob naming convention, and log format, see the Blob Audit Log Format Reference (doc file download).

There are several methods to view Blob Auditing logs:

  1. Through the Azure Portal - see method (1) in the Table Auditing section below (Excel download not supported).
  2. Download log files from your Azure Storage Blob container via the portal or by using a tool such as Azure Storage Explorer.

    Once you have downloaded the log file locally, you can double-click the file to open, view and analyze the logs in SSMS.

    Additional methods:

    • You can download multiple files simultaneously via Azure Storage Explorer - right-click on a specific subfolder (e.g. a subfolder that includes all log files for a specific date) and choose "Save as" to save in a local folder.

      After downloading several files (or an entire day, as described above), you can merge them locally as follows:

      Open SSMS -> File -> Open -> Merge Extended Events -> Choose all files to merge

    • Programmatically:

Table Auditing

Table Auditing logs are saved as a collection of Azure Storage Tables with a SQLDBAuditLogs prefix.

For further details about the Table audit log format, see the Table Audit Log Format Reference (doc file download).

There are several methods to view Table Auditing logs:

  1. Through the Azure Portal - at the top of the Auditing & Threat detection blade, click on More and then on Explore. An Audit records blade will open, where you'll be able to view the logs.

    • You can choose to view specific dates by clicking on Filter at the top area of the Audit records blade
    • You can download and view the audit logs in Excel format by clicking on Open in Excel at the top area of the Audit records blade

      Navigation Pane

  2. Alternatively, a preconfigured report template is available as a downloadable Excel spreadsheet to help you quickly analyze log data. To use the template on your audit logs, you need Excel 2013 or later and Power Query, which you can download here.

    You can also import your audit logs into the Excel template directly from your Azure storage account using Power Query. You can then explore your audit records and create dashboards and reports on top of the log data.

    Navigation Pane

Practices for usage in production

Auditing Geo-replicated databases

When using Geo-replicated databases, it is possible to set up Auditing on either the Primary database, the Secondary database, or both, depending on the Audit type.

Table Auditing - you can configure a separate policy, on either the database or the server level, for each of the two databases (Primary and Secondary) as described in Set up auditing for your database section.

Blob Auditing - follow these instructions:

  1. Primary database - turn on Blob Auditing either on the server or the database itself, as described in Set up auditing for your database section.
  2. Secondary database - Blob Auditing can only be turned on/off from the Primary database auditing settings.

    • Turn on Blob Auditing on the Primary database, as described in Set up auditing for your database section. (Note: Blob Auditing must be enabled on the database itself, not the server).
    • Once Blob Auditing is enabled on the Primary database, it will also become enabled on the Secondary database.
    • Important: By default, the storage settings for the Secondary database will be identical to those of the Primary database, causing cross-regional traffic. You can avoid this by enabling Blob Auditing on the Secondary Server and configuring a local storage in the Secondary Server storage settings (this will override the storage location for the Secondary database and result in each database saving the Audit logs to a local storage).


Storage Key Regeneration

In production, you are likely to refresh your storage keys periodically. When refreshing your keys, you need to re-save the auditing policy. The process is as follows:

  1. In the storage details blade switch the Storage Access Key from Primary to Secondary, and then click OK at the bottom. Then click SAVE at the top of the auditing configuration blade.

    Navigation Pane

  2. Go to the storage configuration blade and regenerate the Primary Access Key.

    Navigation Pane

  3. Go back to the auditing configuration blade, switch the Storage Access Key from Secondary to Primary, and then click OK at the bottom. Then click SAVE at the top of the auditing configuration blade.
  4. Go back to the storage configuration blade and regenerate the Secondary Access Key (in preparation for the next keys refresh cycle).

Automation (PowerShell / REST API)

You can also configure Auditing in Azure SQL Database using the following automation tools:

  1. PowerShell cmdlets

  2. REST API - Blob auditing

  3. REST API - Table auditing