Database Targeting Debugging in ASP.NET Core

Overview

We tracked down a user-role issue that looked like a permissions problem, but the real cause was database targeting.

One user could sign in and get roles. Another user with apparently similar data could sign in but did not get the expected roles.

At first glance this looked like:

• an AspNetUsers / Employees mismatch
• a username convention problem
• a case-sensitivity problem
• or missing rows in AspNetUserRoles

Those were all worth checking, and we did improve that area too. But the actual production-style issue turned out to be simpler and more dangerous:

The application could be pointed at the wrong database without it being obvious.

That is a classic ASP.NET Core configuration trap. Connection strings can come from several places, and if the resolution order is wrong, a default value in appsettings.json can silently override the value you thought the app was using.

The Symptom

The practical symptom was:

• one user had roles
• another user did not
• local and work behavior did not line up
• testing suggested the app might not be using the database everyone assumed it was using

In our case, the risk was that the app could run against local SQLite instead of SQL Server.

Why This Happens in .NET Core

ASP.NET Core configuration is flexible, but that flexibility cuts both ways.

A connection string can be defined in:

• appsettings.json
• appsettings.{Environment}.json
• environment variables
• launchSettings.json for local development
• IIS / App Service / deployment host environment settings
• user secrets or other providers if added

That means the question is never just:

What is the configured connection string?

It is:

Which configuration source wins at runtime?

That is the part that bites.

Important: launchSettings.json Is Local Only

This is the first thing to remember:

Properties/launchSettings.json affects local runs from Visual Studio and dotnet run.

It does not control a published deployment.

So if:

• local development uses DB_CONNECTION from launchSettings.json
• and published work environments rely on server environment variables

then those are two different runtime worlds.

You cannot assume that because a local profile points to SQL Server, the published app also does.

The Specific Bug We Found

In our app we had a default SQLite connection in appsettings.json:

"ConnectionStrings": {
  "DefaultConnection": "Data Source=CDISV2Core.db",
  "UserActionLogging": "Data Source=CDISV2Core_Logs.db"
}

That default is useful for local development.

The problem was our connection resolver in Program.cs was preferring ConnectionStrings:DefaultConnection before DB_CONNECTION.

That meant a server-provided DB_CONNECTION could be ignored if appsettings.json already had a non-empty DefaultConnection.

So the app could quietly resolve to:

Data Source=CDISV2Core.db

even when the operator believed it was using SQL Server.

That is exactly the kind of bug that creates misleading role behavior:

• local SQLite data says one thing
• SQL Server says another
• the app behavior depends entirely on which database it actually opened

Why This Was Hard to Spot

There were several reasons this hid well:

1. The config looked correct at a glance.
2. launchSettings.json had a SQL Server profile, which made local runs look fine.
3. The default SQLite value in appsettings.json was always present.
4. The application did not originally make the final resolved DB source obvious enough at runtime.
5. Identity, employee matching, and roles were already complex enough to distract from the simpler infrastructure issue.

That combination is a minefield.

What We Changed

We made a set of changes to make database targeting explicit and diagnosable.

1. Make Runtime DB Settings Win

We changed the main DB resolver in Program.cs so DB_CONNECTION wins over ConnectionStrings:DefaultConnection.

Old behavior:

1. ConnectionStrings:DefaultConnection
2. DB_CONNECTION
3. ConnectionStrings:Default

New behavior:

1. DB_CONNECTION
2. ConnectionStrings:DefaultConnection
3. ConnectionStrings:Default

This matters because environment-driven deployments should be able to override local defaults.

2. Add Startup Diagnostics

We added startup logging that prints:

• main DB connection string source
• main DB provider
• logging DB connection string source
• logging DB provider

This makes it much easier to answer:

• are we using SQLite or SQL Server?
• did the value come from JSON or an environment variable?

3. Fix the Home Debug Page

We updated the home debug page logic so it shows the effective connection strings, not just the raw JSON defaults.

This matters because reading only GetConnectionString("DefaultConnection") can be misleading when the real runtime value is coming from DB_CONNECTION.

4. Add Toggleable Login Debug Emails

We added a LoginDebugEmail config section:

"LoginDebugEmail": {
  "Enabled": true,
  "RecipientEmail": ""
}

When enabled, the login flow sends a debug email through the existing email service.

If RecipientEmail is blank, it goes to admins using the existing system notification path.

The email includes:

• attempted email
• resolved username
• login outcome
• resolved roles
• main DB provider and connection string
• logging DB provider and connection string

This is useful when the issue only appears in another environment and you need evidence from the live authentication path.

5. Keep Logging DB Separate

We also made the logging database explicitly configurable and diagnosable.

That matters because:

• app data can come from SQL Server
• logging data can come from SQLite

and you need to know which is which.

Other Issues We Found Along the Way

The DB targeting issue was the big one, but we also found genuine user-linking risks:

• username drift between AspNetUsers.UserName and Employees.UserId
• employee lookup fallback by email for older records
• potential case-sensitive matching issues in some paths
• helper functions that were previously looking up employees using the wrong key

Those were real issues, but they are easier to reason about once you are sure the app is actually looking at the expected database.

That is the key lesson:

Always prove the database target first.

How to Verify the App Is Using the Right Database

Here is the checklist we recommend now.

Application-Level Checks

1. Check startup logs for:
   • main DB provider
   • main DB connection string source
   • logging DB provider
   • logging DB connection string source
2. Check the debug page or diagnostics endpoint for the effective resolved values.
3. If enabled, review the login debug email generated during sign-in.

Configuration Checks

1. Check appsettings.json.
2. Check appsettings.{Environment}.json.
3. Check environment variables on the host.
4. Check whether the host sets DB_CONNECTION or ConnectionStrings__DefaultConnection.
5. Remember that launchSettings.json does not apply to published environments.

Data Checks

1. Verify the user exists in AspNetUsers.
2. Verify the user has rows in AspNetUserRoles.
3. Verify the expected AspNetRoles exist.
4. Verify Employees.UserId matches the intended identity username.
5. Check whether duplicate identity or employee rows exist for the same email.

Practical Advice for Teams

If you support multiple environments, do not rely on “it should be using SQL Server.”

Make the app prove it.

Recommended practices:

• keep a local SQLite default if it helps development, but make runtime environment variables win
• log the chosen provider at startup
• log the source of the chosen connection string
• add a safe diagnostics page for admins
• separate app DB and logging DB config clearly
• do not assume launchSettings.json has anything to do with production behavior
• when debugging auth or roles, capture the DB target in the same diagnostic output

Suggested Configuration Pattern

For this app, the cleanest mental model is:

• main app DB:
  • local dev can use DB_CONNECTION=Data Source=CDISV2Core.db
  • deployed environments should set DB_CONNECTION explicitly
• logging DB:
  • local dev can use UserActionLogging:ConnectionString
  • deployed environments should also set that explicitly if needed

The important part is consistency.

The more places the same value is defined, the easier it is to fool yourself.

Final Lesson

This incident looked like a role problem. It looked like an employee-link problem. It even looked like a case-sensitivity problem.

But before any of that, it was a targeting problem.

If the app is pointed at the wrong database, every other investigation starts from the wrong facts.

That is why database targeting should be one of the first checks in any authentication, authorization, or role-assignment investigation in ASP.NET Core.

Configuration in .NET Core is powerful, but if you do not make precedence and runtime resolution visible, it becomes a minefield very quickly.