< Back
Set-AzureRmSqlDatabaseAuditing
Post
NAME Set-AzureRmSqlDatabaseAuditing
SYNOPSIS
Changes the auditing settings for an Azure SQL database.
SYNTAX
Set-AzureRmSqlDatabaseAuditing [-ResourceGroupName] <String> [-ServerName] <String> [-DatabaseName] <String> [-AuditAction <String[]>]
[-AuditActionGroup {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}] [-DefaultProfile <IAzureContextContainer>] [-PassThru] [-RetentionInDays
<UInt32>] -State {Enabled | Disabled} [-StorageAccountName <String>] [-StorageKeyType {Primary | Secondary}] [-Confirm] [-WhatIf]
[<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 .
PARAMETERS
-AuditAction <String[]>
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/re ... and-action
s#database-level-audit-actions.
Required? false
Position? named
Default value None
Accept pipeline input? True (ByPropertyName)
Accept wildcard characters? false
-AuditActionGroup <AuditActionGroups[]>
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/re ... ns#databas
e-level-audit-action-groups.
Required? false
Position? named
Default value None
Accept pipeline input? True (ByPropertyName)
Accept wildcard characters? false
-DatabaseName <String>
SQL Database name.
Required? true
Position? 2
Default value None
Accept pipeline input? True (ByPropertyName)
Accept wildcard characters? false
-DefaultProfile <IAzureContextContainer>
The credentials, account, tenant, and subscription used for communication with azure.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-PassThru [<SwitchParameter>]
{{Fill PassThru Description}}
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-ResourceGroupName <String>
The name of the resource group.
Required? true
Position? 0
Default value None
Accept pipeline input? True (ByPropertyName)
Accept wildcard characters? false
-RetentionInDays <UInt32>
The number of retention days for the audit logs.
Required? false
Position? named
Default value None
Accept pipeline input? True (ByPropertyName)
Accept wildcard characters? false
-ServerName <String>
SQL Database server name.
Required? true
Position? 1
Default value None
Accept pipeline input? True (ByPropertyName)
Accept wildcard characters? false
-State <String>
The state of the policy.
Required? true
Position? named
Default value None
Accept pipeline input? True (ByPropertyName)
Accept wildcard characters? false
-StorageAccountName <String>
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.
Required? false
Position? named
Default value None
Accept pipeline input? True (ByPropertyName)
Accept wildcard characters? false
-StorageKeyType <String>
Specifies which of the storage access keys to use.
Required? false
Position? named
Default value None
Accept pipeline input? True (ByPropertyName)
Accept wildcard characters? false
-Confirm [<SwitchParameter>]
Prompts you for confirmation before running the cmdlet.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-WhatIf [<SwitchParameter>]
Shows what would happen if the cmdlet runs. The cmdlet is not run.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (http://go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
None
This cmdlet does not accept any input.
OUTPUTS
Microsoft.Azure.Commands.Sql.Security.Model.DatabaseBlobAuditingSettingsModel
NOTES
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"
RELATED LINKS
Online Version: https://docs.microsoft.com/en-us/powers ... seauditing
SYNOPSIS
Changes the auditing settings for an Azure SQL database.
SYNTAX
Set-AzureRmSqlDatabaseAuditing [-ResourceGroupName] <String> [-ServerName] <String> [-DatabaseName] <String> [-AuditAction <String[]>]
[-AuditActionGroup {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}] [-DefaultProfile <IAzureContextContainer>] [-PassThru] [-RetentionInDays
<UInt32>] -State {Enabled | Disabled} [-StorageAccountName <String>] [-StorageKeyType {Primary | Secondary}] [-Confirm] [-WhatIf]
[<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 .
PARAMETERS
-AuditAction <String[]>
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/re ... and-action
s#database-level-audit-actions.
Required? false
Position? named
Default value None
Accept pipeline input? True (ByPropertyName)
Accept wildcard characters? false
-AuditActionGroup <AuditActionGroups[]>
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/re ... ns#databas
e-level-audit-action-groups.
Required? false
Position? named
Default value None
Accept pipeline input? True (ByPropertyName)
Accept wildcard characters? false
-DatabaseName <String>
SQL Database name.
Required? true
Position? 2
Default value None
Accept pipeline input? True (ByPropertyName)
Accept wildcard characters? false
-DefaultProfile <IAzureContextContainer>
The credentials, account, tenant, and subscription used for communication with azure.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-PassThru [<SwitchParameter>]
{{Fill PassThru Description}}
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-ResourceGroupName <String>
The name of the resource group.
Required? true
Position? 0
Default value None
Accept pipeline input? True (ByPropertyName)
Accept wildcard characters? false
-RetentionInDays <UInt32>
The number of retention days for the audit logs.
Required? false
Position? named
Default value None
Accept pipeline input? True (ByPropertyName)
Accept wildcard characters? false
-ServerName <String>
SQL Database server name.
Required? true
Position? 1
Default value None
Accept pipeline input? True (ByPropertyName)
Accept wildcard characters? false
-State <String>
The state of the policy.
Required? true
Position? named
Default value None
Accept pipeline input? True (ByPropertyName)
Accept wildcard characters? false
-StorageAccountName <String>
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.
Required? false
Position? named
Default value None
Accept pipeline input? True (ByPropertyName)
Accept wildcard characters? false
-StorageKeyType <String>
Specifies which of the storage access keys to use.
Required? false
Position? named
Default value None
Accept pipeline input? True (ByPropertyName)
Accept wildcard characters? false
-Confirm [<SwitchParameter>]
Prompts you for confirmation before running the cmdlet.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-WhatIf [<SwitchParameter>]
Shows what would happen if the cmdlet runs. The cmdlet is not run.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (http://go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
None
This cmdlet does not accept any input.
OUTPUTS
Microsoft.Azure.Commands.Sql.Security.Model.DatabaseBlobAuditingSettingsModel
NOTES
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"
RELATED LINKS
Online Version: https://docs.microsoft.com/en-us/powers ... seauditing