System.Data.SqlClient.SqlException: Login failed for user '' with a .NET Core 2.2 app

Question

I'm trying to publish my .net core 2.2 application through IIS Version (10.0.14393).

I've verified that the authentication works with my account/login and there isn't any database issues with the connection. When I've deployed to IIS, I can get onto my main home page but when I try accessing different pages, I get this type of error:

SqlException: Login failed for user '{Domain}\{Machine_Name}$'.

I've tried the following but none has been proven to guide me in resolving this issue:

1. turned on windows auth and turned off anonymous authorization in both my project, and on IIS web site.
2. Included windows auth sections in web config and application.json.
3. Made sure that the correct web.config application.json was being pushed

Could it be anything to do with my migrations? I have three connection strings and my appsettings.json file is setup in this manner:

{
  "Logging": {
    "IncludeScopes": false,
    "LogLevel": {
      "Default": "Warning"}},
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "KitLogContext": "Server={server};Database=DB;user Id=DBAdmin;Password=DB_Password;Trusted_Connection=True;MultipleActiveResultSets=true",
    "PermswageContext": ""Server={server};Database=DB;user Id=DBAdmin;Password=DB_Password;Trusted_Connection=True;MultipleActiveResultSets=true",
    "DATKitsContext": "Server={server};Database=DB;user Id=DBAdmin;Password=DB_Password;Trusted_Connection=True;MultipleActiveResultSets=true"
  },
  "iisSettings": {
    "windowsAuthentication": true,
    "anonymousAuthentication": false,
    "iisExpress": {
      "applicationUrl": "http://localhost/appname.com",
      "sslPort": 0
    }}}

Answer 1

"KitLogContext": "Server={server};Database=DB;user Id=DBAdmin;Password=DB_Password;Trusted_Connection=True;MultipleActiveResultSets=true",

You're setting Trusted_Connection=True, but providing a User_ID and Password. These are incompatible connection string settings.

Set Trusted_Connection=False if you are using a SQL Login and Password.

Otherwise add the AppDomain identity as a login to SQL Server. If IIS is running on the SQL Server this will be the Application Pool Identity. If the SQL Server is remote this may be the Machine Account for the IIS server, eg SomeDomain\ServerName$. EG

use DB
create login [SomeDomain\ServerName$] from windows
create user [SomeDomain\ServerName$] for login [SomeDomain\ServerName$]
alter role AppUserRole add member [SomeDomain\ServerName$]

Where AppServerRole is a custom database role with the least privileges to run your application code. eg

use DB
create role AppServerRole 
grant select, execute on schema::dbo to AppServerRole