使用Set-AzSqlServerAudit PowerShell命令时PredicateExpression报错问题

问题场景 #

执行以下PowerShell命令设置Azure SQL Server审计规则时：

Set-AzSqlServerAudit -ResourceGroupName "My_RG" -ServerName "My_Server" -BlobStorageTargetState Enabled -StorageAccountResourceId "XXXXXX" -RetentionInDays 365 -PredicateExpression "[database_principal_name]= 'dbo' OR ([action_name] = 'DATABASE AUTHENTICATION FAILED' OR [action_name] = 'CREATE SERVER AUDIT SPECIFICATION' OR [action_name] = 'ALTER SERVER AUDIT SPECIFICATION' OR [action_name] = 'DROP SERVER AUDIT SPECIFICATION')"

仅保留"[database_principal_name]= 'dbo'"作为PredicateExpression参数值时命令可正常执行，但使用完整表达式会抛出如下错误：

Set-AzSqlServerAudit : One or more errors occurred. At line:1 char:1
 + Set-AzSqlServerAudit -ResourceGroupName "My_RG" -ServerName "My_Serv...
 + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     + CategoryInfo          : CloseError: (:) [Set-AzSqlServerAudit], AggregateException
     + FullyQualifiedErrorId : Microsoft.Azure.Commands.Sql.Auditing.Cmdlet.SetAzSqlServerAudit

解决办法 #

问题出在PowerShell对字符串参数的解析与Azure SQL审计谓词的语法兼容上，修改PredicateExpression的引号使用方式即可解决：

方案1：单引号包裹并转义内部单引号 #

Set-AzSqlServerAudit -ResourceGroupName "My_RG" -ServerName "My_Server" -BlobStorageTargetState Enabled -StorageAccountResourceId "XXXXXX" -RetentionInDays 365 -PredicateExpression '[database_principal_name]= ''dbo'' OR ([action_name] = ''DATABASE AUTHENTICATION FAILED'' OR [action_name] = ''CREATE SERVER AUDIT SPECIFICATION'' OR [action_name] = ''ALTER SERVER AUDIT SPECIFICATION'' OR [action_name] = ''DROP SERVER AUDIT SPECIFICATION'')'

方案2：使用Here-String传递表达式 #

$predicate = @"
[database_principal_name]= 'dbo' OR ([action_name] = 'DATABASE AUTHENTICATION FAILED' OR [action_name] = 'CREATE SERVER AUDIT SPECIFICATION' OR [action_name] = 'ALTER SERVER AUDIT SPECIFICATION' OR [action_name] = 'DROP SERVER AUDIT SPECIFICATION')
"@
Set-AzSqlServerAudit -ResourceGroupName "My_RG" -ServerName "My_Server" -BlobStorageTargetState Enabled -StorageAccountResourceId "XXXXXX" -RetentionInDays 365 -PredicateExpression $predicate

原因说明 #

PowerShell中双引号包裹的字符串会尝试解析内部特殊字符或变量，即便表达式中没有变量，复杂的OR嵌套结构也可能导致参数传递时出现解析异常。使用单引号转义内部单引号或Here-String，可确保表达式原封不动传递给Azure SQL服务，避免解析错误。

内容的提问来源于stack exchange，提问作者paulH