Set-AzSynapseSqlPoolAuditSetting
Changes the auditing settings for an Azure Synapse Analytics SQL pool.
Syntax
SqlPoolParameterSet (Default)
Set-AzSynapseSqlPoolAuditSetting
[[-ResourceGroupName] <String>]
[-WorkspaceName] <String>
-SqlPoolName <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]
[-DefaultProfile <IAzureContextContainer>]
[-WhatIf]
[-Confirm]
[<CommonParameters>]
SqlPoolParentObjectParameterSet
Set-AzSynapseSqlPoolAuditSetting
-WorkspaceObject <PSSynapseWorkspace>
-SqlPoolName <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]
[-DefaultProfile <IAzureContextContainer>]
[-WhatIf]
[-Confirm]
[<CommonParameters>]
SqlPoolObjectParameterSet
Set-AzSynapseSqlPoolAuditSetting
-SqlPoolObject <PSSynapseSqlPool>
[-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]
[-DefaultProfile <IAzureContextContainer>]
[-WhatIf]
[-Confirm]
[<CommonParameters>]
SqlPoolResourceIdParameterSet
Set-AzSynapseSqlPoolAuditSetting
-ResourceId <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]
[-DefaultProfile <IAzureContextContainer>]
[-WhatIf]
[-Confirm]
[<CommonParameters>]
Description
The Set-AzSynapseSqlPoolAuditSetting cmdlet changes the auditing settings of an Azure Synapse Analytics SQL pool. 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
Set-AzSynapseSqlPoolAuditSetting -WorkspaceName ContosoWorkspace -Name ContosoSqlPool -BlobStorageTargetState Enabled -StorageAccountResourceId "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/resourcegroup01/providers/Microsoft.Storage/storageAccounts/mystorage" -StorageKeyType Primary
Enable the blob storage auditing policy of an Azure Synapse Analytics SQL pool named ContosoSqlPool.
Example 2
Set-AzSynapseSqlPoolAuditSetting -WorkspaceName ContosoWorkspace -Name ContosoSqlPool -BlobStorageTargetState Disabled
Disable the blob storage auditing policy of an Azure Synapse Analytics SQL pool named ContosoSqlPool.
Example 3
Set-AzSynapseSqlPoolAuditSetting -WorkspaceName ContosoWorkspace -Name ContosoSqlPool -BlobStorageTargetState Enabled -StorageAccountResourceId "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/resourcegroup01/providers/Microsoft.Storage/storageAccounts/mystorage" -StorageKeyType Primary -PredicateExpression "statement <> 'select 1'"
Enable the blob storage auditing policy of an Azure Synapse Analytics SQL pool named ContosoSqlPool with advanced filtering using a T-SQL predicate.
Example 4
Set-AzSynapseSqlPoolAuditSetting -WorkspaceName ContosoWorkspace -Name ContosoSqlPool -PredicateExpression ""
Remove the advanced filtering setting from the auditing policy of an Azure Synapse Analytics SQL pool named ContosoSqlPool.
Example 5
Set-AzSynapseSqlPoolAuditSetting -WorkspaceName ContosoWorkspace -Name ContosoSqlPool -EventHubTargetState Enabled -EventHubName "EventHubName" -EventHubAuthorizationRuleResourceId "EventHubAuthorizationRuleResourceId"
Enable the event hub auditing policy of an Azure Synapse Analytics SQL pool named ContosoSqlPool.
Example 6
Set-AzSynapseSqlPoolAuditSetting -WorkspaceName ContosoWorkspace -Name ContosoSqlPool -EventHubTargetState Disabled
Disable the event hub auditing policy of an Azure Synapse Analytics SQL pool named ContosoSqlPool.
Example 7
Set-AzSynapseSqlPoolAuditSetting -WorkspaceName ContosoWorkspace -Name ContosoSqlPool -LogAnalyticsTargetState Enabled -WorkspaceResourceId "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/insights-integration/providers/Microsoft.OperationalInsights/workspaces/myworkspace"
Enable the log analytics auditing policy of an Azure Synapse Analytics SQL pool named ContosoSqlPool.
Example 8
Set-AzSynapseSqlPoolAuditSetting -WorkspaceName ContosoWorkspace -Name ContosoSqlPool -LogAnalyticsTargetState Disabled
Disable the log analytics auditing policy of an Azure Synapse Analytics SQL pool named ContosoSqlPool.
Example 9
Get-AzSynapseSqlPool -WorkspaceName ContosoWorkspace -Name ContosoSqlPool | Set-AzSynapseSqlPoolAuditSetting -BlobStorageTargetState Disabled
Disable the blob storage auditing policy of an Azure Synapse Analytics SQL pool named ContosoSqlPool through pipeline.
Example 10
Set-AzSynapseSqlPoolAuditSetting -WorkspaceName ContosoWorkspace -Name ContosoSqlPool -LogAnalyticsTargetState Enabled -WorkspaceResourceId "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/insights-integration/providers/Microsoft.OperationalInsights/workspaces/myworkspace" -BlobStorageTargetState Disabled
Disable sending audit records of an Azure Synapse Analytics SQL Pool to blob storage, and enable sending them to log analytics.
Example 11
Set-AzSynapseSqlPoolAuditSetting -WorkspaceName ContosoWorkspace -Name ContosoSqlPool -BlobStorageTargetState Enabled -StorageAccountResourceId "/subscriptions/00000000-0000-0000-0000-000000000000/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"
Enable sending audit records of an Azure Synapse Analytics SQL Pool to blob storage, event hub and log analytics.
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://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: | |
| 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 |
| 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 |
-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
This Cmdlet does not return an object by default. If this switch is specified, it returns true if successful.
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
Resource group name.
Parameter properties
| Type: | String |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
SqlPoolParameterSet
| Position: | 0 |
| Mandatory: | False |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | False |
-ResourceId
Resource identifier of Synapse SQL Pool.
Parameter properties
| Type: | String |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
SqlPoolResourceIdParameterSet
| Position: | Named |
| 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.
Parameter properties
| Type: | |
| 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 |
-SqlPoolName
Name of Synapse SQL pool.
Parameter properties
| Type: | String |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
| Aliases: | Name |
Parameter sets
SqlPoolParameterSet
| Position: | Named |
| Mandatory: | True |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | False |
SqlPoolParentObjectParameterSet
| Position: | Named |
| Mandatory: | True |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | False |
-SqlPoolObject
SQL pool input object, usually passed through the pipeline.
Parameter properties
| Type: | PSSynapseSqlPool |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
| Aliases: | InputObject |
Parameter sets
SqlPoolObjectParameterSet
| Position: | Named |
| Mandatory: | True |
| Value from pipeline: | True |
| 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 |
-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 |
-WorkspaceName
Name of Synapse workspace.
Parameter properties
| Type: | String |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
SqlPoolParameterSet
| Position: | 1 |
| Mandatory: | True |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | False |
-WorkspaceObject
workspace input object, usually passed through the pipeline.
Parameter properties
| Type: | PSSynapseWorkspace |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
SqlPoolParentObjectParameterSet
| Position: | Named |
| Mandatory: | True |
| Value from pipeline: | True |
| 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.
