Understanding action_id discrepancies in Azure SQL Database Audit Logs (BCM vs AL / CR / DR)

Overview

While working with Azure SQL Database auditing in enterprise environments, you may encounter an inconsistency in how the action_id field is captured across different PaaS SQL servers.

In one such scenario, a customer observed:

This inconsistency impacted downstream compliance pipelines, as the audit data was expected to be captured and interpreted uniformly across all servers.

This article explains:

• How Azure SQL Auditing works by default
• What causes BCM to appear instead of AL/CR/DR
• How to standardize audit logs across PaaS servers

How Azure SQL Database Auditing Works?

Azure SQL Database auditing uses a managed and fixed audit policy at the service level.

When auditing is enabled at the server level, the default auditing policy includes the following action groups:

• BATCH_COMPLETED_GROUP
• SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP
• FAILED_DATABASE_AUTHENTICATION_GROUP

These groups audit:

• All query execution activity
• Successful authentication attempts
• Failed authentication attempts

As a result, SQL batches — including DDL statements like CREATE, ALTER, or DROP on database objects — are captured under the BATCH_COMPLETED_GROUP and appear with action_id = BCM

Why AL, CR, and DR are not captured by default?

Audit action IDs such as AL, CR and DR are considered Security / DDL-level audit events.

These events are not included in the default Azure SQL auditing policy.

Instead, they are generated only when the corresponding Security-related AuditActionGroups are explicitly enabled.

For example:

DDL operations such as CREATE / ALTER / DROP on database objects are captured under action groups like DATABASE_OBJECT_CHANGE_GROUP. 

Observed Behavior in a Newly Created Test Server

Running the following PowerShell command on a newly provisioned logical server showed only the default audit action groups enabled.

 

(Get-AzSqlServerAudit -ResourceGroupName "RGName" -ServerName "ServerName").AuditActionGroup

 

 

 

 

Therefore, DDL statements were audited but recorded as action_id = BCM

 

 

 

 

 

 

 

 

Enabling AL / CR / DR Action IDs

To capture DDL operations under their respective audit action IDs, configure the required security audit action groups at the SQL Server level.

For example: In this customer scenario, we executed the following command:

Set-AzSqlServerAudit 
 -ResourceGroupName "RGName" 
 -ServerName "ServerName" 
 -AuditActionGroup 
 "DATABASE_PRINCIPAL_CHANGE_GROUP",
 "DATABASE_ROLE_MEMBER_CHANGE_GROUP",
 "DATABASE_OBJECT_CHANGE_GROUP"

After applying this configuration:

DDL operations were captured in the audit logs as action_id = CR, AL and DR instead of BCM.

 

 

 

 

 

 

Ensuring Consistent Compliance Across PaaS Servers

To standardize audit logging behavior across environments:

Step 1: Compare AuditActionGroups

Run the following command on all servers:

(Get-AzSqlServerAudit -ResourceGroupName "<RG>" -ServerName "<ServerName>").AuditActionGroup

Step 2: Align AuditActionGroups

Configure all server with same AuditActionGroup values. In this case, value used was below:

Set-AzSqlServerAudit 
 -ResourceGroupName "<RG>" 
 -ServerName "<ServerName>" 
 -AuditActionGroup `
 "DATABASE_PRINCIPAL_CHANGE_GROUP",
 "DATABASE_ROLE_MEMBER_CHANGE_GROUP",
 "DATABASE_OBJECT_CHANGE_GROUP"

Step 3: Validate

Once aligned, similar SQL statements across all PaaS servers should now generate consistent action_id values in audit logs.

Accepted values for AuditActionGroups. Ensure appropriate groups are enabled based on your organization’s compliance needs.

 

 

Links:

Get-AzSqlServerAudit (Az.Sql) | Microsoft Learn

Set-AzSqlServerAudit (Az.Sql) | Microsoft Learn