Auditing using managed identity

Applies to: Azure SQL Database Azure Synapse Analytics

Auditing for Azure SQL Database can be configured to use a Storage account with two authentication methods:

• Managed Identity
• Storage Access Keys

Managed Identity can be a system-assigned managed identity (SMI) or user-assigned managed identity (UMI).

To configure writing audit logs to a storage account, go to the Azure portal, and select your logical server resource for Azure SQL Database. Select Storage in the Auditing menu. Select the Azure storage account where logs will be saved.

By default, the identity used is the primary user identity assigned to the server. If there's no user identity, the server creates a system-assigned managed identity and uses it for authentication.

Select the retention period by opening the Advanced properties. Then select Save. Logs older than the retention period are deleted.

Note

To set up managed identity-based auditing on Azure Synapse Analytics, see the Configure system-assigned managed identity for Azure Synapse Analytics auditing section later in this article.

User-assigned managed identity

UMI gives users flexibility to create and maintain their own UMI for a given tenant. UMI can be used as server identities for Azure SQL. UMI is managed by the user, compared to a system-assigned managed identity, which identity is uniquely defined per server, and assigned by the system.

For more information about UMI, see Managed identities in Microsoft Entra ID for Azure SQL.

Configure user-assigned managed identity for Azure SQL Database auditing

Before auditing can be set up to send logs to your storage account, the managed identity assigned to the server needs to have the Storage Blob Data Contributor role assignment. This assignment is required if you're configuring auditing using PowerShell, the Azure CLI, REST API, or ARM templates. Role assignment is done automatically when using the Azure portal to configure Auditing, so the below steps are unnecessary if you're configuring Auditing through the Azure portal.

1. Go to the Azure portal.

2. Create a user-assigned managed identity if you haven't already done so. For more information, see creating user assigned managed identity.

3. Go to your storage account that you want to configure for auditing.

4. Select the Access Control (IAM) menu.

5. Select Add > Add role assignment.

6. In the Role tab, search and select Storage Blob Data Contributor. Select Next.

7. In the Members tab, select Managed identity in the Assign access to section, and then Select members. You can select the Managed identity that was created for your server.

8. Select Review + assign.

   

For more information, see Assign Azure roles using portal.

Use the following to configure auditing using user-assigned managed identity:

Portal

1. Go to the Identity menu for your server. Under the User assigned managed identity section, Add the managed identity.

2. You can then select the added managed identity as the Primary identity for your server.

   

3. Go to the Auditing menu for the server. Select Managed Identity as the Storage Authentication Type when configuring the Storage for your server.

The Azure CLI

In order to use a managed identity, pass the --storage-key parameter as an empty string to use the primary managed identity assigned to the server when configuring auditing. Here's a sample command:

az SQL server audit-policy update -g "sampleresourcegroup" -n "sampleauditingtestserver" --state Enabled --bsts Enabled --storage-endpoint https://<storageaccountname>.blob.core.windows.net --storage-key ""

For more information, see az sql server audit-policy.

PowerShell

In order to use a managed identity, pass the UseIdentity parameter as True to use the primary managed identity assigned to the server. Here's a sample command:

Set-AzSqlServerAudit -ResourceGroupName "sampleresourcegroup" -ServerName "sampleauditingtestserver" -BlobStorageTargetState Enabled -StorageAccountResourceId "/subscriptions/<SubscriptionID>/resourcegroups/sampleresourcegroup/providers/Microsoft.Storage/storageAccounts/auditingteststorageacc" -UseIdentity True

For more information, see Set-AzSqlServerAudit.

REST API

In order to use the primary managed identity, skip passing the parameter storageAccountAccessKey in the request:

PUT https://management.azure.com/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.Sql/servers/{serverName}/auditingSettings/default?api-version=2017-03-01-preview

{
  "properties": {
    "state": "Enabled",
    "storageEndpoint": "https://mystorage.blob.core.windows.net"
  }
}

For more information, see Server Auditing Settings - Create Or Update.

Configure system-assigned managed identity for Azure Synapse Analytics auditing

You can't use UMI based authentication to a storage account for auditing. Only system-assigned managed identity (SMI) can be used for Azure Synapse Analytics. For SMI authentication to work, the managed identity must have the Storage Blob Data Contributor role assigned to it, in the storage account's Access Control settings. This role is automatically added if Azure portal is used to configure auditing.

In the Azure portal for Azure Synapse Analytics, there is no option to explicitly choose SAS key or SMI authentication, as is the case for Azure SQL Database.

• If the storage account is behind a VNet or firewall, auditing is automatically configured using SMI authentication.

• If the storage account isn't behind a VNet or firewall, auditing is automatically configured using SAS key based authentication. However, managed identity cannot be used if the storage account isn't behind a VNet or firewall.

To force the use of SMI authentication, regardless of whether the storage account is behind a VNet or firewall, use REST API or PowerShell, as follows:

• If using the REST API, omit the StorageAccountAccessKey field explicitly in the request body.

  For more information, reference:

  • Server Blob Auditing Policies - Create Or Update - REST API (Azure SQL Database)
  • Database Blob Auditing Policies - Create Or Update - REST API (Azure SQL Database

• If using PowerShell, pass the UseIdentity parameter as true.

  For more information, reference:

  • Set-AzSqlServerAudit (Az.Sql)
  • Set-AzSqlDatabaseAudit (Az.Sql)

Next steps

• Auditing overview
• Data Exposed episode: What's New in Azure SQL Auditing
• Auditing for SQL Managed Instance
• Auditing for SQL Server