James Still

Introduction

My father was a cabinet maker. A customer would hire him to build cabinets for their kitchen. And every kitchen was different. Some had several windows, others just one over the sink, and yet others no windows at all. Some ceilings were higher than others. And so on. He would measure each kitchen and build custom cabinets that fit the space. Obviously this was expensive and only customers with some serious money could afford to hire him. Now it’s common to build generic cabinets that are one-size-fits-all. A cabinet maker can crank these out by the hundreds and installers can mount them in the most standard-size kitchens. If there is a need, custom cabinets can still be built and installed alongside the generic cabinets.

Generic Building Blocks

For years and years our bread and butter has been like that of the custom cabinet maker. Each app we wrote was lovingly hand-crafted just for a particular customer. We gave no thought to reuse. A few years ago Ben Busse wrote an excellent article,  and noted that  “building one-off APIs and a custom backend for each and every new application is untenable.” Over time the dozens or hundreds of different APIs in an enterprise become a maintenance nightmare. Data governance? Security? Documentation? Discoverability? It can get pretty crazy, pretty fast.  Busse makes the case for building reusable (generic) REST APIs for common data.

In my shop we’ve been having this same discussion. It seems that every time a new app comes along there’s always the need to store people with first, middle, and last names. You just know that someone is going to write a PERSON.SQL script. Does the next app six months later have the same requirement? Let’s create yet another PERSON table! Maybe we copy and paste from another team’s project. Maybe we start from scratch. Who knows.

What if there were a better way? What if you took generic elements like PERSON that are common across business domains and encapsulate this data in a reusable API? Then development teams could leverage the reusable APIs and focus on creating new APIs that are unique to their business domains. It is this reusability (multi-tenancy) that I want to address.

Multi-Tenancy

Broadly speaking, multi-tenancy can be of two types: logical isolation and physical isolation. It is the latter in which I’m interested. Suppose each development team wants to have its own copy of the database. Or suppose the customer requires it. You would have this architecture (see Multi-tenant SaaS patterns):

The catalog is a data store of all tenants that holds information as to which database the tenant is assigned. It could be a SQL Azure instance, or Azure Table Storage, or even an appsettings.json file.  If all tenant databases are on the same SQL Azure server in the same resource group you could group them into an elastic pool. The article I referenced above has guidance on when you might want to do that.

Implementation

The complete source code for this project is available on GitHub in this repo: https://github.com/jamesstill/MultiTenantWidgetApi.

I’ll use Visual Studio 2019 Community Edition with the latest (as of this writing) .NET Core SDK v2.2.401. This will probably be the last time I use Core 2.2 in a web project since 3.0 will be out soon!

I’ll begin by creating a new ASP.NET Core 2.2 API project. In addition to the out-of-the-box NuGet packages I’m going to add EF Core:

• Microsoft.EntityFrameworkCore.InMemory
• Microsoft.EntityFrameworkCore.SqlServer
• Microsoft.EntityFrameworkCore.Tools

In a real production app I would want to use HMAC auth or an OpenID Connect (OIDC) layer such as IdentityServer4, Auth0, or Okta in my API to authenticate JSON web tokens. But to keep things really simple I’m going to use Basic Auth with this NuGet package I wrote:

• SquareWidget.BasicAuth.Core

My strategy here is to use the authenticated security principal (User.Identity.Name) as the tenant ID in the app. So obviously this only works if tenant names are unique. Typically, the API owner issues the credential/tenant name to the caller so this should be no problem.

Configuration

I’m going to add a default (base) connection string to my appsettings.json file:

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=tcp:sample.database.windows.net,1433;Database=sample;..."
  },
  "Logging": {
    "LogLevel": {
      "Default": "Warning"
    }
  },
  "AllowedHosts": "*"
}

And in my Startup.cs I want to inject IHttpContextAccessor into my controller so that I can access the user principal. I’m also setting up basic auth just to use a simple authentication scheme for this example. As I said earlier, in a real production app I would want to use HMAC or JWT with claims. Here’s my ConfigureServices method in Startup.cs:

public void ConfigureServices(IServiceCollection services)
{
    services.AddMvc()
        .SetCompatibilityVersion(CompatibilityVersion.Version_2_2);
    services.AddHttpContextAccessor();

    // basic auth as an example authentication scheme
    services
        .AddAuthentication(BasicAuthenticationDefaults.AuthenticationScheme)
        .AddBasicAuthentication<BasicAuthenticationService>();
}

I also need to add authentication to the HTTP request pipeline:

public void Configure(IApplicationBuilder app, IHostingEnvironment env)
{
    // omitted for brevity

    app.UseAuthentication();    // <---------
    app.UseHttpsRedirection();
    app.UseMvc();
}

And of course my controller is decorated with the [Authorize] attribute. This completes the configuration needed for the API.

Catalog Service

In the diagram above tenant information is stored in a catalog data store. I’m going to hard-code that store in a CatalogService class. In a production app you would call out to a database, table storage, or another API. Here’s the class:

public class CatalogService
{
    private readonly TenantSettings _tenantSettings;

    public CatalogService(TenantSettings tenantSettings)
    {
        _tenantSettings = tenantSettings ??
            throw new ArgumentNullException(nameof(tenantSettings));
    }

    public async Task<string> GetConnectionString()
    {
        // fetch the tenant from the catalog
        var tenant = await GetTenantFromCatalog(_tenantSettings.TenantId);
        if (tenant == null)
        {
            throw new Exception("Tenant not found in catalog!");
        }

        var builder = new SqlConnectionStringBuilder(_tenantSettings.DefaultConnectionString)
        {
            DataSource = tenant.DatabaseServerName,
            InitialCatalog = tenant.DatabaseName
        };

        return builder.ConnectionString;
    }

    /// <summary>
    /// Stub to simulate a call out to an API, Azure Table Storage, DB, 
    /// or the location of the catalog you implement in your project.
    /// </summary>
    /// <param name="tenantId"></param>
    /// <returns></returns>
    private async Task<Tenant> GetTenantFromCatalog(string tenantId)
    {
        var list = new List<Tenant>()
        {
            new Tenant {
                TenantId = "tenant1",
                DatabaseServerName = "tcp:tenant1.database.windows.net,1433",
                DatabaseName = "tenant1db"
            },
            new Tenant {
                TenantId = "tenant2",
                DatabaseServerName = "tcp:tenant2.database.windows.net,1433",
                DatabaseName = "tenant2db"
            },
            new Tenant {
                TenantId = "tenant3",
                DatabaseServerName = "tcp:tenant3.database.windows.net,1433",
                DatabaseName = "tenant3db"
            }
        };

        await Task.CompletedTask;
        return list
            .Where(t => t.TenantId == tenantId)
            .SingleOrDefault() ?? new Tenant();
    }
}

The main logic in the service is in the GetConnectionString method. Once we have the authenticated principal name (the tenantId) we can use a SqlConnectionStringBuilder to transform the default connection string into the tenant’s connection string. The controller can new up a DbContext instance with this connection string:

// build a DbContext for this tenant
var service = new CatalogService(settings);
var cn = service.GetConnectionString().Result;
var optionsBuilder = new DbContextOptionsBuilder<WidgetDbContext>()
    .UseSqlServer(cn);

WidgetDbContext context = new WidgetDbContext(optionsBuilder.Options);

In this way you can achieve multi-tenancy with physical database isolation pretty easily. With the DbContext instantiated, your controller Get method is the same as with any API:

[HttpGet]
[ProducesResponseType(typeof(IEnumerable<Widget>), (int)HttpStatusCode.OK)]
[ProducesResponseType(404)]
[ProducesResponseType(500)]
public async Task<IActionResult> Get()
{
    if (_context == null)
    {
        return StatusCode(500);
    }

    var list = await _context.Widgets
        .AsNoTracking()
        .ToListAsync();

    return Ok(list);
}

Test with Postman

Clone the source code and bring it down to your machine. Then build and run the API. You can test in Postman with basic auth using either “tenant1” or “tenant2” as a username and any password. Here I am authenticating as tenant1:

And after I send the request I get back my list of widgets:

Try it with tenant2 and you’ll get only those widgets for that tenant.

Logical Isolation

Suppose you only need logical isolation? Then all widgets for all tenants could be stored in the same table with an extra TenantId column on the table. You’d want to put an index on TenantId. There would be no CatalogService. And returning all of a tenant’s widgets would be done with an additional WHERE clause:

var list = await _context.Widgets
    .Where(w => w.TenantId == _tenantId) // <-----
    .AsNoTracking()
    .ToListAsync();

If the risk is low, logical isolation is good enough. But I’d be careful if your business depends on tenant’s never accidentally seeing other tenant data. If Salesforce can get this wrong then you probably will too. Physical isolation is worth the extra hassle if you want to be sure that customer data is never co-mingled.