Write audit to a storage account behind VNet and firewall

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:

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

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

5. 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:

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

• Create or Update Database Auditing Policy (Set-AzSqlDatabaseAudit)
• Create or Update Server Auditing Policy (Set-AzSqlServerAudit)

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:

• Deploy an Azure SQL Server with Auditing enabled to write audit logs to a blob storage

Next steps

• Use PowerShell to create a virtual network service endpoint, and then a virtual network rule for Azure SQL Database.
• Virtual Network Rules: Operations with REST APIs
• Use virtual network service endpoints and rules for servers