Building a fully multitenant system using Microsoft Identity Framework and SQL Row Level Security

This post was written a while ago, but I’ve never published it.

In this post, I show an example of using SQL row level security to isolate data among different organizations in a same database using row level security. In that post, I show an example of reading a value in the database session context as part of the filtering logic. In this post, I show an example of how to preset a value in the database session context from ASP.NET core application, and also how to authenticate users from multiple tenants using Microsoft Identity Framework.

Making authentication multi-tenant

Making the authentication multi-tenant meaning your app allows users from multiple azure ad tenants to authenticate. When registering an application in azure, you have several options under Supported account types, as shown in the below screenshot. For multitenant, you must choose either “Accounts in any organizational directory”, or “Accounts in any organizational directory and personal Microsoft accounts”. The screenshot below shows an example choice I make when registering the web app:

Provisioning applications

Before a user from a tenant other than the tenant where the app registration resides can authenticate to use your application, your web app and API service principals must be created in the user’s tenant. The process of creating the service principals in the user’s tenant is called provisioning. From what I understand, there are a few ways to provision the applications.

• Use PowerShell script.
• Use prompt = admin_consent which tells AAD to show the admin consent flow.
• Use prompt = consent if a non-admin user can consent to allow the apps to be created in the user’s tenant.
• Use the admin consent endpoint.

In my experience, I have the most luck of provisioning the applications using PowerShell script.

No matter what I do, I am not able to provision both the API and web app into the user’s tenant. The only way I could get it to work is by using the PowerShell script. Below shows the screenshot of what the permission looks like. Notice that the list of permission is missing the one for the web API.

In the app, I get the following error:

An unhandled exception occurred while processing the request.

OpenIdConnectProtocolException: Message contains error: ‘invalid_client’, error_description: ‘AADSTS650051: The application needs access to a service that your organization testorg010189 has not subscribed to. Please contact your administrator to review the configuration of your service subscriptions.

In the user’s tenant, I only see Survey application got provisioned:

For the client to be successfully consented into a customer’s tenant, all resources to which it requests permissions must already exist in the customer’s tenant. If this condition isn’t met, Azure AD returns an error that the resource must be added first

Build apps that sign in Azure AD users – Microsoft Entra | Microsoft Docs

Service Principal vs Application Object

An application object is a unique identifier representing the instance of the application in a tenant which hosts the application (the application’s home tenant).

A service principal is created in every tenant where the app is used. The application object acts as a blueprint to create service principals.

Validating tokens from multiple tenants

In the docs, Microsoft suggests to not validate the issuer’s id for multitenant app since a user can sign in from any tenant. However, what I have found is that you don’t need to explicitly disable the issuer validation, like below:

  services.Configure<JwtBearerOptions>(JwtBearerDefaults.AuthenticationScheme, configureOptions =>
        {
            configureOptions.TokenValidationParameters.ValidateIssuer = false;
            configureOptions.TokenValidationParameters.IssuerValidator = null;
        }

Preset id of authenticated user in the database context

In the previous post, I gave an example of looking up the id of the authenticated user in the database session context. If you log into the database directly, it’s easy to call the sp_set_session_context store procedure to set a value in the session. However, the user does not authenticate directly against the database. Rather, the user logs into your web application. As such, we need a way to manipulate the database session from the web application. More specifically, because the web application goes through the API for data, we need a way to update the database session context via the web API.

One way is to make a call to the database to set the session context on every successful authentication. We can leverage the JWT bearer event. For instance, we can call the sp_set_session_context after validating the access token, like below:

 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);
            sqlCommand.Parameters.Add(keyParameter);
            sqlCommand.Parameters.Add(valueParameter);
            await sqlCommand.ExecuteNonQueryAsync();
        }

Errors you may experience

invalid_client – AADSTS650052: The app needs access to a service (\”api://tenantA/myapi\”) that your organization (tenant B) has not subscribed to or enabled. Contact your IT Admin to review the configuration of your service subscriptions

For this error, need to add the client id of the web app to the Authorized client applications list of the web API.

‘invalid_client’, error_description: ‘AADSTS650051: The application needs access to a service that your organization Contoso has not subscribed to. Please contact your administrator to review the configuration of your service subscriptions

References

Multitenancy and identity management – Azure Architecture Center | Microsoft Docs

Managing incremental consent and conditional access · AzureAD/microsoft-identity-web Wiki · GitHub

Build apps that sign in Azure AD users – Microsoft Entra | Microsoft Docs

Architecting multitenant solutions on Azure – Azure Architecture Center | Microsoft Docs

Apps & service principals in Azure AD – Microsoft Entra | Microsoft Docs