Catalog My learning Blog
Full-Stack Developer Course: React, .NET and SQL Server · Phase 7 · Lesson 68 · 7 min read

Multi-Tenancy in ASP.NET Core and SQL Server

On this page
  1. Learning Objective
  2. Concept Explanation
  3. Worked Example: Tenant from Claims, Query Filters, Write Checks and Row-Level Security
  4. Real-World Examples
  5. Review Questions & Answers

Learning Objective

Compare multi-tenant data isolation models, resolve the tenant from the authenticated identity instead of client input, enforce isolation with EF Core query filters and write checks, add SQL Server row-level security as a second barrier, and test for cross-tenant leakage.

Concept Explanation

A multi-tenant application serves many customers, called tenants, from one deployment, and the promise to each of them is that their data is invisible to the others. That promise is the single most important security property of a software-as-a-service product, and leaks between tenants are among its most damaging failures. There are three main isolation models for the data. Shared database with a shared schema stores every tenant's rows in the same tables, distinguished by a TenantId column: it is the cheapest and simplest to operate, and the most dependent on discipline. Schema per tenant gives each tenant their own tables in one database. Database per tenant gives each their own database, the strongest isolation and easiest per-tenant backup and restore, at higher cost and operational complexity, often mitigated with Azure SQL elastic pools.

The choice depends on the customers. Many small tenants with modest compliance needs suit the shared schema. A few large enterprise customers who demand isolation, dedicated encryption keys, or data residency suit database per tenant. Some products offer both tiers. Whatever you choose, the first rule is that the tenant identity must come from a trusted source. Derive it from the authenticated user's token or session claims, established at sign-in, or from a subdomain that is cross-checked against the user's memberships. Never accept a tenant identifier from a request header or body and simply believe it, because then any user can read any tenant's data by changing a value, a textbook object-level authorization failure at scale.

In the shared schema model, enforce isolation in layers so that no single mistake causes a leak. First, EF Core global query filters add a WHERE TenantId equals the current tenant to every query on tenant-owned entities, so individual queries cannot forget it. Second, a SaveChanges interceptor sets TenantId on new rows from the current tenant and rejects any attempt to write a row for a different tenant. Third, and most robust, SQL Server row-level security applies a filter and a block predicate inside the database, driven by a value the application sets in the session context for each connection, so even raw SQL, a forgotten filter, or IgnoreQueryFilters cannot cross the boundary. Each layer covers the failure of the one before it.

Isolation extends beyond tables. Cache keys must include the tenant, as Day 67 showed. Background jobs and message consumers must set the tenant context from their payload before touching data. File storage paths should be prefixed by tenant, with access checked, and search indexes need a tenant filter. Then there are noisy neighbors: one tenant's heavy usage should not starve the others, so apply rate limits, quotas, and query timeouts per tenant. Finally, test the boundary directly, with two tenants and a suite that tries to read, update, and delete across it through every endpoint, because the guarantee is only as good as the evidence you can show.

ModelIsolationCost and operations
Shared database, shared schemaLogical, relies on filters and policiesCheapest, simplest to run and migrate
Schema per tenantStronger logical separationMigrations run per schema, more objects
Database per tenantStrongest, per-tenant backup and keysMost expensive, needs automation, elastic pools help
Query filterAdds the tenant predicate to every EF queryBypassable with IgnoreQueryFilters or raw SQL
Row-level securityDatabase enforces filter and block predicatesSecond barrier that code cannot skip
Per-tenant limitsRate, quota, timeout by tenantPrevents noisy neighbor problems

Worked Example: Tenant from Claims, Query Filters, Write Checks and Row-Level Security

The code resolves the tenant from the claims, applies EF filters, guards writes, sets the SQL session context, and defines an RLS policy. A test verifies isolation.

C#
public interface ITenantOwned { Guid TenantId { get; set; } }

public sealed class CurrentTenant(IHttpContextAccessor http) : ICurrentTenant
{
    private Guid? _override;                                            // for jobs and consumers, set explicitly
    public void Set(Guid id) => _override = id;
    public Guid Id => _override ?? Guid.Parse(http.HttpContext!.User.FindFirstValue("tid")
                        ?? throw new UnauthorizedAccessException("No tenant on the identity."));   // claim, never a header
}

public class AppDb(DbContextOptions<AppDb> options, ICurrentTenant tenant) : DbContext(options)
{
    protected override void OnModelCreating(ModelBuilder mb)
    {
        foreach (var type in mb.Model.GetEntityTypes().Where(t => typeof(ITenantOwned).IsAssignableFrom(t.ClrType)))
        {
            // Filter 1: every query gets WHERE TenantId = @tenant.
            var p = Expression.Parameter(type.ClrType, "e");
            var body = Expression.Equal(Expression.Property(p, nameof(ITenantOwned.TenantId)),
                                        Expression.Property(Expression.Constant(this), nameof(CurrentTenantId)));
            mb.Entity(type.ClrType).HasQueryFilter(Expression.Lambda(body, p));
        }
    }
    public Guid CurrentTenantId => tenant.Id;
}

// Filter 2: writes are stamped with the tenant, and a mismatch is a hard failure.
public sealed class TenantWriteGuard(ICurrentTenant tenant) : SaveChangesInterceptor
{
    public override ValueTask<InterceptionResult<int>> SavingChangesAsync(DbContextEventData d, InterceptionResult<int> r, CancellationToken ct = default)
    {
        foreach (var e in d.Context!.ChangeTracker.Entries<ITenantOwned>())
        {
            if (e.State == EntityState.Added) e.Entity.TenantId = tenant.Id;
            else if (e.State is EntityState.Modified or EntityState.Deleted && e.Entity.TenantId != tenant.Id)
                throw new InvalidOperationException("Attempted cross-tenant write.");
        }
        return base.SavingChangesAsync(d, r, ct);
    }
}

// Filter 3: tell SQL Server who the tenant is on every connection.
public sealed class TenantSessionContext(ICurrentTenant tenant) : DbConnectionInterceptor
{
    public override async Task ConnectionOpenedAsync(DbConnection c, ConnectionEndEventData e, CancellationToken ct = default)
    {
        await using var cmd = c.CreateCommand();
        cmd.CommandText = "EXEC sp_set_session_context @key = N'TenantId', @value = @id, @read_only = 1;";
        var p = cmd.CreateParameter(); p.ParameterName = "@id"; p.Value = tenant.Id; cmd.Parameters.Add(p);
        await cmd.ExecuteNonQueryAsync(ct);
    }
}
SQL
-- Row-level security: the database refuses to show or accept rows for another tenant, even for raw SQL.
CREATE SCHEMA security;
GO
CREATE FUNCTION security.TenantPredicate(@TenantId UNIQUEIDENTIFIER)
RETURNS TABLE WITH SCHEMABINDING AS
    RETURN SELECT 1 AS allowed WHERE @TenantId = CAST(SESSION_CONTEXT(N'TenantId') AS UNIQUEIDENTIFIER);
GO
CREATE SECURITY POLICY security.TenantPolicy
    ADD FILTER PREDICATE security.TenantPredicate(TenantId) ON dbo.Projects,
    ADD BLOCK  PREDICATE security.TenantPredicate(TenantId) ON dbo.Projects AFTER INSERT,
    ADD BLOCK  PREDICATE security.TenantPredicate(TenantId) ON dbo.Projects AFTER UPDATE
    WITH (STATE = ON);
-- Repeat for every tenant-owned table. The application login must not be db_owner, or it could disable the policy.
C#
[Fact]
public async Task Tenant_a_can_never_see_or_change_tenant_b()
{
    var b = await SeedProjectAsync(TenantB);
    var clientA = ClientFor(TenantA);

    Assert.Equal(HttpStatusCode.NotFound, (await clientA.GetAsync($"/api/projects/{b.Id}")).StatusCode);
    Assert.Equal(HttpStatusCode.NotFound, (await clientA.PatchAsJsonAsync($"/api/projects/{b.Id}", new { name = "x" })).StatusCode);
    Assert.DoesNotContain(await ListProjectsAsync(clientA), p => p.Id == b.Id);

    // Defense in depth: even bypassing the EF filter, the database policy hides the row.
    await using var db = DbFor(TenantA);
    Assert.Empty(await db.Projects.IgnoreQueryFilters().Where(p => p.Id == b.Id).ToListAsync());
}

The last assertion is the important one: it proves that removing the application-level filter does not expose data, because the database applies its own. Run the same test for every entity type, and treat a new tenant-owned table without a policy as a failing check.

The tenant comes from the authenticated identity, then EF query filters, a write guard and SQL Server row-level security each independently enforce isolation. Caches, jobs and storage carry the tenant, and tests try to cross the boundary.
The tenant comes from the authenticated identity, then EF query filters, a write guard and SQL Server row-level security each independently enforce isolation. Caches, jobs and storage carry the tenant, and tests try to cross the boundary.

Real-World Examples

  • In August 2021, security researchers at Wiz disclosed ChaosDB, a vulnerability in Azure Cosmos DB's Jupyter notebook feature that let them obtain the primary keys of other customers' accounts, giving read and write access to thousands of tenants' databases. Microsoft fixed it quickly and notified affected customers. It is a reminder that tenant isolation failures can occur even at cloud provider scale and that customers should rely on layers.
  • Microsoft publishes the Wingtip Tickets SaaS sample application showing different Azure SQL multi-tenant patterns, database per tenant with elastic pools, shared database with sharding, and hybrid models, alongside guidance on tenant onboarding, cross-tenant queries, and performance isolation. It is a practical reference for choosing between the models described here.
Key TakeawaysChoose an isolation model based on your customers, and derive the tenant from the authenticated identity, never from a client-supplied value. Layer defenses: EF query filters, a write guard, and SQL Server row-level security driven by session context, extend the tenant scope to caches, jobs, and storage, apply per-tenant quotas, and prove isolation with tests that try to cross the boundary.

Review Questions & Answers

A header or body value is chosen by the caller, so any user could set it to another tenant's identifier and read or modify that tenant's data, which is a direct object-level authorization failure. Deriving the tenant from a claim issued at sign-in means the caller cannot change it, and the server is the only source of the value.

Query filters are application code and can be bypassed by IgnoreQueryFilters, raw SQL, a bulk operation, or a bug. Row-level security is enforced inside the database for every query on the connection, so even those paths cannot return or write another tenant's rows. It is a second, independent barrier that fails safe.

They run without an authenticated web request, so no tenant is set by default and queries could run unscoped. They must take the tenant from the job or message payload and set it in the tenant context before any data access, and message headers or job arguments should always carry it.

One tenant's heavy usage consumes shared resources such as database capacity or CPU and degrades service for others. It is addressed with per-tenant rate limits, quotas, and query timeouts, and for the largest tenants by moving them to dedicated databases or elastic pool allocations.