SQL Auditing support for OBO access request from middle tier applications in Microsoft Entra

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.

The on-behalf-of (OBO) flow describes the scenario of a web API using an identity other than its own to call another web API. Referred to as delegation in OAuth, the intent is to pass a user's identity and permissions through the request chain. 


For the middle-tier service to make authenticated requests to the downstream service, it needs to secure an access token from the Microsoft identity platform. It only uses delegated scopes and not application roles. Roles remain attached to the principal (the user) and never to the application operating on the user's behalf. This occurs to prevent the user gaining permission to resources they should not have access to. 





 To get more details about OBO, you can refer https://learn.microsoft.com/azure/active-directory/develop/v2-oauth2-on-behalf-of-flow. 


OBO support for Azure SQL Database and Azure SQL MI (Azure SQL Managed Instance) has been available for quite some time now. It is important to audit the connections coming through OBO access to Azure SQL Database and capture the middle tier applications in audit logs. This is required for security and compliance requirements. The good news is that now the middle tier application details are available in audit logs for you to access.  


We have added a new column in audit logs, obo_middle_tier_app_id , this is the application ID of the middle tier application which connects to SQL Database using OBO access. For audit log fields refer sys.fn_get_audit_file (Transact-SQL) - SQL Server | Microsoft Learn 


Here is the sample of audit log with obo middle tier app id published in logs.  




To retrieve the Microsoft Entra application name, you can use the following PowerShell command.


Get-AzureADApplication -Filter "AppId eq '421599eb-eed7-4988-9b31-02b43a4d37b8'" 


ObjectId                             AppId                                DisplayName      

--------                             -----                                ----------- 

ed192e92-84d4-4baf-997d-1e190a81f28e 421599eb-eed7-4988-9b31-02b43a4d37b8 MyNewApp 


With this it is now possible to determine the middle tier application details from the audit logs for all OBO related access.  

Leave a Reply

Your email address will not be published. Required fields are marked *


This site uses Akismet to reduce spam. Learn how your comment data is processed.