Write audit to a storage account behind VNet and firewall

  • Article

Applies to: Azure SQL Database Azure Synapse Analytics

Auditing for Azure SQL Database and Azure Synapse Analytics supports writing database events to an Azure Storage account behind a virtual network and firewall.

This article explains two ways to configure Azure SQL Database and Azure storage account for this option. The first uses the Azure portal, the second uses REST.

Background

Azure Virtual Network (VNet) is the fundamental building block for your private network in Azure. VNet enables many types of Azure resources, such as Azure Virtual Machines (VM), to securely communicate with each other, the internet, and on-premises networks. VNet is similar to a traditional network in your own data center, but brings with it additional benefits of Azure infrastructure such as scale, availability, and isolation.

To learn more about the VNet concepts, Best practices and many more, see What is Azure Virtual Network.

To learn more about how to create a virtual network, see Quickstart: Create a virtual network using the Azure portal.

Prerequisites

For audit to write to a storage account behind a VNet or firewall, the following prerequisites are required:

  • A general-purpose v2 storage account. If you have a general-purpose v1 or blob storage account, upgrade to a general-purpose v2 storage account. For more information, see Types of storage accounts.
  • The premium storage with BlockBlobStorage is supported
  • The storage account must be on the same tenant and at the same location as the logical SQL server (it's OK to be on different subscriptions).
  • The Azure Storage account requires Allow trusted Microsoft services to access this storage account. Set this on the Storage Account Firewalls and Virtual networks.
  • You must have Microsoft.Authorization/roleAssignments/write permission on the selected storage account. For more information, see Azure built-in roles.

Note

When Auditing to storage account is already enabled on a server / db, and if the target storage account is moved behind a firewall, we lose write access to the storage account and audit logs stop getting written to it.To make auditing work we have to resave the audit settings from portal.

Configure in Azure portal

Connect to Azure portal with your subscription. Navigate to the resource group and server.

  1. Click on Auditing under the Security heading. Select On.

  2. Select Storage. Select the storage account where logs will be saved. The storage account must comply with the requirements listed in Prerequisites.

  3. Open Storage details

Note

If the selected Storage account is behind VNet, you will see the following message:

You have selected a storage account that is behind a firewall or in a virtual network. Using this storage requires to enable 'Allow trusted Microsoft services to access this storage account' on the storage account and creates a server managed identity with 'storage blob data contributor' RBAC.

If you do not see this message, then storage account is not behind a VNet.

  1. Select the number of days for the retention period. Then click OK. Logs older than the retention period are deleted.

  2. Select Save on your auditing settings.

You have successfully configured audit to write to a storage account behind a VNet or firewall.

Configure with REST commands

As an alternative to using the Azure portal, you can use REST commands to configure audit to write database events on a storage account behind a VNet and Firewall.

The sample scripts in this section require you to update the script before you run them. Replace the following values in the scripts:

Sample value Sample description
<subscriptionId> Azure subscription ID
<resource group> Resource group
<logical SQL Server> Server name
<administrator login> Administrator account
<complex password> Complex password for the administrator account

To configure SQL Audit to write events to a storage account behind a VNet or Firewall:

  1. Register your server with Microsoft Entra ID (formerly Azure Active Directory). Use either PowerShell or REST API.

    PowerShell

    Connect-AzAccount
Select-AzSubscription -SubscriptionId <subscriptionId>
Set-AzSqlServer -ResourceGroupName <your resource group> -ServerName <azure server name> -AssignIdentity

    REST API:

    Sample request

    PUT https://management.azure.com/subscriptions/<subscription ID>/resourceGroups/<resource group>/providers/Microsoft.Sql/servers/<azure server name>?api-version=2015-05-01-preview

    Request body

    {
"identity": {
           "type": "SystemAssigned",
           },
"properties": {
  "fullyQualifiedDomainName": "<azure server name>.database.windows.net",
  "administratorLogin": "<administrator login>",
  "administratorLoginPassword": "<complex password>",
  "version": "12.0",
  "state": "Ready"
  }
}

  2. Assign the Storage Blob Data Contributor role to the server hosting the database that you registered with Microsoft Entra ID in the previous step.

    For detailed steps, see Assign Azure roles using the Azure portal.

    Note

    Only members with Owner privilege can perform this step. For various Azure built-in roles, refer to Azure built-in roles.

  3. Configure the server's blob auditing policy, without specifying a storageAccountAccessKey:

    Sample request

      PUT https://management.azure.com/subscriptions/<subscription ID>/resourceGroups/<resource group>/providers/Microsoft.Sql/servers/<azure server name>/auditingSettings/default?api-version=2017-03-01-preview

    Request body

    {
  "properties": {
   "state": "Enabled",
   "storageEndpoint": "https://<storage account>.blob.core.windows.net"
  }
}

Using Azure PowerShell

Using Azure Resource Manager template

You can configure auditing to write database events on a storage account behind virtual network and firewall using Azure Resource Manager template, as shown in the following example:

Important

In order to use storage account behind virtual network and firewall, you need to set isStorageBehindVnet parameter to true

Note

The linked sample is on an external public repository and is provided 'as is', without warranty, and are not supported under any Microsoft support program/service.

Next steps