How to fix a problem where an ASP.Net Core MVC application could not access data after it is published to IIS using VS.

Hi,

Thanks for reaching out to this forum.

This occurs during the TDS login handshake before SQL authentication/authorization. If TrustServerCertificate=True or Encrypt=False is really in the effective connection string, the driver should either skip validation (TrustServerCertificate) or not negotiate encryption (Encrypt=False) unless the server forces it.

Since you have stated that you have tried this, I suggest the best course of action is troubleshooting this problem step by step.

Decision Tree:

1. Log the actual connection string inside IIS.
2. If it’s missing TrustServerCertificate=True, fix configuration precedence.
3. If it includes it but still fails, test a standalone console app as the same identity.
4. If console also fails: SQL Server encryption / certificate configuration issue.
5. If console succeeds: web app identity / override issue.
6. Once connectivity works, add proper certificate (optional Step 8) OR keep TrustServerCertificate=True.

Step 1. Confirm the Real Error Context You already have the stack trace. We just accept: failure occurs during TLS handshake before migration runs.

Step 2. Log Effective Connection String at Runtime (Under IIS) Add temporarily to Program.cs BEFORE AddDbContext:

var builder = WebApplication.CreateBuilder(args);
 
// TEMP: dump connection strings
var snapshot = builder.Configuration.AsEnumerable()
    .Where(kv => kv.Key.StartsWith("ConnectionStrings"))
    .ToList();
File.WriteAllLines(Path.Combine(AppContext.BaseDirectory, "conn-effective.txt"),
    snapshot.Select(kv => $"{kv.Key} = {kv.Value}"));
Console.WriteLine("== ConnectionStrings ==");
foreach (var kv in snapshot) Console.WriteLine($"{kv.Key} = {kv.Value}");

After building the app (still in Program.cs):

var app = builder.Build();
 
// TEMP: log DbContext actual connection string
using (var scope = app.Services.CreateScope())
{
    var ctx = scope.ServiceProvider.GetRequiredService<YourDbContext>();
    var cs = ctx.Database.GetDbConnection().ConnectionString;
    File.WriteAllText(Path.Combine(AppContext.BaseDirectory, "dbcontext-connection.txt"), cs);
}

Publish, browse once, then open the published folder/logs and inspect:

• conn-effective.txt
• dbcontext-connection.txt

You MUST see: TrustServerCertificate=True (or Encrypt=False if you are choosing that workaround — but prefer TrustServerCertificate=True if the server forces encryption).

If it is missing: proceed to Step 3.

Step 3. Check Configuration Overrides (Provider Precedence) Inspect for environment overrides:

1. IIS Manager → Site → Configuration Editor → system.webServer/aspNetCore → look for <environmentVariables>.
2. Application Pool → Advanced Settings → Identity (note which user runs it).
3. System/User Environment Variables: none should be named ConnectionStrings__DefaultConnection.
4. Published web.config: search for ConnectionStrings__.
5. Confirm appsettings.json and (if used) appsettings.Production.json in publish folder contain updated string:

      "ConnectionStrings": {
        "DefaultConnection": "Server=localhost\\SQLEXPRESS;Database=GateAccessPro;Trusted_Connection=True;TrustServerCertificate=True;MultipleActiveResultSets=True"
      }
   

Fix any override you find, republish, re-run Step 2.

Step 4. Test SQL Connectivity Under SAME Identity (Outside IIS Code) Open a command prompt running as the app pool identity (or temporarily change pool to a custom service account you can log on with):

sqlcmd -S localhost\SQLEXPRESS -E -Q "SELECT @@VERSION"

If this fails: SQL Server instance / permissions / network protocol issue (resolve before continuing).

If success, move to Step 5.

Step 5. Minimal Console App Connectivity Test Create a small console project (NET 7/8) on the server; run as same identity:

using Microsoft.Data.SqlClient;
var cs = "Server=localhost\\SQLEXPRESS;Database=GateAccessPro;Trusted_Connection=True;TrustServerCertificate=True;";
try
{
    using var cn = new SqlConnection(cs);
    cn.Open();
    Console.WriteLine("OK: " + cn.ServerVersion);
}
catch (Exception ex)
{
    Console.WriteLine("FAIL:\n" + ex);
}

Outcomes:

• Succeeds: The SQL stack is fine; web app config/identity mismatch.
• Fails with same SSL error: SQL Server encryption setting or cert trust problem (go to Step 7).

Step 6. Version + Package Sanity Check .csproj for Microsoft.Data.SqlClient version (prefer current stable 5.x):

<ItemGroup>
<PackageReference Include="Microsoft.Data.SqlClient" Version="5.2.0" />
</ItemGroup>

Rebuild & republish. Remove any lingering System.Data.SqlClient references to avoid mixed behaviors.

Step 7. Narrow Further (Force Encryption? Identity? Permissions?)

• Open SQL Server Configuration Manager → SQL Server Network Configuration → Protocols for SQLEXPRESS → TCP/IP → Properties → Flags → Force Encryption.
  • If Force Encryption = Yes and you don’t have a trusted cert installed, you MUST use TrustServerCertificate=True.
• If after confirming the connection string includes TrustServerCertificate=True it still fails:
  • You may actually be connecting to a different instance (e.g. a cached LocalDB) → search published folder for localdb (findstr /S /I localdb *.*).
• Once SSL error disappears, if you then get:
  • Login failed for user 'IIS APPPOOL\YourAppPool': create a login and user:

        CREATE LOGIN [IIS APPPOOL\YourAppPool] FROM WINDOWS;
        USE GateAccessPro;
        CREATE USER [IIS APPPOOL\YourAppPool] FOR LOGIN [IIS APPPOOL\YourAppPool];
        ALTER ROLE db_owner ADD MEMBER [IIS APPPOOL\YourAppPool]; -- reduce later
    

• Temporarily remove automatic migrations to isolate connectivity:

try
{
    using var scope = app.Services.CreateScope();
    var db = scope.ServiceProvider.GetRequiredService<YourDbContext>();
    if (await db.Database.CanConnectAsync())
        Console.WriteLine("CanConnect OK");
    else
        Console.WriteLine("CanConnect FALSE");
}
catch (Exception e)
{
    File.WriteAllText(Path.Combine(AppContext.BaseDirectory, "canconnect-error.txt"), e.ToString());
}

Step 8 (Optional but Proper): Install and Use a Trusted Certificate

Do this only after you’ve proven the diagnostic issue (wrong/overridden connection string) is solved, unless policy requires real TLS trust immediately.

1. Obtain or create a certificate whose CN / SAN matches the machine name (e.g. MYDEVBOX or FQDN).
2. Import into Local Computer > Personal (Certificates MMC).
3. Grant private key read to SQL Server service account (NT SERVICE\MSSQL$SQLEXPRESS or the custom account).
4. SQL Server Configuration Manager → Protocols for SQLEXPRESS → Properties → Certificate tab → select the cert.
5. (Optional) Set Force Encryption = Yes.
6. Restart SQL Server (SQLEXPRESS) service.
7. Update connection string:

      Server=MYDEVBOX;Database=GateAccessPro;Trusted_Connection=True;Encrypt=True;TrustServerCertificate=False;MultipleActiveResultSets=True;
   

8. If you used localhost before, change to the machine name (certificate must match).

Final Reference Connection Strings

Development (bypassing trust while diagnosing):

Server=localhost\\SQLEXPRESS;Database=GateAccessPro;Trusted_Connection=True;TrustServerCertificate=True;MultipleActiveResultSets=True;

Production (with real cert):

Server=MYDEVBOX;Database=GateAccessPro;Trusted_Connection=True;Encrypt=True;TrustServerCertificate=False;MultipleActiveResultSets=True;

Please let me know if this helps. If the problem still persists, please give me results of the steps you've taken.

From your log message it seems that this is a certificate issue. You might want to refer this answer thread

Are you aware of the following issues? Did you take neccessary actions to cope with them?

1. In SQL Server Express, a named instance is always used by default. You specify an instance name when you install SQL Server Express, or the default named instance of SQLExpress is used. If the default named instance is used, you reference the instance as computername\SQLExpress.
2. The SQL Server Browser Service is required to connect your app to the SQL Server named instance over the TCP/IP protocol.
3. The data and transaction log files of a database must be attached to the instance of SQL Server.
4. If you use the Windows authentication on the SQL Server, the account of IIS worker process must have the access right to the SQL Server.