I am using SqlConnectionStringBuilder to create my connection string. This is because I keep my database passwords in secrets.json for local development and in environment variables on the servers under IIS --> Website name --> Configuration Editor, Section: system.webServer/aspNetCore , From: ApplicationHost.config <location path='WebsiteName'/> .

I have done this in ASP.NET Core 3.1 with no issues. Currently I'm working on a site that was started in ASP.NET 5 and updated to ASP.NET Core 6. I'm using local db for local dev so in this instance there's no need for a database password in secrets.json (although I still use it for other secrets).

Whenever I try to run my website I get an error in the Windows Logs stating:
System.Data.SqlClient.SqlException (0x80131904): Login failed for user 'UserName'.

Another log entry right before it reads:

Login failed for user 'UserName'. Reason: Password did not match that for the login provided. [CLIENT: IP Address of Server]

If I hard code the password into the connection string in appsettings the website runs without issue. I can even get the database password from environment variables and display it on the page so I know the website is able to access them. However, if I inject the environment variables into the connection string, it's somehow injecting the wrong password or not working in some other way.

I've spent days on this issue and can't figure it out. The SQL Server user is set as an admin, and has dbowner rights to the database as well.

My code in startup.cs looks like this:

private string _csSqlDb = null;

And then inside public void ConfigureServices

var csBuilder = new SqlConnectionStringBuilder(
                Configuration.GetConnectionString("SqlDb"));

// Development uses local db so no password
if (!Environment.IsDevelopment())
{
    csBuilder.Password = Configuration["DbPassword"]; //use with .NET Core 3.1 and it works
    //csBuilder.Password = System.Environment.GetEnvironmentVariable("DbPassword");
}

_csSqlDb = csBuilder.ConnectionString;

services.AddDbContext<ApplicationDbContext>(options =>
                options.UseSqlServer(_csSqlDb));

Here are the connection strings I am using.

Local development (uses Windows Authenitcation to localdb) :

"ConnectionStrings": {
    "SqlDb": "Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=DbName;Integrated Security=True;Connect Timeout=60;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"
  }

Staging Server (Uses SQL Server Authentication to SQL Server Express 2019):

"ConnectionStrings": {
    "SqlDb": "Data Source=IPAddress\\SQLExpress;Initial Catalog=DBName;Persist Security Info=True;Trusted_Connection=False;User Id=UserName"
  }

If anyone has any insights or ideas as to what the problem can be, I would be very grateful. This issue is happening on my local Windows Server 2019 Standard running SQL Server 2019 Express, and also on a production server running Windows Server 2019 Standard running SQL Server 2017 Web Edition.