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

Tai Bo
3 min readAug 13, 2022

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.");
var sessionContextRepository = ctx.HttpContext.RequestServices.GetRequiredService<ISessionContextRepository>();
await sessionContextRepository.SetSessionContext("azureAdObjectId", value: ctx.Principal.GetObjectId());

Below shows the relevant codes in the SessionContextRepository class

public async Task SetSessionContext(string key, string value)
var connection = _budgetDbContext.Database.GetDbConnection();
await connection.OpenAsync();
var keyParameter = new SqlParameter("Key", key);
var valueParameter = new SqlParameter("Value", value);
var sqlCommand = new SqlCommand("EXEC sp_set_session_context @key = @Key, @value = @Value", (SqlConnection)connection);
await sqlCommand.ExecuteNonQueryAsync();
Tai Bo

Backend developer in .NET core. I enjoy the outdoor, hanging out with good friends, reading and personal development.