How to Log Actual Application Admin Users in Azure SQL Audit Logs Instead of the Authenticated Entra ID Admin

Question

Hey everyone, I hope you’re all doing well.

I’m running into an issue related to Azure SQL audit logs, and I’m a bit confused about the best way to approach it.

The problem is that an Azure SQL Server has one Entra ID admin user, and we use that user to authenticate and access the database. As a result, every entry in the audit logs shows that Entra admin user as the one who ran the query, regardless of who actually initiated the action from the application.

I’m using this SQL Server from my App Service backend. For example, let’s say my web app has two admins: Admin A and Admin B. Both can delete or update data through the app, but in the SQL audit logs, the Entra admin user (whose credentials are fetched from Key Vault and used by the backend) always appears as the user who executed the query.

This kind of defeats the purpose of auditing, because ideally I want to know which admin in my web app performed a given action.

To solve this, my first thought was to create a separate database user for each admin and somehow execute queries as those users from the backend. However, I learned that in the audit logs, the user that authenticated to the SQL Server is what appears, not the logical app user, so this doesn’t fully solve the problem.

After that, I explored two possible approaches:

1. Maintain a pool of database connections using multiple users (for example, one for customer traffic, one for Admin A, one for Admin B, etc.). Then, based on the type of request coming from the frontend, select the appropriate connection. However, I’m using Sequelize, and maintaining multiple connection pools like this seems complex and painful. I’m also not convinced this is a good or scalable solution.
2. Separate backend instances for customers and admins. -> Use one service account for customer-related queries. -> Use a single service account for all admin-related queries. -> Additionally, implement a custom logging mechanism at the application level to record which specific admin performed an action, and store those logs in a storage account or the database itself.

This is the first time I’ve encountered this kind of issue, and I’m not sure where the correct solution should live:

-> Should this be solved at the application level?

-> At the database/server level?

-> Or am I completely missing a better or more standard solution?

Any guidance or best practices around this would be greatly appreciated. Thanks in advance!

Answer 1

To log actual application admin users in Azure SQL audit logs instead of the authenticated Entra ID admin, you can consider the following approaches:

1. Custom Application-Level Logging: Implement a logging mechanism within your application that records which specific admin performed an action. This can be done by capturing the admin's identity at the application level and storing this information in a separate log or database table. This way, even if the audit logs show the Entra ID admin, you will have a record of which admin initiated the action.
2. Use of Separate Database Users: While you mentioned that using separate database users for each admin might not fully solve the problem due to how Azure SQL logs the authenticated user, it could still be beneficial to create distinct database users for different roles (e.g., Admin A and Admin B). You can then implement a mechanism to switch users based on the action being performed. However, this approach may require more complex connection management.
3. Service Accounts: As you suggested, using different service accounts for customer-related queries and admin-related queries can help. This way, you can have one service account for general operations and another for admin tasks, which can help in segregating logs based on the type of operation.
4. Audit Configuration: Ensure that your audit logging is configured correctly to capture the necessary details. While Azure SQL does log the authenticated user, you might want to explore additional logging features or settings that could provide more granular insights.

Ultimately, the best solution may involve a combination of these strategies. Implementing application-level logging is crucial to ensure you can track actions performed by individual admins, while also considering how to manage database connections effectively.

---

References:

• Microsoft Entra authentication for Azure SQL
• Best practices for securing PaaS databases in Azure

Answer 2

From an SQL Server point of view, the best is if each user authentications with his or her login. Then all your auditing problems are gone. I don't work on the application layer at all, so I cannot say how feasible this solution is on the application side. Then again, if I understood this correctly, you only need to audit admins, and in that case, you would only need individual logins for the admins.

There are a couple of other options. One of them is session context. When you connect, you issue

EXEC sp_session_context 'REALUSER', 'John Doe', 'read_only'

Then you can retrieve the value with SELECT session_context(N'REALUSER'). This works well if you are rolling your own auditing as you can tailor defaults etc to use session_context. However, as I understand it, you are using the built-in logging in Azure, and the built-in logging has no nothing about session_context. (The name REALUSER is nothing magic. It is just a name and you could use MYUSER, ADMINNAME or whatever you fancy.)

Another option is to create SQL database users for the admin and then impersonate when you connect:

EXECUTE AS USER = 'RealUser' WITH COOKIE INTO @cookie

The Azure Auditing should now pick up this user. You need to capture the value of that @cookie variable, because before you disconnect, you need to issue:

REVERT WITH COOKIE = @cookie

I'm discussing these alternatives a little more here: https://www.sommarskog.se/grantperm.html#applogin. Reviewing what I have written, I see that there at the time there was a known issue with a memory leak in the so-called TokenAndPermUserStore. That was a couple of years back, so that may have been fixed, but my gut feeling that it is not. But if this is only for admin users, you may not suffer too much this problem.

I should clarify that the reason for this 'read_only' and @cookie is that you want to prevent that the user is able to revert the session context or impersonation, in case the user is able to submit direct SQL commands, for instance through SQL injection.