Set-AzSqlDatabaseAuditing

Important: This cmdlet is deprecated, [Set-AzSqlDatabaseAudit](https://docs.microsoft.com/en-us/powershell/module/az.sql/set-azsqldatabaseaudit) is replacing it. Changes the auditing settings for an Azure SQL database.

Syntax

Set-AzSqlDatabaseAuditing
   [-ResourceGroupName] <String>
   [-ServerName] <String>
   [-DatabaseName] <String>
   -State <String>
   [-PassThru]
   [-AuditActionGroup <AuditActionGroups[]>]
   [-AuditAction <String[]>]
   [-PredicateExpression <String>]
   [-AsJob]
   [-BlobStorage]
   [-StorageAccountName <String>]
   [-StorageKeyType <String>]
   [-RetentionInDays <UInt32>]
   [-DefaultProfile <IAzureContextContainer>]
   [-WhatIf]
   [-Confirm]
   [<CommonParameters>]
Set-AzSqlDatabaseAuditing
   [-ResourceGroupName] <String>
   [-ServerName] <String>
   [-DatabaseName] <String>
   -State <String>
   [-PassThru]
   [-AuditActionGroup <AuditActionGroups[]>]
   [-AuditAction <String[]>]
   [-PredicateExpression <String>]
   [-AsJob]
   [-BlobStorage]
   -StorageAccountName <String>
   -StorageAccountSubscriptionId <Guid>
   [-StorageKeyType <String>]
   [-RetentionInDays <UInt32>]
   [-DefaultProfile <IAzureContextContainer>]
   [-WhatIf]
   [-Confirm]
   [<CommonParameters>]
Set-AzSqlDatabaseAuditing
   [-ResourceGroupName] <String>
   [-ServerName] <String>
   [-DatabaseName] <String>
   -State <String>
   [-PassThru]
   [-AuditActionGroup <AuditActionGroups[]>]
   [-AuditAction <String[]>]
   [-PredicateExpression <String>]
   [-AsJob]
   [-EventHubName <String>]
   [-EventHubAuthorizationRuleResourceId <String>]
   [-EventHub]
   [-DefaultProfile <IAzureContextContainer>]
   [-WhatIf]
   [-Confirm]
   [<CommonParameters>]
Set-AzSqlDatabaseAuditing
   [-ResourceGroupName] <String>
   [-ServerName] <String>
   [-DatabaseName] <String>
   -State <String>
   [-PassThru]
   [-AuditActionGroup <AuditActionGroups[]>]
   [-AuditAction <String[]>]
   [-PredicateExpression <String>]
   [-AsJob]
   [-WorkspaceResourceId <String>]
   [-LogAnalytics]
   [-DefaultProfile <IAzureContextContainer>]
   [-WhatIf]
   [-Confirm]
   [<CommonParameters>]
Set-AzSqlDatabaseAuditing
   -InputObject <AzureSqlDatabaseModel>
   -State <String>
   [-PassThru]
   [-AuditActionGroup <AuditActionGroups[]>]
   [-AuditAction <String[]>]
   [-PredicateExpression <String>]
   [-AsJob]
   [-BlobStorage]
   [-StorageAccountName <String>]
   [-StorageKeyType <String>]
   [-RetentionInDays <UInt32>]
   [-DefaultProfile <IAzureContextContainer>]
   [-WhatIf]
   [-Confirm]
   [<CommonParameters>]
Set-AzSqlDatabaseAuditing
   -InputObject <AzureSqlDatabaseModel>
   -State <String>
   [-PassThru]
   [-AuditActionGroup <AuditActionGroups[]>]
   [-AuditAction <String[]>]
   [-PredicateExpression <String>]
   [-AsJob]
   [-BlobStorage]
   -StorageAccountName <String>
   -StorageAccountSubscriptionId <Guid>
   [-StorageKeyType <String>]
   [-RetentionInDays <UInt32>]
   [-DefaultProfile <IAzureContextContainer>]
   [-WhatIf]
   [-Confirm]
   [<CommonParameters>]
Set-AzSqlDatabaseAuditing
   -InputObject <AzureSqlDatabaseModel>
   -State <String>
   [-PassThru]
   [-AuditActionGroup <AuditActionGroups[]>]
   [-AuditAction <String[]>]
   [-PredicateExpression <String>]
   [-AsJob]
   [-EventHubName <String>]
   [-EventHubAuthorizationRuleResourceId <String>]
   [-EventHub]
   [-DefaultProfile <IAzureContextContainer>]
   [-WhatIf]
   [-Confirm]
   [<CommonParameters>]
Set-AzSqlDatabaseAuditing
   -InputObject <AzureSqlDatabaseModel>
   -State <String>
   [-PassThru]
   [-AuditActionGroup <AuditActionGroups[]>]
   [-AuditAction <String[]>]
   [-PredicateExpression <String>]
   [-AsJob]
   [-WorkspaceResourceId <String>]
   [-LogAnalytics]
   [-DefaultProfile <IAzureContextContainer>]
   [-WhatIf]
   [-Confirm]
   [<CommonParameters>]

Description

The Set-AzSqlDatabaseAuditing 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. The audit logs destination is determined by specifying one of the following switch parameters: BlobStorage, LogAnalytics or EventHub (if none is specified, the default is BlobStorage). Use the State parameter to enable/disable the policy. When audit logs destination is blob storage, specify the StorageAccountName parameter to determine the storage account for the audit logs and the StorageKeyType parameter to define the storage keys. 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. If the cmdlet succeeds and you use the PassThru parameter, it returns an object describing the current auditing settings in addition to the database identifiers. Database identifiers include, but are not limited to, ResourceGroupName, ServerName, and DatabaseName.

Examples

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

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

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

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

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

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

Example 4: Enable the blob storage auditing policy of an Azure SQL database with advanced filtering using a T-SQL predicate

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

Example 5: Remove the advanced filtering setting from the blob storage auditing policy of an Azure SQL database

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

Example 6: Enable the event hub auditing policy of an Azure SQL database

PS C:\>Set-AzSqlDatabaseAuditing -State Enabled -ResourceGroupName "ResourceGroup01" -ServerName "Server01" -EventHub -EventHubName "EventHubName" -EventHubAuthorizationRuleResourceId "EventHubAuthorizationRuleResourceId"

Example 7: Disable the event hub auditing policy of an Azure SQL database

PS C:\>Set-AzSqlDatabaseAuditing -State Disabled -ResourceGroupName "ResourceGroup01" -ServerName "Server01" -EventHub

Example 8: Enable the log analytics auditing policy of an Azure SQL database

PS C:\>Set-AzSqlDatabaseAuditing -State Enabled -ResourceGroupName "ResourceGroup01" -ServerName "Server01" -DatabaseName "Database01" -LogAnalytics -WorkspaceResourceId "/subscriptions/4b9e8510-67ab-4e9a-95a9-e2f1e570ea9c/resourceGroups/insights-integration/providers/Microsoft.OperationalInsights/workspaces/viruela2"

Example 9: Disable the log analytics auditing policy of an Azure SQL database

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

Example 10: Disable, through pipeline, the log analytics auditing policy of an Azure SQL database

PS C:\>Get-AzSqlDatabase -ResourceGroupName "ResourceGroup01" -ServerName "Server01" -DatabaseName "Database01" | Set-AzSqlDatabaseAuditing -LogAnalytics -State Disabled

Parameters

-AsJob

Run cmdlet in the background

Type:SwitchParameter
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-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:System.String[]
Position:Named
Default value:None
Accept pipeline input:True (ByPropertyName)
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:Microsoft.Azure.Commands.Sql.Auditing.Model.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, 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
Accept pipeline input:True (ByPropertyName)
Accept wildcard characters:False
-BlobStorage

Specifies that audit logs destination is blob storage

Type:SwitchParameter
Position:Named
Default value:None
Accept pipeline input:True (ByPropertyName)
Accept wildcard characters:False
-Confirm

Prompts you for confirmation before running the cmdlet.

Type:SwitchParameter
Aliases:cf
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-DatabaseName

SQL Database name.

Type:String
Position:2
Default value:None
Accept pipeline input:True (ByPropertyName)
Accept wildcard characters:False
-DefaultProfile

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

Type:Microsoft.Azure.Commands.Common.Authentication.Abstractions.Core.IAzureContextContainer
Aliases:AzContext, AzureRmContext, AzureCredential
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-EventHub

Specifies that audit logs destination is event hub

Type:SwitchParameter
Position:Named
Default value:None
Accept pipeline input:True (ByPropertyName)
Accept wildcard characters:False
-EventHubAuthorizationRuleResourceId

The resource Id for the event hub authorization rule

Type:String
Position:Named
Default value:None
Accept pipeline input:True (ByPropertyName)
Accept wildcard characters:False
-EventHubName

The name of the event hub. If none is specified when providing EventHubAuthorizationRuleResourceId, the default event hub will be selected.

Type:String
Position:Named
Default value:None
Accept pipeline input:True (ByPropertyName)
Accept wildcard characters:False
-InputObject

The database object to manage its audit policy.

Type:AzureSqlDatabaseModel
Position:Named
Default value:None
Accept pipeline input:True (ByValue)
Accept wildcard characters:False
-LogAnalytics

Specifies that audit logs destination is log analytics

Type:SwitchParameter
Position:Named
Default value:None
Accept pipeline input:True (ByPropertyName)
Accept wildcard characters:False
-PassThru

Specifies whether to output the auditing policy at end of cmdlet execution

Type:SwitchParameter
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-PredicateExpression

The T-SQL predicate (WHERE clause) used to filter audit logs.

Type:String
Position:Named
Default value:None
Accept pipeline input:True (ByPropertyName)
Accept wildcard characters:False
-ResourceGroupName

The name of the resource group.

Type:String
Position:0
Default value:None
Accept pipeline input:True (ByPropertyName)
Accept wildcard characters:False
-RetentionInDays

The number of retention days for the audit logs.

Type:System.Nullable`1[System.UInt32]
Position:Named
Default value:None
Accept pipeline input:True (ByPropertyName)
Accept wildcard characters:False
-ServerName

SQL server name.

Type:String
Position:1
Default value:None
Accept pipeline input:True (ByPropertyName)
Accept wildcard characters:False
-State

The state of the policy.

Type:String
Accepted values:Enabled, Disabled
Position:Named
Default value:None
Accept pipeline input:True (ByPropertyName)
Accept wildcard characters:False
-StorageAccountName

The name of the storage account.

Type:String
Position:Named
Default value:None
Accept pipeline input:True (ByPropertyName)
Accept wildcard characters:False
-StorageAccountSubscriptionId

The storage account subscription id

Type:Guid
Position:Named
Default value:None
Accept pipeline input:True (ByPropertyName)
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
Accept pipeline input:True (ByPropertyName)
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:None
Accept pipeline input:False
Accept wildcard characters:False
-WorkspaceResourceId

The workspace ID (resource ID of a Log Analytics workspace) for a Log Analytics workspace to which you would like to send Audit Logs. Example: /subscriptions/4b9e8510-67ab-4e9a-95a9-e2f1e570ea9c/resourceGroups/insights-integration/providers/Microsoft.OperationalInsights/workspaces/viruela2

Type:String
Position:Named
Default value:None
Accept pipeline input:True (ByPropertyName)
Accept wildcard characters:False

Inputs

String

AzureSqlDatabaseModel

Microsoft.Azure.Commands.Sql.Auditing.Model.AuditActionGroups[]

System.String[]

SwitchParameter

Guid

System.Nullable`1[[System.UInt32, System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e]]

Outputs

DatabaseBlobAuditingSettingsModel