When building a multi-tenant application, one of the first questions that comes up is simple: how do we make sure one customer cannot see another customer’s data?
At application level, the obvious solution is adding a filter:
var orders = await dbContext.Orders
.Where(x => x.TenantId == currentTenantId)
.ToListAsync();This works, but it depends on every developer remembering to apply the filter correctly.
That was the part I did not like.
In applications where multiple companies share the same database and tables, forgetting one Where() condition can become a data isolation problem. Instead of relying only on application code, I wanted the database to participate in enforcing the boundary.
This is where Row-Level Security became useful.
Note: All code samples in this article are simplified examples created only for demonstration purposes. They are not copied from any actual production project.
The Problem I Was Trying to Solve
Imagine a SaaS application with an Orders table:
Orders
--------------------------------
Id
TenantId
OrderNumber
TotalAmount
CreatedAtTenant A and Tenant B use the same application and the same database.
The application knows which tenant the authenticated user belongs to. Every order also contains a TenantId.
The basic rule is:
Current TenantId == Order.TenantIdSimple enough.
The problem is enforcing this rule everywhere.
EF Core provides global query filters, which are very useful for this scenario. A typical implementation looks something like this:
modelBuilder.Entity<Order>()
.HasQueryFilter(x => x.TenantId == _tenantId);EF Core automatically adds the condition to queries against that entity. Microsoft specifically documents multi-tenancy as one of the common uses for global query filters.
I still don’t consider this equivalent to database Row-Level Security.
For example, EF Core allows query filters to be disabled with IgnoreQueryFilters(). Raw SQL, another application, a reporting process, or somebody accessing the database directly may also operate outside the assumptions made by the EF model.
For stronger isolation, I wanted the rule closer to the data.
Takeaway: EF Core query filters are excellent for automatically scoping application queries, but database RLS provides another enforcement boundary.
Moving the Tenant Boundary into SQL Server
SQL Server supports Row-Level Security using security policies and predicate functions. The database evaluates the policy when rows are accessed rather than requiring every application query to contain the tenant condition.
The architecture I prefer looks roughly like this:
Authenticated User
|
v
Application determines TenantId
|
v
EF Core opens SQL connection
|
v
TenantId stored in SESSION_CONTEXT
|
v
SQL Server RLS policy
|
v
Only matching tenant rows are accessibleThe interesting part is SESSION_CONTEXT.
SQL Server allows an application to associate key-value information with the current database session using sp_set_session_context. The value can later be retrieved using SESSION_CONTEXT(). Microsoft specifically recommends this pattern for middle-tier applications where multiple application users share the same SQL database account.
For example:
EXEC sys.sp_set_session_context
@key = N'TenantId',
@value = '8D56E27A-3A2D-4B8E-9A81-83CFE77C21E4';Now SQL Server knows which tenant this database session represents.
Takeaway: The application still determines the tenant, but SQL Server receives that identity and becomes responsible for enforcing which rows are accessible.
Creating the Security Predicate
The next step is creating a predicate function.
A simplified version could look like this:
CREATE SCHEMA Security;
GO
CREATE FUNCTION Security.fn_TenantAccess
(
@TenantId uniqueidentifier
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
SELECT 1 AS IsAllowed
WHERE @TenantId =
TRY_CAST(
SESSION_CONTEXT(N'TenantId')
AS uniqueidentifier
)
);
GOThe function compares two values:
TenantId stored on the row
vs.
TenantId stored in the SQL sessionIf they match, the row is allowed.
Then I attach the predicate to the table:
CREATE SECURITY POLICY Security.TenantSecurityPolicy
ADD FILTER PREDICATE
Security.fn_TenantAccess(TenantId)
ON dbo.Orders
WITH (STATE = ON);
GOA filter predicate controls which existing rows are visible. SQL Server security policies can also use block predicates to prevent operations that would violate the policy, which is important when inserts and updates must also be protected.
Now something interesting happens.
The application can execute:
SELECT *
FROM dbo.Orders;There is no tenant condition in that query.
But SQL Server still returns only the rows permitted by the security policy.
That is the main reason I like this approach.
Takeaway: Authorization is no longer dependent on remembering WHERE TenantId = ... in every query.
Connecting It to EF Core
Of course, SQL Server does not automatically know which tenant is using the ASP.NET Core application.
EF Core needs to pass that information when opening the database connection.
EF Core provides database interceptors that can observe and modify operations around database connections, commands, transactions, and other operations. Connection interceptors are therefore a convenient place for this type of setup.
A simplified interceptor could look like this:
public sealed class TenantConnectionInterceptor
: DbConnectionInterceptor
{
private readonly ICurrentTenant _currentTenant;
public TenantConnectionInterceptor(
ICurrentTenant currentTenant)
{
_currentTenant = currentTenant;
}
public override async Task ConnectionOpenedAsync(
DbConnection connection,
ConnectionEndEventData eventData,
CancellationToken cancellationToken = default)
{
await using var command = connection.CreateCommand();
command.CommandText = """
EXEC sys.sp_set_session_context
@key = N'TenantId',
@value = @TenantId;
""";
var parameter = command.CreateParameter();
parameter.ParameterName = "@TenantId";
parameter.Value = _currentTenant.TenantId;
command.Parameters.Add(parameter);
await command.ExecuteNonQueryAsync(cancellationToken);
}
}Then the interceptor can be registered with EF Core:
services.AddDbContext<ApplicationDbContext>(
(serviceProvider, options) =>
{
var interceptor =
serviceProvider.GetRequiredService<
TenantConnectionInterceptor>();
options.UseSqlServer(connectionString);
options.AddInterceptors(interceptor);
});EF Core supports registering interceptors on the DbContext configuration through AddInterceptors.
After this, normal application code remains normal:
var orders = await dbContext.Orders
.OrderByDescending(x => x.CreatedAt)
.ToListAsync();There is no tenant condition here.
SQL Server applies the RLS policy.
That separation is important to me. Business queries should describe the data the feature needs. Security infrastructure should enforce which portion of that data the caller is allowed to access.
Takeaway: An EF Core connection interceptor gives us a central place to pass tenant context into SQL Server instead of repeating tenant setup throughout the application.
Connection Pooling Is Where You Need to Be Careful
One thing I learned while working with tenant-aware database designs is that connection pooling changes how you should think about database sessions.
A connection used by Tenant A can eventually return to the pool and later be reused.
That means I never assume:
“I already configured this connection earlier.”
Tenant context should be established for the connection being used by the current operation.
This is also why blindly setting session context once somewhere during application startup would be wrong. The tenant belongs to the request or operation, not to the application process.
I also prefer a fail-closed design.
If TenantId is missing, the security predicate should normally return no tenant-owned rows rather than interpreting missing context as permission to see everything.
This gives failures a much safer behavior.
Takeaway: Treat tenant identity as request-scoped information and assume pooled connections can be reused.
Should I Still Use EF Core Global Query Filters?
Sometimes, yes.
At first this may look redundant:
EF Core Global Query Filter
+
SQL Server Row-Level SecurityBut they solve slightly different problems.
A global query filter makes tenant scoping explicit in the EF Core model and ensures normal LINQ queries automatically contain the expected filter. SQL Server RLS provides enforcement at the database boundary.
I sometimes think about them as:
Query Filter = application convenience
RLS = database enforcementUsing both can be reasonable for sensitive multi-tenant systems.
There is one caution, though. Global query filters have behaviors that developers need to understand. IgnoreQueryFilters() can disable them, and required navigations combined with filters may produce results developers do not initially expect.
If RLS is the actual security boundary, I try not to make the correctness of tenant isolation depend entirely on the EF filter.
Takeaway: Query filters can improve application design, while RLS protects the database boundary. They can complement each other instead of competing.
Performance Considerations
Row-Level Security is not free.
Every protected query involves the security predicate, so the columns participating in tenant filtering need to be considered when designing indexes.
For a table frequently queried inside a tenant, an index such as this may make sense:
CREATE INDEX IX_Orders_TenantId_CreatedAt
ON dbo.Orders(TenantId, CreatedAt);The exact index depends on the queries, of course. I would not create indexes blindly just because TenantId exists.
Instead, I check the actual execution plans and workload.
Microsoft’s RLS guidance also recommends indexing columns used by security predicates when performance requires it.
Another practical point is keeping the predicate simple. Security predicates are not a good place for complicated business logic involving many joins and calculations.
Tenant ID comparison is cheap and easy to reason about:
Row.TenantId == Session.TenantIdThat simplicity helps both performance and maintainability.
Takeaway: Keep RLS predicates simple, index tenant-aware access patterns appropriately, and verify performance using real execution plans.
Common Mistakes I Try to Avoid
The most dangerous mistake is trusting a TenantId supplied directly by the client.
For example, I would not design an API where this is automatically trusted:
GET /orders?tenantId=123and then put 123 into the SQL session.
The tenant should come from trusted authentication and authorization context established by the server.
Another mistake is protecting reads but forgetting writes.
Being unable to read another tenant’s order is good, but the system also needs to prevent creating or modifying rows in a way that crosses tenant boundaries. SQL Server RLS supports both filter predicates and block predicates for this reason.
Testing is another area where shortcuts are dangerous.
I normally want integration tests covering at least this behavior:
Tenant A creates Order A
Tenant B creates Order B
Login as Tenant A
-> Order A visible
-> Order B not visible
Login as Tenant B
-> Order B visible
-> Order A not visibleThen I test inserts and updates across tenants as well.
For RLS, I prefer integration tests against the real database engine rather than relying only on mocked DbContext tests. The important behavior exists inside SQL Server, so SQL Server needs to participate in the test.
Takeaway: Tenant identity must come from trusted server-side context, and isolation should be tested for reads and writes.
Final Thoughts
Row-Level Security changed how I think about multi-tenant data protection.
I still like EF Core global query filters. They make queries cleaner and reduce repetitive filtering. But for applications where tenant isolation is an important security requirement, I prefer having another boundary below the application.
The pattern becomes fairly straightforward:
Authentication
↓
Resolve TenantId
↓
EF Core
↓
Set SQL SESSION_CONTEXT
↓
SQL Server Security Policy
↓
Tenant-filtered dataThe application tells the database who the current tenant is. The database decides which rows that tenant is allowed to access.
For me, that is the biggest benefit. If somebody forgets a .Where(x => x.TenantId == tenantId) somewhere in a repository six months later, the mistake should not automatically become a cross-tenant data leak.
Security works better when one forgotten line of application code is not the only thing standing between two customers’ data.
Sources
Microsoft Learn: EF Core Global Query Filters
Covers global query filters, multi-tenancy, IgnoreQueryFilters(), required navigations, and filter limitations.
Global Query Filters documentation
Microsoft Learn: SQL Server Row-Level Security
Explains SQL Server RLS, security predicates, security policies, and database-level row access restrictions.
SQL Server Row-Level Security documentation
Microsoft Learn: CREATE SECURITY POLICY
Reference for creating filter and block predicates used by SQL Server Row-Level Security.
CREATE SECURITY POLICY documentation
Microsoft Learn: sp_set_session_context
Documents how applications can store key-value information such as tenant or user identifiers in a SQL Server session.
sp_set_session_context documentation
Microsoft Learn: EF Core Interceptors
Explains EF Core database interceptors, including connection interception and interceptor registration.
EF Core Interceptors documentation
Microsoft Learn: SQL Server Security Best Practices
Discusses RLS as a database security mechanism and recommends SESSION_CONTEXT for middle-tier applications using a shared SQL account.
SQL Server Security Best Practices
