Auditing in Azure SQL Data Warehouse

SQL Data Warehouse auditing allows you to record events in your database 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. SQL Data Warehouse auditing also integrates with Microsoft Power BI for drill-down reporting and analysis.

Auditing tools enable and facilitate adherence to compliance standards but don't guarantee compliance. For more information about Azure programs that support standards compliance, see the Azure Trust Center.

Azure SQL Data Warehouse Database Auditing basics

SQL Data Warehouse 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 the following event categories:

Plain SQL and Parameterized SQL for which the collected audit logs are classified as

  • Access to data
  • Schema changes (DDL)
  • Data changes (DML)
  • Accounts, roles, and permissions (DCL)
  • Stored Procedure, Login and, Transaction Management.

For each Event Category, Auditing of Success and Failure operations are configured separately.

For more information about the activities and events audited, see the Audit Log Format Reference (doc file download).

Audit logs are stored in your Azure storage account. You can define an audit log retention period.

An auditing policy can be defined for a specific database or as a default server policy. A default server auditing policy applies to all databases on a server, which do not have a specific overriding database auditing policy defined.

Before setting up audit auditing check if you are using a "Downlevel Client."

Set up auditing for your database

  1. Launch the Azure portal.
  2. Go to the Settings blade of the SQL Data Warehouse you want to audit. In the Settings blade, select Auditing & Threat detection.

  3. Next, enable auditing by clicking the ON button.

  4. In the auditing configuration blade, select STORAGE DETAILS to open the Audit Logs Storage blade. Select the Azure storage account where logs will be saved and, the retention period.

    Tip

    Use the same storage account for all audited databases to get the most out of the pre-configured reports templates.

  5. Click the OK button to save the storage details configuration.
  6. Under LOGGING BY EVENT, click SUCCESS and FAILURE to log all events, or choose individual event categories.
  7. If you are configuring Auditing for a database, you may need to alter the connection string of your client to ensure data auditing is correctly captured. Check the Modify Server FDQN in the connection string topic for downlevel client connections.
  8. Click OK.

Analyze audit logs and reports

Audit logs are aggregated in a collection of Store Tables with a SQLDBAuditLogs prefix in the Azure storage account you chose during setup. You can view log files using a tool such as Azure Storage Explorer.

A preconfigured dashboard 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.

The template has fictional sample data in it, and you can set up Power Query to import your audit log directly from your Azure storage account.

Storage key regeneration

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

  1. In the auditing configuration blade (described above in the setup auditing section) switch the Storage Access Key from Primary to Secondary and SAVE.

  2. Go to the storage configuration blade and regenerate the Primary Access Key.
  3. Go back to the auditing configuration blade, switch the Storage Access Key from Secondary to Primary and press SAVE.
  4. Go back to the storage UI and regenerate the Secondary Access Key (as preparation for the next keys refresh cycle.

Automation (PowerShell/REST API)

You can also configure auditing in Azure SQL Data Warehouse by using the following automation tools: