How to get the SUSER_NAME() of the user for auditing fields in Azure SQL

Question

We are migrating from on-prem database into Azure SQL. We have audit fields in the database that grabs the SUSER_NAME() whenever a DML transaction (i.e., INSERT, UPDATE, DELETE - soft-delete) occurs. Since the on-prem database uses the Windows Domain Active Directory and the users login to the database using Integrated Security we can correctly get the current user's name.

However, when we use the managed identity in Azure SQL, the SUSER_NAME() comes down as a concatenation of two guids: the Managed Identity guid and the service container guid. As a result, we are losing the user name of the current user. Is it possible to get the user name with the SQL Function SUSER_NAME() (or some other SQL function) or will this be a code change where we now pass in the actual user name, for the audit field in question, to the call to the Azure SQL database?

Update 1:

Our application is designed to interact with Azure SQL using the following DbContext constructor:

public SomeDbEntities(IConfiguration objConfiguration, AzureAuthenticationInterceptor azureAuthenticationInterceptor) :
    base(new DbContextOptionsBuilder<SomeDbEntities>()
        .UseSqlServer(objConfiguration.GetValue<string>("Azure:ConnectionStrings:SomeDbEntities"))
        .AddInterceptors(azureAuthenticationInterceptor)
        .Options)
{ }

We are using an azureAuthenticationInterceptor in order to deal with a thread blocking issue. The solution for that was provided here: EF Core Connection to Azure SQL with Managed Identity

Update 2:

The audit fields are being set by triggers using the SUSER_NAME() function that log data to a history table.

Answer 1

Since you're connecting to Azure SQL with a Service Prinicpal, you have to pass the AAD user's identity somehow. You can use a custom table, or call sp_set_session_context every time you connect.

Here's a example of a DbContext that sets the end user's identity using sp_set_session_context:

public class Order
{
    public int OrderId { get; set; }
    public int ProductId { get; set; }
    public int Quantity { get; set; }
    public DateTime OrderDate { get; set; }
    public string CreatedBy { get; set; }

}

and

using Microsoft.AspNetCore.Http;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace SessionContextSample.Models
{
    public class OrderContext : DbContext
    {
        public OrderContext(IHttpContextAccessor ctxt, DbContextOptions opts) : base(opts)
        {
            
            //Skip this if running outside of an HttpRequest, as when running Migrations at startup, or in background workers
            if (ctxt.HttpContext != null)
            {
                var userName = ctxt.HttpContext.User.Identity.Name ?? "Unkown";

                //Open the DbConnection.  It will remain open for the lifetime of this web request
                this.Database.GetDbConnection().Open();  
                this.Database.ExecuteSqlRaw("EXEC sys.sp_set_session_context @key = N'user', @value = {0};  ",userName);
            }
        }

        public DbSet<Order> Orders { get; set; }
        
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            base.OnConfiguring(optionsBuilder);


        }
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Order>().Property(o => o.CreatedBy)
                        .HasDefaultValueSql("cast(SESSION_CONTEXT(N'user') as nvarchar(255))")
                        .ValueGeneratedOnAdd()
                        .HasMaxLength(255)
                        .IsRequired();
            base.OnModelCreating(modelBuilder);
        }
    }
}

If you Open() the connection EF will leave it open until the DbContext is Disposed (at the end of your Scope/WebRequest).

Answer 2

I have not researched the "can you tell me how to solve this the way I am already trying to solve it" method. I know you can keep parity with on prem by using lift & shift into an Azure VM. This gives you no cloud native capabilities of Azure SQL Database, but allows you to keep the same audit method. So, if the goal is move to the cloud with minimal changes to our methods, then lift & shift is the easiest, as you will still be running on the server, non-cloud version. You can then modernize and move to Azure SQL. This is not a horrible option if you have to get the apps into the cloud, with their data source, but don't have time to find all of the audit triggers, etc.

Option #2 is to move to the cloud native version of auditing, which is built into the Azure SQL database and can do DML. This is ideal, in many ways, but your existing code will not work. There are plenty of sites on how to set up auditing of DML and DDL. If you have a lot of audit triggers, this will mean setting up a deploy to the cloud strategy that removes them and switches to Azure cloud native approaches for the database.

You appear to want option #3 - cloud native without changing code? If so, I don't know how. It appears @David Browne - Microsoft has offered a way to set things up through C# code. I would still consider modernizing over time and move to cloud native audit approaches, but this could be a good stop gap (unless your data access code is spread all over the place).