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 further details 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 will apply 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. navigate to the configuration blade of the SQL Data Warehouse database / SQL Server you want to audit. Click the Settings button on top and then, in the Setting blade, and select Auditing.

  3. In the auditing configuration blade, first unselect the Inherit Auditing Settings from Server checkbox. This allows you to specify the settings for a particular database.

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

  5. 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 preconfigured reports templates.

  6. Click the OK button to save the storage details configuration.
  7. Under LOGGING BY EVENT, click SUCCESS and FAILURE to log all events, or choose individual event categories.
  8. 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.
  9. 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.

For more detailed instructions on working with the report template, read the How To (doc download).

Practices for usage in production

The description in this section refers to screen captures above. Either Azure Portal or Classic Azure Classic Portal may be used.

Storage Key Regeneration

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

  1. In the auditing configuration blade (described above in the set up 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

There are several PowerShell cmdlets you can use to configure auditing in Azure SQL Database. To access the auditing cmdlets you must be running PowerShell in Azure Resource Manager mode.

Note

The Azure Resource Manager module is currently in preview. It might not provide the same management capabilities as the Azure module.

When you are in Azure Resource Manager mode, run Get-Command *AzureSql* to list the available cmdlets.