Connect to SQL Server from IIS (ASP.Net Core)

I spend hours trying multiple combinations to connect my asp.net core site hosted on an IIS Server to a remote SQL Server using just the SQL user and password without exit. This is my connection string in appsettings.json:

 "ConnectionStrings": {
    "Booking": "Server=SQLServerName;Database=Booking;User Id=XXX;Password=XXX;"
  }

• I have tried to set Integrated security = false and Trusted connection = false
• I changed the property Identity in the App pool to LocalSystem, although _I have tried all the options.
• I can connect to the database from my computer to SQLManager without any issue.
• In my events viewer, I see the next error: Microsoft.Data.SqlClient.SqlException (0x80131904): Login failed for user 'DOMAIN\NAMEOFTHESERVER$'.
• SQL Server 2016 in the database server, windows server 2016 in the webserver.
• I use windows authentication for user authentication, but I need to use a simple user and password to connect to the db.

Any idea about what I'm doing wrong?

Thank you

SqlException (0x80131904): Login failed for user 'DOMAIN\NAMEOFTHESERVER$'.

This means that the connection string you show is not being used, because this username belongs to NT AUTHORITY\NETWORK SERVICE. So there's another configuration file being used, for example appsettings.development.json, which uses Integrated Security=True, using the application pool's identity.

Change the environment in your web.config to production, so the appsettings.development.json is not used:

<system.webServer>
    <aspNetCore ...>
      <environmentVariables>
        <environmentVariable name="ASPNETCORE_ENVIRONMENT" value="Production" />