Guides

Ensure that 'AuditActionGroups' in 'auditing' policy for a SQL server is set properly

To capture all critical activities done on SQL Servers and databases within sql servers, auditing should be configured to capture appropriate 'AuditActionGroups'. Property AuditActionGroup should contains at least SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP, FAILED_DATABASE_AUTHENTICATION_GROUP, BATCH_COMPLETED_GROUP to ensure comprehensive audit logging for SQL servers and SQL databases hosted on SQL Server.

Suggest Edits

Risk Level: Low
Cloud Entity: SQL Server on Virtual Machines
CloudGuard Rule ID: D9.AZU.MON.23
Covered by Spectral: No
Category: Compute

GSL LOGIC

SQLServer should have auditing.auditActionsAndGroups contain [ 'SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP' ] and auditing.auditActionsAndGroups contain [ 'FAILED_DATABASE_AUTHENTICATION_GROUP' ] and auditing.auditActionsAndGroups contain [ 'BATCH_COMPLETED_GROUP' ]

REMEDIATION

The default auditing settings include 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_GROUP

From Portal
On Azure Console, there is no Provision to check or change AuditActionGroup property.

From Command Line
Run

az sql server audit-policy update --resource-group RESOURCEGROUPNAME --name SERVERNAME --actions BATCH_COMPLETED_GROUP, SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP, FAILED_DATABASE_AUTHENTICATION_GROUP --retention-days NUMBEROFDAYS

From Powershell
Run

Set-AzSqlServerAudit -ResourceGroupName RESOURCEGROUP -ServerName SERVERNAME -StorageAccountResourceId STORAGEACCOUNTID -AuditActionGroup BATCH_COMPLETED_GROUP, SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP, FAILED_DATABASE_AUTHENTICATION_GROUP -RetentionInDays NUMBEROFDAYS

References

  1. https://learn.microsoft.com/en-us/powershell/module/az.sql/set-azsqlserveraudit?view=azps-9.0.0
  2. https://learn.microsoft.com/en-us/powershell/module/az.sql/get-azsqlserveraudit?view=azps-9.0.0
  3. https://learn.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions?view=sql-server-ver16
  4. https://learn.microsoft.com/en-us/cli/azure/sql/server/audit-policy?view=azure-cli-latest#az-sql-server-audit-policy-update
  5. https://workbench.cisecurity.org/sections/40171/recommendations/116789

SQL Server on Virtual Machines

SQL Server on Azure virtual machines enables you to use full versions of SQL Server in the Cloud without having to manage any on-premises hardware. SQL Server VMs also simplify licensing costs when you pay as you go.

Azure virtual machines run in many different geographic regions around the world. They also offer a variety of machine sizes. The virtual machine image gallery allows you to create a SQL Server VM with the right version, edition, and operating system. This makes virtual machines a good option for a many different SQL Server workloads.

Compliance Frameworks

  • Azure CIS Foundations v. 1.1.0
  • Azure CloudGuard Best Practices
  • Azure ITSG-33
  • CloudGuard Azure All Rules Ruleset

Updated over 1 year ago