Azure Synapse SQL Pool Extended Auditing Policy

This page shows how to write Terraform and Azure Resource Manager for Synapse SQL Pool Extended Auditing Policy and write them securely.

azurerm_synapse_sql_pool_extended_auditing_policy (Terraform)

The SQL Pool Extended Auditing Policy in Synapse can be configured in Terraform with the resource name azurerm_synapse_sql_pool_extended_auditing_policy. The following sections describe how to use the resource and its parameters.

Example Usage from GitHub

An example could not be found in GitHub.

Review your Terraform file for Azure best practices

Shisho Cloud, our free checker to make sure your Terraform configuration follows best practices, is available (beta).


The following arguments are supported:

  • sql_pool_id - (Required) The ID of the Synapse SQL pool to set the extended auditing policy. Changing this forces a new resource to be created.

  • storage_endpoint - (Optional) The blob storage endpoint (e.g. This blob storage will hold all extended auditing logs.

  • retention_in_days - (Optional) The number of days to retain logs for in the storage account.

  • storage_account_access_key - (Optional) The access key to use for the auditing storage account.

  • storage_account_access_key_is_secondary - (Optional) Is storage_account_access_key value the storage's secondary key?

  • log_monitoring_enabled - (Optional) Enable audit events to Azure Monitor? To enable server audit events to Azure Monitor, please enable its master database audit events to Azure Monitor.

In addition to the Arguments listed above - the following Attributes are exported:

  • id - The ID of the Synapse SQL Pool Extended Auditing Policy.

Explanation in Terraform Registry

Manages a Synapse SQL Pool Extended Auditing Policy.

Tips: Best Practices for The Other Azure Synapse Resources

In addition to the azurerm_synapse_workspace, Azure Synapse has the other resources that should be configured for security reasons. Please check some examples of those resources and precautions.



Ensure to enable the managed virtual network

It is better to enable the managed virtual network, which is disabled as the default.

Review your Azure Synapse settings

In addition to the above, there are other security points you should be aware of making sure that your .tf files are protected in Shisho Cloud.

Microsoft.Synapse/workspaces/sqlPools/extendedAuditingSettings (Azure Resource Manager)

The workspaces/sqlPools/extendedAuditingSettings in Microsoft.Synapse can be configured in Azure Resource Manager with the resource name Microsoft.Synapse/workspaces/sqlPools/extendedAuditingSettings. The following sections describe how to use the resource and its parameters.

Example Usage from GitHub

An example could not be found in GitHub.


  • apiVersion required - string
  • name required - string

    The name of the blob auditing policy.

  • properties required
      • auditActionsAndGroups optional - array

        Specifies the Actions-Groups and Actions to audit.

        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:


        This above combination is also the set that is configured by default when enabling auditing from the Azure portal.

        The supported action groups to audit are (note: choose only specific groups that cover your auditing needs. Using unnecessary groups could lead to very large quantities of audit records):


        These are groups that 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 Database-Level Audit Action Groups.

        For Database auditing policy, specific Actions can also be specified (note that Actions cannot be specified for Server auditing policy). 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::{db_name} and SCHEMA::{schema_name} 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 Database-Level Audit Actions

      • isAzureMonitorTargetEnabled optional - boolean

        Specifies whether audit events are sent to Azure Monitor. In order to send the events to Azure Monitor, specify 'state' as 'Enabled' and 'isAzureMonitorTargetEnabled' as true.

        When using REST API to configure auditing, Diagnostic Settings with 'SQLSecurityAuditEvents' diagnostic logs category on the database should be also created. Note that for server level audit you should use the 'master' database as {databaseName}.

        Diagnostic Settings URI format: PUT{subscriptionId}/resourceGroups/{resourceGroup}/providers/Microsoft.Sql/servers/{serverName}/databases/{databaseName}/providers/microsoft.insights/diagnosticSettings/{settingsName}?api-version=2017-05-01-preview

        For more information, see Diagnostic Settings REST API or Diagnostic Settings PowerShell

      • isStorageSecondaryKeyInUse optional - boolean

        Specifies whether storageAccountAccessKey value is the storage's secondary key.

      • predicateExpression optional - string

        Specifies condition of where clause when creating an audit.

      • queueDelayMs optional - integer

        Specifies the amount of time in milliseconds that can elapse before audit actions are forced to be processed. The default minimum value is 1000 (1 second). The maximum is 2,147,483,647.

      • retentionDays optional - integer

        Specifies the number of days to keep in the audit logs in the storage account.

      • state required - string

        Specifies the state of the policy. If state is Enabled, storageEndpoint or isAzureMonitorTargetEnabled are required.

      • storageAccountAccessKey optional - string

        Specifies the identifier key of the auditing storage account. If state is Enabled and storageEndpoint is specified, not specifying the storageAccountAccessKey will use SQL server system-assigned managed identity to access the storage. Prerequisites for using managed identity authentication:

        1. Assign SQL Server a system-assigned managed identity in Azure Active Directory (AAD).
        2. Grant SQL Server identity access to the storage account by adding 'Storage Blob Data Contributor' RBAC role to the server identity. For more information, see Auditing to storage using Managed Identity authentication
      • storageAccountSubscriptionId optional - string

        Specifies the blob storage subscription Id.

      • storageEndpoint optional - string

        Specifies the blob storage endpoint (e.g. If state is Enabled, storageEndpoint or isAzureMonitorTargetEnabled is required.

  • type required - string

Frequently asked questions

What is Azure Synapse SQL Pool Extended Auditing Policy?

Azure Synapse SQL Pool Extended Auditing Policy is a resource for Synapse of Microsoft Azure. Settings can be wrote in Terraform.