User Managed Identity support for Auditing Azure SQL Database is in Public Preview Now.
Published Feb 13 2023 05:49 AM 4,996 Views
Microsoft

We are happy to announce that User Managed Identity support for Auditing Azure SQL Database is in Public Preview now.  

Auditing for Azure SQL database has started supporting User Managed Identity. Auditing can be configured to Storage account using two authentication methods, managed identity, and storage access keys. For managed identity you can use system managed identity or user managed identity. To know more about UMI in azure refer here.

 

To configure writing audit logs to a storage account, select Storage when you get to the Auditing section. Select the Azure storage account where logs will be saved, you can use two storage authentication types i.e., managed identity and storage access keys.  

For managed identity, we support system and user managed identity.  

By default, it picks primary user identity assigned to the server, and if there is no user identity, it will create system assigned identity and use it for authentication.  

select the retention period by opening Advanced properties. Then click Save. Logs older than the retention period is deleted.  

 

To configure Auditing using User managed Identity, follow the below steps.    

 

Portal:  

Create a user managed identity and assign it to the server (User Managed identity in Azure AD for Azure SQL - Azure SQL Database & Azure SQL Managed Instance | ....) 
 

Review the identity blade for your Azure database, you can see there is one primary identity configured. 

 

SravaniSaluru_0-1676295536886.png

 

 From Portal, go to auditing blade and select managed identity while enabling audit.  

 

SravaniSaluru_1-1676295536889.png

 

 

 

 

SravaniSaluru_2-1676295536894.png

 

 

 

PowerShell, CLI, Rest API and ARM:  

 

Go to the desired storage account where auditing needs to send logs to and assign the 'Storage Blob Data Contributor' RBAC to the user managed identity previously assigned to the server.  This needs to be done before auditing is being set up using PowerShell, CLI (Command Line Interface), Rest API (Application Programming Interfaces), ARM (Azure Resource Manager) templates). 

Assign Azure roles using the Azure portal - Azure RBAC | Microsoft Docs    

 

For portal, the role assignment is done automatically. 

SravaniSaluru_3-1676295536901.png

 

 

 

REST API: 

 
For primary managed Identity to be used, you need to skip passing parameter "storageAccountAccessKey" in the request.  
Server Auditing Settings - Create Or Update - REST API (Azure SQL Database) | Microsoft Learn 

 

CLI:  

You need to pass --storage-key as empty string to use primary managed identity assigned to the server to enable auditing, please see the below 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 ""  

 

PowerShell:  

Pass UseIdentity as True for primary managed identity assigned to the server to be picked up. Please see the below command: 

Set-AzSqlServerAudit -ResourceGroupName "sampleresourcegroup" -ServerName "sampleauditingtestserver" -BlobStorageTargetState Enabled -StorageAccountResourceId "/subscriptions/857355f0-57d5-4754-b91a-0393dbff9afc/resourcegroups/sampleresourcegroup/providers/Microsoft.Storage/storageAccounts/auditingteststorageacc" -UseIdentity True 

 

UMI gives User 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 SMI, which identity is uniquely defined per server, and assigned by the system. 

 

For more information about UMI, please refer to User-assigned managed identity (UMI) in Azure AD for Azure SQL - Microsoft Community Hub 

Co-Authors
Version history
Last update:
‎Feb 13 2023 05:51 AM
Updated by: