Set-AzSqlDatabaseAudit

Module:: Az.Sql

Changes the auditing settings for an Azure SQL database.

Syntax

Set-AzSqlDatabaseAudit
   [-AuditActionGroup <AuditActionGroups[]>]
   [-AuditAction <String[]>]
   [-PredicateExpression <String>]
   [-BlobStorageTargetState <String>]
   [-StorageAccountResourceId <String>]
   [-StorageKeyType <String>]
   [-RetentionInDays <UInt32>]
   [-EventHubTargetState <String>]
   [-EventHubName <String>]
   [-EventHubAuthorizationRuleResourceId <String>]
   [-LogAnalyticsTargetState <String>]
   [-WorkspaceResourceId <String>]
   [-PassThru]
   [-ResourceGroupName] <String>
   [-ServerName] <String>
   [-DatabaseName] <String>
   [-DefaultProfile <IAzureContextContainer>]
   [-WhatIf]
   [-Confirm]
   [<CommonParameters>]

Set-AzSqlDatabaseAudit
   [-AuditActionGroup <AuditActionGroups[]>]
   [-AuditAction <String[]>]
   [-PredicateExpression <String>]
   [-BlobStorageTargetState <String>]
   [-StorageAccountResourceId <String>]
   [-StorageKeyType <String>]
   [-RetentionInDays <UInt32>]
   [-EventHubTargetState <String>]
   [-EventHubName <String>]
   [-EventHubAuthorizationRuleResourceId <String>]
   [-LogAnalyticsTargetState <String>]
   [-WorkspaceResourceId <String>]
   [-PassThru]
   -DatabaseObject <AzureSqlDatabaseModel>
   [-DefaultProfile <IAzureContextContainer>]
   [-WhatIf]
   [-Confirm]
   [<CommonParameters>]

Description

The Set-AzSqlDatabaseAudit 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. When blob storage is a destination for audit logs, specify the StorageAccountResourceId 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.

Examples

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

PS C:\>Set-AzSqlDatabaseAudit -ResourceGroupName "ResourceGroup01" -ServerName "Server01" -DatabaseName "Database01" -BlobStorageTargetState Enabled  -StorageAccountResourceId "/subscriptions/7fe3301d-31d3-4668-af5e-211a890ba6e3/resourceGroups/resourcegroup01/providers/Microsoft.Storage/storageAccounts/mystorage"

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

PS C:\>Set-AzSqlDatabaseAudit -ResourceGroupName "ResourceGroup01" -ServerName "Server01" -DatabaseName "Database01" -BlobStorageTargetState Disabled

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

PS C:\>Set-AzSqlDatabaseAudit -ResourceGroupName "ResourceGroup01" -ServerName "Server01" -DatabaseName "Database01" -PredicateExpression "schema_name <> 'sys''" -BlobStorageTargetState Enabled -StorageAccountResourceId "/subscriptions/7fe3301d-31d3-4668-af5e-211a890ba6e3/resourceGroups/resourcegroup01/providers/Microsoft.Storage/storageAccounts/mystorage"

Example 4: Remove the filtering from the auditing policy of an Azure SQL database

PS C:\>Set-AzSqlDatabaseAudit -ResourceGroupName "ResourceGroup01" -ServerName "Server01" -DatabaseName "Database01" -PredicateExpression ""

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

PS C:\>Set-AzSqlDatabaseAudit -ResourceGroupName "ResourceGroup01" -ServerName "Server01" -DatabaseName "Database01" -EventHubTargetState Enabled -EventHubName "EventHubName" -EventHubAuthorizationRuleResourceId "EventHubAuthorizationRuleResourceId"

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

PS C:\>Set-AzSqlDatabaseAudit -ResourceGroupName "ResourceGroup01" -ServerName "Server01" -DatabaseName "Database01" -EventHubTargetState Disabled

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

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

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

PS C:\>Set-AzSqlDatabaseAudit -ResourceGroupName "ResourceGroup01" -ServerName "Server01" -DatabaseName "Database01" -LogAnalyticsTargetState Disabled

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

PS C:\>Get-AzSqlDatabase -ResourceGroupName "ResourceGroup01" -ServerName "Server01" -DatabaseName "Database01" | Set-AzSqlDatabaseAudit -LogAnalyticsTargetState Disabled

Example 10: Disable sending audit records of an Azure SQL database to blob storage, and enable sending them to log analytics.

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

Example 11: Enable sending audit records of an Azure SQL database to blob storage, event hub and log analytics.

PS C:\>Set-AzSqlDatabaseAudit -ResourceGroupName "ResourceGroup01" -ServerName "Server01" -DatabaseName "Database01" -BlobStorageTargetState Enabled -StorageAccountResourceId "/subscriptions/7fe3301d-31d3-4668-af5e-211a890ba6e3/resourceGroups/resourcegroup01/providers/Microsoft.Storage/storageAccounts/mystorage" -EventHubTargetState Enabled -EventHubName "EventHubName" -EventHubAuthorizationRuleResourceId "EventHubAuthorizationRuleResourceId" -LogAnalyticsTargetState Enabled  -WorkspaceResourceId "/subscriptions/4b9e8510-67ab-4e9a-95a9-e2f1e570ea9c/resourceGroups/insights-integration/providers/Microsoft.OperationalInsights/workspaces/viruela2"

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
Accept pipeline input:	False
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:	False
Accept wildcard characters:	False

-BlobStorageTargetState

Indicates whether blob storage is a destination for audit records.

Type:	String
Accepted values:	Enabled, Disabled
Position:	Named
Default value:	None
Accept pipeline input:	False
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:	False
Accept wildcard characters:	False

-DatabaseObject

The database object to manage its audit policy.

Type:	AzureSqlDatabaseModel
Position:	Named
Default value:	None
Accept pipeline input:	True
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

-EventHubAuthorizationRuleResourceId

The resource Id for the event hub authorization rule

Type:	String
Position:	Named
Default value:	None
Accept pipeline input:	False
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:	False
Accept wildcard characters:	False

-EventHubTargetState

Indicates whether event hub is a destination for audit records.

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

-LogAnalyticsTargetState

Indicates whether log analytics is a destination for audit records.

Type:	String
Accepted values:	Enabled, Disabled
Position:	Named
Default value:	None
Accept pipeline input:	False
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:	False
Accept wildcard characters:	False

-ResourceGroupName

The name of the resource group.

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

-RetentionInDays

The number of retention days for the audit logs.

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

-ServerName

SQL server name.

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

-StorageAccountResourceId

The storage account resource id

Type:	String
Position:	Named
Default value:	None
Accept pipeline input:	False
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:	False
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:	False
Accept wildcard characters:	False

Inputs

String

AzureSqlDatabaseModel

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

String[]

Guid

Nullable<T>[[System.UInt32, System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e]]

Microsoft.Azure.Commands.Sql.Auditing.Model.DatabaseAuditModel

Outputs

Boolean