Set-AzureRmSqlDatabaseAuditing

Changes the auditing settings for an Azure SQL database.

Warning

The AzureRM PowerShell module has been officially deprecated as of February 29, 2024. Users are advised to migrate from AzureRM to the Az PowerShell module to ensure continued support and updates.

Although the AzureRM module may still function, it's no longer maintained or supported, placing any continued use at the user's discretion and risk. Please refer to our migration resources for guidance on transitioning to the Az module.

Syntax

Set-AzureRmSqlDatabaseAuditing
   -State <String>
   [-PassThru]
   [-AuditActionGroup <AuditActionGroups[]>]
   [-AuditAction <String[]>]
   [-StorageAccountName <String>]
   [-StorageKeyType <String>]
   [-RetentionInDays <UInt32>]
   [-PredicateExpression <String>]
   [-ServerName] <String>
   [-DatabaseName] <String>
   [-ResourceGroupName] <String>
   [-DefaultProfile <IAzureContextContainer>]
   [-WhatIf]
   [-Confirm]
   [<CommonParameters>]
Set-AzureRmSqlDatabaseAuditing
   -State <String>
   [-PassThru]
   [-AuditActionGroup <AuditActionGroups[]>]
   [-AuditAction <String[]>]
   -StorageAccountName <String>
   [-StorageAccountSubscriptionId <Guid>]
   [-StorageKeyType <String>]
   [-RetentionInDays <UInt32>]
   [-PredicateExpression <String>]
   [-ServerName] <String>
   [-DatabaseName] <String>
   [-ResourceGroupName] <String>
   [-DefaultProfile <IAzureContextContainer>]
   [-WhatIf]
   [-Confirm]
   [<CommonParameters>]

Description

The Set-AzureRmSqlDatabaseAuditing cmdlet changes the auditing settings of an Azure SQL database. To use the cmdlet, use the ResourceGroupName, ServerName, and DatabaseName parameters to identify the database. Specify the StorageAccountName parameter to specify the storage account for the audit logs and the StorageKeyType parameter to define the storage keys. Use the State parameter to enable/disable the policy. You can also define retention for the audit logs by setting the value of the RetentionInDays parameter to define the period for the audit logs. After the cmdlet runs successfully, auditing of the database is enabled. If the cmdlet succeeds and you use the PassThru parameter, it returns an object describing the current blob auditing policy in addition to the database identifiers. Database identifiers include, but are not limited to, ResourceGroupName, ServerName, and DatabaseName.

Examples

Example 1: Enable the auditing policy of an Azure SQL database

PS C:\>Set-AzureRmSqlDatabaseAuditing -State Enabled -ResourceGroupName "ResourceGroup01" -ServerName "Server01" -StorageAccountName "Storage22" -DatabaseName "Database01"

Example 2: Disable the blob auditing policy of an Azure SQL database

PS C:\>Set-AzureRmSqlDatabaseAuditing -State Disabled -ResourceGroupName "ResourceGroup01" -ServerName "Server01" -DatabaseName "Database01"

Example 3: Enable the auditing policy of an Azure SQL database using a storage account from a different subscription

PS C:\>Set-AzureRmSqlDatabaseAuditing -State Enabled -ResourceGroupName "ResourceGroup01" -ServerName "Server01" -StorageAccountName "Storage22" -StorageAccountSubscriptionId "7fe3301d-31d3-4668-af5e-211a890ba6e3"

Example 4: Enable the extended auditing policy of an Azure SQL database

PS C:\>Set-AzureRmSqlDatabaseAuditing -State Enabled -ResourceGroupName "ResourceGroup01" -ServerName "Server01" -StorageAccountName "Storage22" -DatabaseName "Database01" -PredicateExpression "statement <> 'select 1'"

Example 5: Remove the extended auditing policy of an Azure SQL database, and set an auditing policy instead of it.

PS C:\>Set-AzureRmSqlDatabaseAuditing -State Enabled -ResourceGroupName "ResourceGroup01" -ServerName "Server01" -StorageAccountName "Storage22" -DatabaseName "Database01" -PredicateExpression ""

Parameters

-AuditAction

The set of audit actions. The supported actions to audit are: SELECT UPDATE INSERT DELETE EXECUTE RECEIVE REFERENCES The general form for defining an action to be audited is: [action] ON [object] BY [principal] Note that [object] in the above format can refer to an object like a table, view, or stored procedure, or an entire database or schema. For the latter cases, the forms DATABASE::[dbname] and SCHEMA::[schemaname] are used, respectively. For example: SELECT on dbo.myTable by public SELECT on DATABASE::myDatabase by public SELECT on SCHEMA::mySchema by public For more information, see https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions#database-level-audit-actions.

Type:String[]
Position:Named
Default value:None
Required:False
Accept pipeline input:True
Accept wildcard characters:False

-AuditActionGroup

The recommended set of action groups to use is the following combination - this 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" This above combination is also the set that is configured by default. These groups cover all SQL statements and stored procedures executed against the database, and should not be used in combination with other groups as this will result in duplicate audit logs. For more information, see https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions#database-level-audit-action-groups.

Type:AuditActionGroups[]
Accepted values: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, AUDIT_CHANGE_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
Position:Named
Default value:None
Required:False
Accept pipeline input:True
Accept wildcard characters:False

-Confirm

Prompts you for confirmation before running the cmdlet.

Type:SwitchParameter
Aliases:cf
Position:Named
Default value:False
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-DatabaseName

SQL Database name.

Type:String
Position:2
Default value:None
Required:True
Accept pipeline input:True
Accept wildcard characters:False

-DefaultProfile

The credentials, account, tenant, and subscription used for communication with azure.

Type:IAzureContextContainer
Aliases:AzureRmContext, AzureCredential
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-PassThru

{{Fill PassThru Description}}

Type:SwitchParameter
Position:Named
Default value:False
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-PredicateExpression

The statement of the Where Clause used to filter audit logs.

Type:String
Position:Named
Default value:None
Required:False
Accept pipeline input:True
Accept wildcard characters:False

-ResourceGroupName

The name of the resource group.

Type:String
Position:0
Default value:None
Required:True
Accept pipeline input:True
Accept wildcard characters:False

-RetentionInDays

The number of retention days for the audit logs.

Type:Nullable<T>[UInt32]
Position:Named
Default value:None
Required:False
Accept pipeline input:True
Accept wildcard characters:False

-ServerName

SQL Database server name.

Type:String
Position:1
Default value:None
Required:True
Accept pipeline input:True
Accept wildcard characters:False

-State

The state of the policy.

Type:String
Accepted values:Enabled, Disabled
Position:Named
Default value:None
Required:True
Accept pipeline input:True
Accept wildcard characters:False

-StorageAccountName

The name of the storage account. Wildcard characters are not permitted. This parameter is not required. If you do not specify this parameter, the cmdlet uses the storage account that was defined previously as part of the auditing policy. If this is the first time an auditing policy is defined and you do not specify this parameter, the cmdlet fails.

Type:String
Position:Named
Default value:None
Required:True
Accept pipeline input:True
Accept wildcard characters:False

-StorageAccountSubscriptionId

Specifies storage account subscription id

Type:Guid
Position:Named
Default value:None
Required:False
Accept pipeline input:True
Accept wildcard characters:False

-StorageKeyType

Specifies which of the storage access keys to use.

Type:String
Accepted values:Primary, Secondary
Position:Named
Default value:None
Required:False
Accept pipeline input:True
Accept wildcard characters:False

-WhatIf

Shows what would happen if the cmdlet runs. The cmdlet is not run.

Type:SwitchParameter
Aliases:wi
Position:Named
Default value:False
Required:False
Accept pipeline input:False
Accept wildcard characters:False