Azure / Azure/azure-powershell
Unable to set SENSITIVE_BATCH_COMPLETED_GROUP AuditActionGroup with Set-AzSqlServerAudit on Azure SQL
- Dominant language
- C#
- Stars
- 4.8k
- Forks
- 4.3k
- Avg merge
- 3d 14h
- Merged PRs (30d)
- 54
Description
### Description
We have spent much time to classify the data in our Azure SQL Databases, and now we want to set the auditing so it only logs SELECT, UPDATE, DELETE for the Sensitive data. In sys.dm_audit_actions view we can see that the actions groups we want to use are present but when using the PowerShell command (or azure cli) it only support for a few of those groups.
https://learn.microsoft.com/en-us/powershell/module/az.sql/set-azsqlserveraudit?view=azps-11.3.0#-auditactiongroup
Can I in some way set the audit action to SENSITIVE_BATCH_COMPLETED_GROUP or is this only possible on onprem SQL ?
### Issue script & Debug output
```PowerShell
Set-AzSqlServerAudit -ResourceGroupName $rg -ServerName $server -AuditActionGroup "SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP", "FAILED_DATABASE_AUTHENTICATION_GROUP", "SENSITIVE_BATCH_COMPLETED_GROUP"
Set-AzSqlServerAudit: Cannot bind parameter 'AuditActionGroup'. Cannot convert value "SENSITIVE_BATCH_COMPLETED_GROUP" to type "Microsoft.Azure.Commands.Sql.Auditing.Model.AuditActionGroups". Error: "Unable to match the identifier name SENSITIVE_BATCH_COMPLETED_GROUP to a valid enumerator name. Specify one of the following enumerator names and try again:
BATCH_STARTED_GROUP, BATCH_COMPLETED_GROUP, APPLICATION_ROLE_CHANGE_PASSWORD_GROUP, BACKUP_RESTORE_GROUP, DATABASE_LOGOUT_GROUP, DATABASE_OBJECT_CHANGE_GROUP, DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP, DATABASE_OBJECT_PERMISSION_CHANGE_GROUP, DATABASE_OPERATION_GROUP, DATABASE_PERMISSION_CHANGE_GROUP, DATABASE_PRINCIPAL_CHANGE_GROUP, DATABASE_PRINCIPAL_IMPERSONATION_GROUP, DATABASE_ROLE_MEMBER_CHANGE_GROUP, FAILED_DATABASE_AUTHENTICATION_GROUP, SCHEMA_OBJECT_ACCESS_GROUP, SCHEMA_OBJECT_CHANGE_GROUP, SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP, SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP, SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP, USER_CHANGE_PASSWORD_GROUP, LEDGER_OPERATION_GROUP, DBCC_GROUP, DATABASE_OWNERSHIP_CHANGE_GROUP, DATABASE_CHANGE_GROUP"
```
### Environment data
```PowerShell
Name Value
---- -----
PSVersion 7.4.1
PSEdition Core
GitCommitId 7.4.1
OS Microsoft Windows 10.0.22621
Platform Win32NT
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0…}
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1
WSManStackVersion 3.0
```
### Module versions
```PowerShell
ModuleType Version PreRelease Name ExportedCommands
---------- ------- ---------- ---- ----------------
Script 2.15.0 Az.Accounts {Add-AzEnvironment, Clear-AzConfig, Clear-AzContex…
Script 4.13.0 Az.Sql {Add-AzSqlDatabaseToFailoverGroup, Add-AzSqlElasti…
```
### Error output
_No response_
Contributor guide
Research direction
Start with Set-AzSqlServerAudit and the AuditActionGroups enum named in the error, then compare its supported values with Azure SQL's available audit action groups and the linked documentation. Done means either adding supported handling for SENSITIVE_BATCH_COMPLETED_GROUP with coverage, or documenting that Azure SQL does not allow it.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- azure, csharp, powershell, sql
- Domain
- cloud, databases, security
- Issue type
- Feature
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 45/100