Logo of Trend Micro
Use the Conformity Knowledge Base AI to help improve your Cloud Posture

Configure "AuditActionGroup" for SQL Server Auditing

Trend Cloud One™ – Conformity is a continuous assurance tool that provides peace of mind for your cloud infrastructure, delivering over 1000 automated best practice checks.

Risk Level: Medium (should be achieved)
Rule ID: Sql-002

Ensure that the "AuditActionGroup" property is properly configured within the auditing policy implemented at the Microsoft Azure SQL server level, in order to capture all critical activity triggered on your SQL database servers and on all the SQL databases hosted on those servers. Prior to running this rule by the Cloud Conformity engine, SQL database auditing needs to be enabled for all Microsoft Azure SQL servers.

This rule resolution is part of the Conformity Security & Compliance tool for Azure.

Security

To capture critical actions performed on your Azure SQL databases, auditing should be configured to enable the "AuditActionGroup" property with the appropriate configuration. To ensure comprehensive audit logging for your SQL servers and SQL databases hosted on these servers, the "AuditActionGroup" should contain the following action groups: SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP – which indicates a principal logged in successfully to a contained database, FAILED_DATABASE_AUTHENTICATION_GROUP – indicates that a principal tried to log on to a contained database and failed (events in this class are triggered by new connections or by connections that are reused from a connection pool), and BATCH_COMPLETED_GROUP – which indicates that the Transact-SQL batch has been completed.

Audit

To determine if "AuditActionGroup" is enabled and properly configured at the Azure SQL database server level, perform the following actions:

Note: Getting "AuditActionGroup" property configuration status using Microsoft Azure Management Console (Azure Portal) is not currently supported.

Using Azure CLI

01 Run Get-AzSqlServer PowerShell command (cmdlet) using custom query filters to list the names of all SQL database servers (and the name of their associated resource groups) available in the current Azure subscription: 

Get-AzSqlServer | Select-Object ServerName,ResourceGroupName

02 The command output should return the requested SQL database server information: 

ServerName              ResourceGroupName
----------              -----------------
cc-project5-sql-server  cloud-shell-storage-westeurope
cc-development-server   cloud-shell-storage-westeurope
cc-prod-sql-server      cloud-shell-storage-westeurope

03 Run Get-AzSqlServerAuditing PowerShell command using the name of the SQL server that you want to examine as identifier parameter and custom query filters to describe the action groups enabled for the "AuditActionGroup" property on the selected database server: 

Get-AzSqlServerAuditing -ServerName "cc-project5-sql-server" -ResourceGroupName "cloud-shell-storage-westeurope" | Select-Object AuditActionGroup

04 The command output should return the name of each action group currently enabled: 

AuditActionGroup
----------------
FAILED_DATABASE_AUTHENTICATION_GROUP

If Get-AzSqlServerAuditing cmdlet output does not return all action groups available for "AuditActionGroup", i.e. SUCCESSFUL_DATABASE_AUTHENTICATION_GR OUP, FAILED_DATABASE_AUTHENTICATION_GROUP and BATCH_COMPLETED_GROUP, the "AuditActionGroup" property configuration for the selected Microsoft Azure SQL database server is not compliant.

05 Repeat step no. 3 and 4 for each SQL database server provisioned in the selected Azure subscription.

06 Repeat steps no. 1 – 5 for each subscription available in your Microsoft Azure cloud account.

Remediation / Resolution

To enable the "AuditActionGroup" property with the required configuration for your Microsoft Azure SQL database servers, perform the following actions:

Note: Configuring action groups for the "AuditActionGroup" property using Microsoft Azure Management Console (Azure Portal) is not currently supported.

Using Azure CLI

01 Run Set-AzSqlServerAuditing PowerShell cmdlet using the name of the SQL server that you want to reconfigure as identifier parameter (see Audit section part I to identify the right SQL resource) and the action groups required to be enabled, to properly configure the "AuditActionGroup" property for the selected Microsoft Azure SQL database server. For compliance, "AuditActionGroup" must have all three action groups enabled, i.e. SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP, FAILED_DATABASE_AUTHENTICATION_GROUP and BATCH_COMPLETED_GROUP. The audit log destination is determined by specifying one of the following parameters: BlobStorage, LogAnalytics or EventHub (if none is specified, the default is BlobStorage). Use the State parameter to enable/disable the auditing policy. You can also define retention for the audit logs by setting the right value for the RetentionInDays parameter. For example, the following PowerShell command request configures the "AuditActionGroup" property to include SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP, FAILED_DATABASE_AUTHENTICATION_GROUP and BATCH_COMPLETED_GROUP action groups, required by SQL database auditing for an SQL server named "cc-project5-sql-server", available in the "cloud-shell-storage-westeurope" resource group (the command does not produce an output): 

Set-AzSqlServerAuditing -State Enabled -ServerName "cc-project5-sql-server" -ResourceGroupName "cloud-shell-storage-westeurope" -StorageAccountName "abcd1234abcd1234abcd1234" -AuditActionGroup "SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP", "FAILED_DATABASE_AUTHENTICATION_GROUP", "BATCH_COMPLETED_GROUP" -RetentionInDays 90

02 Repeat step no. 1 for each SQL database server available within the selected subscription.

03 Repeat step no. 1 and 2 for each subscription created in your Microsoft Azure cloud account.

References

Publication date Jul 24, 2019

Related Sql rules