Set up Auditing for Azure SQL Database and Azure Synapse Analytics
Applies to: 
Azure SQL Database
Azure Synapse Analytics
In this article, we go over setting up Auditing for your logical server or database in Azure SQL Database and Azure Synapse Analytics.
Configure Auditing for your server
The default auditing policy includes the following set of action groups, which audits 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_GROUP
To configure auditing for different types of actions and action groups using PowerShell, see Manage Azure SQL Database Auditing using APIs.
Azure SQL Database and Azure Synapse Analytics Audit can store 4,000 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 are truncated and not audited.
The following section describes the Auditing configuration using the Azure portal.
Note
You can't enable auditing on a paused dedicated SQL pool. To enable auditing, resume the dedicated SQL pool.
When Auditing is configured to a Log Analytics workspace or to an Event Hubs destination in the Azure portal or PowerShell cmdlet, a Diagnostic Setting is created with SQLSecurityAuditEvents category enabled.
Go to the Azure portal.
Navigate to Auditing under the Security heading in your SQL database or SQL 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 exists side-by-side with the server audit.
You have multiple options for configuring where audit logs are stored. You can write logs to an Azure storage account, to a Log Analytics workspace for consumption by Azure Monitor logs, or to event hub for consumption using event hub. You can configure any combination of these options, and audit logs are written to each.
Audit to storage destination
To configure writing audit logs to a storage account, select Storage when you get to the Auditing section. Select the Azure storage account where you want to save your logs. You can use the following two storage authentication types: Managed Identity and Storage Access Keys. For managed identity, system-assigned and user-assigned managed identity is supported. By default, the primary user identity assigned to the server is selected. If there's no user identity, then a system-assigned managed identity is created and used for authentication purposes. After you have chosen an authentication type, select a retention period by opening Advanced properties and selecting Save. Logs older than the retention period are deleted.
Note
If you are deploying from the Azure portal, make sure that the storage account is in the same region as your database and server. If you are deploying through other methods, the storage account can be in any region.
- The default value for retention period is 0 (unlimited retention). You can change this value by moving the Retention (Days) slider in Advanced properties when configuring the storage account for auditing.
- If you change retention period from 0 (unlimited retention) to any other value, the retention will only apply to logs written after the retention value was changed. Logs written during the period when retention days were set to unlimited retention are preserved, even after retention is enabled.
Audit to Log Analytics destination
To configure writing audit logs to a Log Analytics workspace, select Log Analytics and open Log Analytics details. Select the Log Analytics workspace where logs you want logs stored, and then select OK. If you haven't created a Log Analytics workspace, see Create a Log Analytics workspace in the Azure portal.
Audit to Event Hubs destination
To configure writing audit logs to an event hub, select Event Hub. Select the event hub where you want logs stored, and then select Save. Be sure that the event hub is in the same region as your database and server.
Note
If you are using multiple targets like storage account, log analytics, or event hub, make sure you have permissions for all the targets else saving audit configuration would fail as it will try to save the settings for all targets.