Get started with SQL database auditing

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.

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

Audit logs are written to Azure Blob Storage on your Azure subscription.

Server-level vs. Database-level auditing policy

An auditing policy can be defined for a specific database or as a default server policy.

  1. If Server Blob auditing is enabled, it always applies to the database (i.e. the database will be audited), regardless of the database auditing settings.

  2. Enabling Blob auditing on the database, in addition to enabling it on the server, will not override or change any of the settings of the server Blob auditing - both audits will exist side by side. In other words, the database will be audited twice in parallel (once by the Server policy and once by the Database policy).

    Note

    You should avoid enabling both server Blob auditing and database Blob auditing together, unless:

    • You want to use a different storage account or retention period for a specific database.
    • You want to audit different event types or categories for a specific database than are being audited for the rest of the databases on this server (e.g. if table inserts need to be audited only for a specific database).

      Otherwise, it is recommended to only enable server-level Blob Auditing and leave the database-level auditing disabled for all databases.

Set up auditing for your database

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

  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. If you prefer to set up a server auditing policy (which will apply to all existing and newly created databases on this server), you can click on the View server settings link in the database auditing blade, which 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), turn Auditing ON, 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. 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.

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 - open the relevant database. At the top of the database's Auditing & Threat detection blade, click on View audit logs.

    Navigation Pane

    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 toggle between audit records that were created by server policy or database policy audit

      Navigation Pane

  2. Using the Merge Audit Files feature in SSMS (starting with SSMS 17):

    • In the SSMS top menu, click on File --> Open --> Merge Audit Files...
    • A dialog window will open, click on Add...
    • In the following page, choose whether to merge audit files from local disk or import from Azure Storage (you will be required to provide your Azure Storage details and account key).
    • Once all files to merge have been added, click OK to complete the merge operation.
    • The merged file will open in SSMS, where you'll be able to view and analyze it, as well as export to XEL/CSV files or to a table.
  3. We have created a sync application that runs in Azure and utilizes OMS Log Analytics public APIs to push SQL audit logs into OMS Log Analytics for consumption via the OMS Log Analytics dashboard (more info here).

  4. 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.

    You can also 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.

    Additional methods:

    • After downloading several files (or an entire day, as described above), you can merge them locally as described in the SSMS Merge Audit Files instructions above.

    • Programmatically:

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.

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. Blob Auditing must be enabled on the primary 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

    For an script example, see Configure auditing and threat detectoin using PowerShell.

  2. REST API - Blob Auditing