Building multitenant application — Part 2: Storing value into database session context from ASP.NET core web API

In the previous post about row level security in SQL server, I gave an example of reading a value from the database session context to feed into the security function for filtering data. In this post, I show examples of calling the sp_set_session_context store procedure from an ASP.NET core web API to store a value into the session context.

If a user logs into an SQL database directly, it’s straight forward to call the sp_set_session_context store procedure to set a value in the session. However, an end user typically does not authenticate directly against the database. Rather, the user logs into a web application which goes through an API for data. As such, we need a way to update the database session context via the web API.

In the application I’m working on, a user needs to authenticate before accessing any feature. Below example shows how I make a call to the database to set the session context on every successful authentication by hooking into the OnTokenValidated event.

services.Configure<JwtBearerOptions>(JwtBearerDefaults.AuthenticationScheme, configureOptions =>
        {
            configureOptions.Events.OnTokenValidated = OnTokenValidated;
        });

 private static async Task OnTokenValidated(TokenValidatedContext ctx)
    {
        if (ctx.Principal is null)
        {
            throw new InvalidOperationException("User claims should not be null.")…