Multi-Tenancy in ASP.NET Core and SQL Server
On this page
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.
| Model | Isolation | Cost and operations |
|---|---|---|
| Shared database, shared schema | Logical, relies on filters and policies | Cheapest, simplest to run and migrate |
| Schema per tenant | Stronger logical separation | Migrations run per schema, more objects |
| Database per tenant | Strongest, per-tenant backup and keys | Most expensive, needs automation, elastic pools help |
| Query filter | Adds the tenant predicate to every EF query | Bypassable with IgnoreQueryFilters or raw SQL |
| Row-level security | Database enforces filter and block predicates | Second barrier that code cannot skip |
| Per-tenant limits | Rate, quota, timeout by tenant | Prevents 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.
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);
}
}-- 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.[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.
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.
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.