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

Note:

  • Azure SQL Database and Azure Synapse Analytics Audit can store 4000 characters of data for character fields in an audit record.
  • Any data beyond the first 4000 characters are truncated and not audited.
  • Enabling auditing on a paused dedicated SQL pool is not possible. 
  • 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.

How to Navigate to enable Auditing in Azure SQL Database and Azure Synapse Analytics

  1. Go to the Azure portal.
  2. Navigate to Auditing under the Security heading in your SQL database or SQL server pane.
  3.  View server settings link on the database auditing page to iew or modify the server auditing settings

4. You can either enable auditing at database level –> go to server –> Database –> Auditing ,for that switch Auditing to ON. If server auditing is enabled, the database-configured audit exists side-by-side with the server audit.

5. To enable auditing at Server level go to Server –> Auditing

6. 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.

Audit to storage destination

To configure writing audit logs to a storage account, select Storage when you get to the Auditing section.

You can use the following two storage authentication types: Managed Identity and Storage Access Keys.

Note:

  • 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 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.

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.

Create a Log Analytics workspace

Note: You should have Log Analytics contributor built in role

  1. Go to Azure portal –> Search Select Log Analytics workspaces.
  2. Select Add.
  3. Select a Subscription from the dropdown.
  4. Use an existing Resource Group or create a new one.
  5. Provide a name for the new Log Analytics workspace, such as DefaultLAWorkspace. This name must be unique per resource group.
  6. Select an available Region
  7. Select Review + Create to review the settings.

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.