Set-AzSqlDatabaseAudit
Changes the auditing settings for an Azure SQL Database.
Syntax
DatabaseParameterSet (Default)
Set-AzSqlDatabaseAudit
[-ResourceGroupName] <String>
[-ServerName] <String>
[-DatabaseName] <String>
[-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]
[-UseIdentity <String>]
[-DefaultProfile <IAzureContextContainer>]
[-WhatIf]
[-Confirm]
[<CommonParameters>]
DatabaseObjectParameterSet
Set-AzSqlDatabaseAudit
-DatabaseObject <AzureSqlDatabaseModel>
[-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]
[-UseIdentity <String>]
[-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. If using blob storage as destination, 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
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
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
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
Set-AzSqlDatabaseAudit -ResourceGroupName "ResourceGroup01" -ServerName "Server01" -DatabaseName "Database01" -PredicateExpression ""
Example 5: Enable the event hub auditing policy of an Azure SQL Database
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
Set-AzSqlDatabaseAudit -ResourceGroupName "ResourceGroup01" -ServerName "Server01" -DatabaseName "Database01" -EventHubTargetState Disabled
Example 7: Enable the log analytics auditing policy of an Azure SQL Database
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
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
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.
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.
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.if the reference object is dropped and recreated ,audit configuration should be resaved. 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://learn.microsoft.com/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions#database-level-audit-actions .
Parameter properties
Type: String [ ]
Default value: None Supports wildcards: False DontShow: False
Parameter sets
(All)
Position: Named Mandatory: False Value from pipeline: False Value from pipeline by property name: False Value from remaining arguments: 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://learn.microsoft.com/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions#database-level-audit-action-groups .
Parameter properties
Type: AuditActionGroups [ ]
Default value: None 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, LEDGER_OPERATION_GROUP, DBCC_GROUP, DATABASE_OWNERSHIP_CHANGE_GROUP, DATABASE_CHANGE_GROUP Supports wildcards: False DontShow: False
Parameter sets
(All)
Position: Named Mandatory: False Value from pipeline: False Value from pipeline by property name: False Value from remaining arguments: False
-BlobStorageTargetState
Indicates whether blob storage is a destination for audit records.
Parameter properties
Type: String
Default value: None Accepted values: Enabled, Disabled Supports wildcards: False DontShow: False
Parameter sets
(All)
Position: Named Mandatory: False Value from pipeline: False Value from pipeline by property name: False Value from remaining arguments: False
-Confirm
Prompts you for confirmation before running the cmdlet.
Parameter properties
Type: SwitchParameter
Default value: None Supports wildcards: False DontShow: False Aliases: cf
Parameter sets
(All)
Position: Named Mandatory: False Value from pipeline: False Value from pipeline by property name: False Value from remaining arguments: False
-DatabaseName
SQL Database name.
Parameter properties
Type: String
Default value: None Supports wildcards: False DontShow: False
Parameter sets
DatabaseParameterSet
Position: 2 Mandatory: True Value from pipeline: False Value from pipeline by property name: False Value from remaining arguments: False
-DatabaseObject
The database object to manage its audit policy.
Parameter properties
Parameter sets
DatabaseObjectParameterSet
Position: Named Mandatory: True Value from pipeline: True Value from pipeline by property name: False Value from remaining arguments: False
-DefaultProfile
The credentials, account, tenant, and subscription used for communication with Azure.
Parameter properties
Type: IAzureContextContainer
Default value: None Supports wildcards: False DontShow: False Aliases: AzContext, AzureRmContext, AzureCredential
Parameter sets
(All)
Position: Named Mandatory: False Value from pipeline: False Value from pipeline by property name: False Value from remaining arguments: False
-EventHubAuthorizationRuleResourceId
The resource Id for the event hub authorization rule
Parameter properties
Type: String
Default value: None Supports wildcards: False DontShow: False
Parameter sets
(All)
Position: Named Mandatory: False Value from pipeline: False Value from pipeline by property name: False Value from remaining arguments: False
-EventHubName
The name of the event hub. If none is specified when providing EventHubAuthorizationRuleResourceId, the default event hub will be selected.
Parameter properties
Type: String
Default value: None Supports wildcards: False DontShow: False
Parameter sets
(All)
Position: Named Mandatory: False Value from pipeline: False Value from pipeline by property name: False Value from remaining arguments: False
-EventHubTargetState
Indicates whether event hub is a destination for audit records.
Parameter properties
Type: String
Default value: None Accepted values: Enabled, Disabled Supports wildcards: False DontShow: False
Parameter sets
(All)
Position: Named Mandatory: False Value from pipeline: False Value from pipeline by property name: False Value from remaining arguments: False
-LogAnalyticsTargetState
Indicates whether log analytics is a destination for audit records.
Parameter properties
Type: String
Default value: None Accepted values: Enabled, Disabled Supports wildcards: False DontShow: False
Parameter sets
(All)
Position: Named Mandatory: False Value from pipeline: False Value from pipeline by property name: False Value from remaining arguments: False
-PassThru
Specifies whether to output the auditing policy at end of cmdlet execution
Parameter properties
Type: SwitchParameter
Default value: None Supports wildcards: False DontShow: False
Parameter sets
(All)
Position: Named Mandatory: False Value from pipeline: False Value from pipeline by property name: False Value from remaining arguments: False
-PredicateExpression
The T-SQL predicate (WHERE clause) used to filter audit logs.
Parameter properties
Type: String
Default value: None Supports wildcards: False DontShow: False
Parameter sets
(All)
Position: Named Mandatory: False Value from pipeline: False Value from pipeline by property name: False Value from remaining arguments: False
-ResourceGroupName
The name of the resource group.
Parameter properties
Type: String
Default value: None Supports wildcards: False DontShow: False
Parameter sets
DatabaseParameterSet
Position: 0 Mandatory: True Value from pipeline: False Value from pipeline by property name: False Value from remaining arguments: False
-RetentionInDays
The number of retention days for the audit logs. Only applies to Storage account as destination.
Parameter properties
Parameter sets
(All)
Position: Named Mandatory: False Value from pipeline: False Value from pipeline by property name: False Value from remaining arguments: False
-ServerName
SQL server name.
Parameter properties
Type: String
Default value: None Supports wildcards: False DontShow: False
Parameter sets
DatabaseParameterSet
Position: 1 Mandatory: True Value from pipeline: False Value from pipeline by property name: False Value from remaining arguments: False
-StorageAccountResourceId
The storage account resource id
Parameter properties
Type: String
Default value: None Supports wildcards: False DontShow: False
Parameter sets
(All)
Position: Named Mandatory: False Value from pipeline: False Value from pipeline by property name: False Value from remaining arguments: False
-StorageKeyType
Specifies which of the storage access keys to use.
Parameter properties
Type: String
Default value: None Accepted values: Primary, Secondary Supports wildcards: False DontShow: False
Parameter sets
(All)
Position: Named Mandatory: False Value from pipeline: False Value from pipeline by property name: False Value from remaining arguments: False
-UseIdentity
Indicates whether to use managed identity or not. It is required when you want to use managed identity while target storage is not behind firewall.
Parameter properties
Type: String
Default value: None Supports wildcards: False DontShow: False
Parameter sets
(All)
Position: Named Mandatory: False Value from pipeline: False Value from pipeline by property name: False Value from remaining arguments: False
-WhatIf
Shows what would happen if the cmdlet runs. The cmdlet is not run.
Parameter properties
Type: SwitchParameter
Default value: None Supports wildcards: False DontShow: False Aliases: wi
Parameter sets
(All)
Position: Named Mandatory: False Value from pipeline: False Value from pipeline by property name: False Value from remaining arguments: 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
Parameter properties
Type: String
Default value: None Supports wildcards: False DontShow: False
Parameter sets
(All)
Position: Named Mandatory: False Value from pipeline: False Value from pipeline by property name: False Value from remaining arguments: False
CommonParameters
This cmdlet supports the common parameters: -Debug, -ErrorAction, -ErrorVariable,
-InformationAction, -InformationVariable, -OutBuffer, -OutVariable, -PipelineVariable,
-ProgressAction, -Verbose, -WarningAction, and -WarningVariable. For more information, see
about_CommonParameters .
Outputs
