Set-AzureSqlDatabaseAuditingPolicy
Set-AzureSqlDatabaseAuditingPolicy
Sets the auditing policy for an Azure SQL database.
Syntax
Parameter Set: Default
Set-AzureSqlDatabaseAuditingPolicy [-ResourceGroupName] <System.String> [-ServerName] <System.String> [-DatabaseName] <System.String> [-EventType {PlainSQL_Success | PlainSQL_Failure | ParameterizedSQL_Success | ParameterizedSQL_Failure | StoredProcedure_Success | StoredProcedure_Failure | Login_Success | Login_Failure | TransactionManagement_Success | TransactionManagement_Failure | All | None}[] ] [-PassThru] [-Profile <Microsoft.Azure.Common.Authentication.Models.AzureProfile> ] [-RetentionInDays <Nullable [System.UInt32]> ] [-StorageAccountName <System.String> ] [-StorageKeyType <System.String> {Primary | Secondary} ] [-TableIdentifier <System.String> ]
Detailed Description
The Set-AzureSqlDatabaseAuditingPolicy cmdlet changes the auditing policy of an Azure SQL database. To use the cmdlet, use the ResourceGroupName, ServerName, and DatabaseName parameters to identify the database, the StorageAccountName parameter to specify the storage account to be used for the audit logs, and the EventType parameter to define which event types to audit. After the cmdlet runs successfully, auditing of the database is enabled. If the database used the policy of its server for auditing before you ran this cmdlet, auditing stops using the policy of its server. If the cmdlet succeeds and you use the PassThru parameter, it returns an object describing the current auditing policy, as well as the database identifiers. Database identifiers include, but are not limited to, ResourceGroupName, ServerName, and DatabaseName.
Parameters
-DatabaseName<System.String>
Specifies the name of the database.
Aliases |
none |
Required? |
true |
Position? |
3 |
Default Value |
none |
Accept Pipeline Input? |
true(ByPropertyName) |
Accept Wildcard Characters? |
false |
-EventType<System.String[]>
Specifies the event types to audit. Valid values are:
-- PlainSQL_Success
-- PlainSQL_Failure
-- ParameterizedSQL_Success
-- ParameterizedSQL_Failure
-- StoredProcedure_Success
-- StoredProcedure_Failure
-- Login_Success
-- Login_Failure
-- TransactionManagement_Success
-- TransactionManagement_Failure
-- All
-- None
You can specify several event types. You can specify All to audit all of the event types or None to specify that no events will be audited. If you specify All or None at the same time, the cmdlet fails to run.
Aliases |
none |
Required? |
false |
Position? |
named |
Default Value |
none |
Accept Pipeline Input? |
true(ByPropertyName) |
Accept Wildcard Characters? |
false |
-PassThru
Returns an object representing the item with which you are working. By default, this cmdlet does not generate any output.
Aliases |
none |
Required? |
false |
Position? |
named |
Default Value |
none |
Accept Pipeline Input? |
false |
Accept Wildcard Characters? |
false |
-Profile<Microsoft.Azure.Common.Authentication.Models.AzureProfile>
Specifies the Azure profile from which this cmdlet reads. If you do not specify a profile, this cmdlet reads from the local default profile.
Aliases |
none |
Required? |
false |
Position? |
named |
Default Value |
none |
Accept Pipeline Input? |
false |
Accept Wildcard Characters? |
false |
-ResourceGroupName<System.String>
Specifies the name of the resource group that contains the database.
Aliases |
none |
Required? |
true |
Position? |
1 |
Default Value |
none |
Accept Pipeline Input? |
true(ByPropertyName) |
Accept Wildcard Characters? |
false |
-RetentionInDays<Nullable [System.UInt32]>
Specifies the number of retention days for the audit logs table. A value of zero (0) means that the table is not retained. The default value is zero. If you specify a value greater than zero, you must specify a value for the TableIdentifer parameter.
Aliases |
none |
Required? |
false |
Position? |
named |
Default Value |
none |
Accept Pipeline Input? |
true(ByPropertyName) |
Accept Wildcard Characters? |
false |
-ServerName<System.String>
Specifies the name of the server that contains the database.
Aliases |
none |
Required? |
true |
Position? |
2 |
Default Value |
none |
Accept Pipeline Input? |
true(ByPropertyName) |
Accept Wildcard Characters? |
false |
-StorageAccountName<System.String>
Specifies the name of the storage account to be used when auditing the database. Wildcards are not permitted. This parameter is not required. When this parameter is not provided, the cmdlet will use the storage account that was defined previously as part of the auditing policy of the database. If this is the first time a database auditing policy is defined and this parameter is not provided, the cmdlet will fail.
Aliases |
none |
Required? |
false |
Position? |
named |
Default Value |
none |
Accept Pipeline Input? |
true(ByPropertyName) |
Accept Wildcard Characters? |
false |
-StorageKeyType<System.String>
Specifies which of the storage access keys to use. Valid values are:
-- Primary
-- Secondary
The default value is Primary.
Aliases |
none |
Required? |
false |
Position? |
named |
Default Value |
none |
Accept Pipeline Input? |
true(ByPropertyName) |
Accept Wildcard Characters? |
false |
-TableIdentifier<System.String>
Specifies the name of the audit logs table. Specify this value if you specify a value greater than zero for the RetentionInDays parameter.
Aliases |
none |
Required? |
false |
Position? |
named |
Default Value |
none |
Accept Pipeline Input? |
true(ByPropertyName) |
Accept Wildcard Characters? |
false |
Inputs
The input type is the type of the objects that you can pipe to the cmdlet.
- None
Outputs
The output type is the type of the objects that the cmdlet emits.
- Microsoft.Azure.Commands.Sql.Security.Model.DatabaseAuditingPolicyModel
Notes
- This cmdlet supports the common parameters: Verbose, Debug, ErrorAction, ErrorVariable, WarningAction, WarningVariable, OutBuffer, PipelineVariable, and OutVariable. For more information, see about_CommonParameters.
Examples
Example 1: Set the auditing policy of a database
This command sets the auditing policy of database named Database01 located in Server02 to use the storage account named Storage31.
PS C:\> Set-AzureSqlDatabaseAuditingPolicy -ResourceGroupName "ResourceGroup11" -ServerName "Server02" -DatabaseName "Database01" –StorageAccountName "Storage31"
Example 2: Set the storage account key of an existing auditing policy of a database
This command sets the auditing policy of database named Database01 located in Server02 to keep using the same storage account name, but use the secondary key.
PS C:\> Set-AzureSqlDatabaseAuditingPolicy -ResourceGroupName "ResourceGroup11" -ServerName "Server02" -DatabaseName "Database01" –StorageAccountKey Secondary
Example 3: Set the auditing policy of a database to use a specific event type
This command sets the auditing policy of database named Database01 located in Server02 to log the Login_Failure event type. The command does not change the storage settings.
PS C:\> Set-AzureSqlDatabaseAuditingPolicy -ResourceGroupName "ResourceGroup11" -ServerName "Server02" -DatabaseName "Database01" –EventType Login_Failure
Related topics
Get-AzureSqlDatabaseAuditingPolicy