Get started with SQL database auditing
Azure SQL Database Auditing tracks database events and writes audited events to an audit log in your Azure Storage account. Auditing is generally available for Basic, Standard, and Premium service tiers.
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 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.
The following section describes the configuration of auditing using the Azure Portal. You may also set up auditing for your database using the Azure Classic Portal.
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 now receive proactive alerts on anomalous database activities that may indicate potential security threats using the new Threat Detection feature, now in preview. Threat Detection can be turned on and configured from within the auditing configuration blade. See Getting Started with Threat Detection for more details.
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, after which the old logs will be deleted.
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 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 Auditing.
Navigate to the settings blade of the SQL Database / SQL Server you want to audit. In the Settings blade select Auditing & Threat detection.
In the auditing configuration blade, turn ON Auditing.
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 auditing reports templates.
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.
You can check the Inherit Auditing settings from server checkbox to designate that this database will be audited according to its server's settings. Once you check this option, you will see a link that allows you to view or modify the server auditing settings from this context.
Once you've configured your auditing settings, you can turn ON Threat Detection and configure the emails to receive security alerts. See the Threat Detection Getting Started page for more details.
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 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 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.
Launch the Azure Classic Portal at https://manage.windowsazure.com/.
Click the SQL Database / SQL Server you want to audit, and then click the AUDITING & SECURITY tab.
Set Auditing to ENABLED.
Edit the LOGGING BY EVENT as needed, to customize which events to audit.
Select a STORAGE ACCOUNT and configure RETENTION.
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:.
In the auditing configuration blade switch the Storage Access Key from Primary to Secondary and click SAVE.
Go to the storage configuration blade and regenerate the Primary Access Key.
Go back to the auditing configuration blade, switch the Storage Access Key from Secondary to Primary and click SAVE.
Go back to the storage UI and regenerate the Secondary Access Key (in preparation for the next keys refresh cycle).
There are several PowerShell cmdlets you can use to configure auditing in Azure SQL Database: