| title | description | services | ms.service | ms.subservice | ms.custom | ms.devlang | ms.topic | author | ms.author | ms.reviewer | manager | ms.date |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
Get started with Azure SQL database auditing | Microsoft Docs |
Use Azure SQL database auditing to track database events into an audit log. |
sql-database |
sql-database |
security |
conceptual |
ronitr |
ronitr |
vanto |
craigg |
10/25/2018 |
Auditing for Azure SQL Database and SQL Data Warehouse tracks database events and writes them to an audit log in your Azure storage account, OMS workspace or Event Hubs. Auditing also:
-
Helps you maintain regulatory compliance, understand database activity, and gain insight into discrepancies and anomalies that could indicate business concerns or suspected security violations.
-
Enables and facilitates adherence to compliance standards, although it doesn't guarantee compliance. For more information about Azure programs that support standards compliance, see the Azure Trust Center.
Note
This topic applies to Azure SQL server, and to both SQL Database and SQL Data Warehouse databases that are created on the Azure SQL server. For simplicity, SQL Database is used when referring to both SQL Database and SQL Data Warehouse.
You can use SQL database auditing 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 pre-configured 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.
Important
Audit logs are written to Append Blobs in an Azure Blob storage on your Azure subscription.
- Premium Storage is currently not supported by Append Blobs.
- Storage in VNet is currently not supported.
An auditing policy can be defined for a specific database or as a default server policy:
-
A server policy applies to all existing and newly created databases on the server.
-
If server blob auditing is enabled, it always applies to the database. The database will be audited, regardless of the database auditing settings.
-
Enabling blob auditing on the database or data warehouse, in addition to enabling it on the server, does 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 is 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 event types or categories for a specific database that differ from the rest of the databases on the server. For example, you might have table inserts that need to be audited only for a specific database.
Otherwise, we recommended that you enable only server-level blob auditing and leave the database-level auditing disabled for all databases.
The following section describes the configuration of auditing using the Azure portal.
-
Go to the Azure portal.
-
Navigate to Auditing under the Security heading in your SQL database/server pane.
-
If you prefer to set up a server auditing policy, you can select the View server settings link on the database auditing page. You can then view or modify the server auditing settings. Server auditing policies apply to all existing and newly created databases on this server.
-
If you prefer to enable auditing on the database level, switch Auditing to ON.
If server auditing is enabled, the database-configured audit will exist side-by-side with the server audit.
-
New - You now have multiple options for configuring where audit logs will be written. You can write logs to an Azure storage account, to a Log Analytics workspace for consumption by Log Analytics, or to event hub for consumption using event hub. You can configure any combination of these options, and audit logs will be written to each.
-
To configure writing audit logs to a storage account, select Storage and open Storage details. Select the Azure storage account where logs will be saved, and then select the retention period. The old logs will be deleted. Then click OK.
-
To configure writing audit logs to a Log Analytics workspace, select Log Analytics (Preview) and open Log Analytics details. Select or create the Log Analytics workspace where logs will be written and then click OK.
-
To configure writing audit logs to an event hub, select Event Hub (Preview) and open Event Hub details. Select the event hub where logs will be written and then click OK. Be sure that the event hub is in the same region as your database and server.
-
Click Save.
-
If you want to customize the audited events, you can do this via PowerShell cmdlets or the REST API.
-
After you've configured your auditing settings, you can turn on the new threat detection feature and configure emails to receive security alerts. When you use threat detection, you receive proactive alerts on anomalous database activities that can indicate potential security threats. For more information, see Getting started with threat detection.
Important
Enabling auditing on an Azure SQL Data Warehouse, or on a server that has an Azure SQL Data Warehouse on it, will result in the Data Warehouse being resumed, even in the case where it was previously paused. Please make sure to pause the Data Warehouse again after enabling auditing.'
If you chose to write audit logs to Log Analytics:
-
Use the Azure portal. Open the relevant database. At the top of the database's Auditing page, click View audit logs.
-
Then, clicking on Open in OMS at the top of the Audit records page will open the Logs view in Log Analytics, where you can customize the time range and the search query.
-
Alternatively, you can also access the audit logs from Log Analytics blade. Open your Log Analytics workspace and under General section, click Logs. You can start with a simple query, such as: search "SQLSecurityAuditEvents" to view the audit logs. From here, you can also use Log Analytics to run advanced searches on your audit log data. Log Analytics gives you real-time operational insights using integrated search and custom dashboards to readily analyze millions of records across all your workloads and servers. For additional useful information about Log Analytics search language and commands, see Log Analytics search reference.
If you chose to write audit logs to Event Hub:
- To consume audit logs data from Event Hub, you will need to set up a stream to consume events and write them to a target. For more information, see Azure Event Hubs Documentation.
If you chose to write audit logs to an Azure storage account, there are several methods you can use to view the logs:
-
Audit logs are aggregated in the account you chose during setup. You can explore audit logs by using a tool such as Azure Storage Explorer. In Azure storage, auditing logs are saved as a collection of blob files within a container named sqldbauditlogs. For further details about the hierarchy of the storage folder, naming conventions, and log format, see the Blob Audit Log Format Reference.
-
Use the Azure portal. Open the relevant database. At the top of the database's Auditing page, click View audit logs.
Audit records opens, from which you'll be able to view the logs.
-
You can view specific dates by clicking Filter at the top of the Audit records page.
-
You can switch between audit records that were created by the server audit policy and the database audit policy by toggling Audit Source.
-
You can view only SQL injection related audit records by checking Show only audit records for SQL injections checkbox.
-
-
Use the system function sys.fn_get_audit_file (T-SQL) to return the audit log data in tabular format. For more information on using this function, see sys.fn_get_audit_file.
-
Use Merge Audit Files in SQL Server Management Studio (starting with SSMS 17):
-
From the SSMS menu, select File > Open > Merge Audit Files.
-
The Add Audit Files dialog box opens. Select one of the Add options to choose whether to merge audit files from a local disk or import them from Azure Storage. You are required to provide your Azure Storage details and account key.
-
After all files to merge have been added, click OK to complete the merge operation.
-
The merged file opens in SSMS, where you can view and analyze it, as well as export it to an XEL or CSV file, or to a table.
-
-
Use Power BI. You can view and analyze audit log data in Power BI. For more information and to access a downloadable template, see Analyzie audit log data in Power BI.
-
Download log files from your Azure Storage blob container via the portal or by using a tool such as Azure Storage Explorer.
- After you have downloaded a log file locally, double-click the file to open, view, and analyze the logs in SSMS.
- You can also download multiple files simultaneously via Azure Storage Explorer. To do so, right-click a specific subfolder and select Save as to save in a local folder.
-
Additional methods:
-
After downloading several files or a subfolder that contains log files, you can merge them locally as described in the SSMS Merge Audit Files instructions described previously.
-
View blob auditing logs programmatically:
- Use the Extended Events Reader C# library.
- Query Extended Events Files by using PowerShell.
-
With geo-replicated databases, when you enable auditing on the primary database the secondary database will have an identical auditing policy. It is also possible to set up auditing on the secondary database by enabling auditing on the secondary server, independently from the primary database.
- Server-level (recommended): Turn on auditing on both the primary server as well as the secondary server - the primary and secondary databases will each be audited independently based on their respective server-level policy.
- Database-level: Database-level auditing for secondary databases can only be configured from Primary database auditing settings.
-
Auditing must be enabled on the primary database itself, not the server.
-
After auditing is enabled on the primary database, it will also become enabled on the secondary database.
[!IMPORTANT] With database-level auditing, the storage settings for the secondary database will be identical to those of the primary database, causing cross-regional traffic. We recommend that you enable only server-level auditing, and leave the database-level auditing disabled for all databases.
-
In production, you are likely to refresh your storage keys periodically. When writing audit logs to Azure storage, you need to resave your auditing policy when refreshing your keys. The process is as follows:
-
Open Storage Details. In the Storage Access Key box, select Secondary, and click OK. Then click Save at the top of the auditing configuration page.
-
Go to the storage configuration page and regenerate the primary access key.
-
Go back to the auditing configuration page, switch the storage access key from secondary to primary, and then click OK. Then click Save at the top of the auditing configuration page.
-
Go back to the storage configuration page and regenerate the secondary access key (in preparation for the next key's refresh cycle).
-
For details about the log format, hierarchy of the storage folder and naming conventions, see the Blob Audit Log Format Reference.
[!IMPORTANT] Azure SQL Database Audit stores 4000 characters of data for character fields in an audit record. When the statement or the data_sensitivity_information values returned from an auditable action contain more than 4000 characters, any data beyond the first 4000 characters will be truncated and not audited.
-
Audit logs are written to Append Blobs in an Azure Blob storage on your Azure subscription:
- Premium Storage is currently not supported by Append Blobs.
- Storage in VNet is currently not supported.
-
The default auditing policy includes all actions and the following set of action groups, which will audit all the queries and stored procedures executed against the database, as well as successful and failed logins:
BATCH_COMPLETED_GROUP
SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP
FAILED_DATABASE_AUTHENTICATION_GROUPYou can configure auditing for different types of actions and action groups using PowerShell, as described in the Manage SQL database auditing using Azure PowerShell section.
-
When using AAD Authentication, failed logins records will not appear in the SQL audit log. To view failed login audit records, you need to visit the Azure Active Directory portal, which logs details of these events.
PowerShell cmdlets:
- Create or Update Database Blob Auditing Policy (Set-AzureRMSqlDatabaseAuditing)
- Create or Update Server Blob Auditing Policy (Set-AzureRMSqlServerAuditing)
- Get Database Auditing Policy (Get-AzureRMSqlDatabaseAuditing)
- Get Server Blob Auditing Policy (Get-AzureRMSqlServerAuditing)
For a script example, see Configure auditing and threat detection using PowerShell.
REST API - Blob auditing:
- Create or Update Database Blob Auditing Policy
- Create or Update Server Blob Auditing Policy
- Get Database Blob Auditing Policy
- Get Server Blob Auditing Policy
Extended policy with WHERE clause support for additional filtering: