question

I attempted to follow the instructions here on the section about String Replacement with secrets in my .NET 6 Razor Pages Web App.

However, when I attempt to load a page that references the Connection String "SmartCareConnection" I get the error from SQL Server that the login failed. What I was expecting is that the login would be successful because it would use the password from the store. How can I get this to work?

Note: when I uncomment app.MapGet("/", () => connection) from program.cs; and run the app, a page appears with the expected connection string that includes the password from the store.

Program.cs
using Toolkat.RoleAuthorization;
using Microsoft.AspNetCore.Authentication.Negotiate;
using Microsoft.EntityFrameworkCore;
using Toolkat.Models.SmartCareScaffold;
using Toolkat.Models.ToolkatScaffold;
using System.Reflection;
using System.Data.SqlClient;

 var builder = WebApplication.CreateBuilder(args);
    
 // Add services to the container.
    
 builder.Services.AddAuthentication(NegotiateDefaults.AuthenticationScheme)
    .AddNegotiate();
    
 builder.Services.AddAuthorization(options =>
 {
     /*
     options.FallbackPolicy = new AuthorizationPolicyBuilder()
         .RequireAuthenticatedUser()
         .Build();
     */
     // By default, all incoming requests will be authorized according to the default policy.
     options.FallbackPolicy = options.DefaultPolicy;
 });
 builder.Services.AddRazorPages();
 //builder.Services.AddDbContext<AppDbContext>(options => options.UseInMemoryDatabase("InMemoryDb"));
 builder.Services.AddDbContext<JourneySmartCareQAContext>(options =>
 {
     options.UseSqlServer(builder.Configuration.GetConnectionString("SmartCareConnection"));
 });
    
 builder.Services.AddDbContext<ToolkatContext>(options =>
 {
     options.UseSqlServer(builder.Configuration.GetConnectionString("ToolkatConnection"));
 });
    
 builder.Services.AddAuthorization(options =>
 {
     options.AddPolicy("Files",
          policy => policy.RequireRole("Admin", "User"));
     options.AddPolicy("Roles",
          policy => policy.RequireRole("Admin"));
     options.AddPolicy("UserRoles",
          policy => policy.RequireRole("Admin"));
     options.AddPolicy("Users",
          policy => policy.RequireRole("Admin"));
     options.AddPolicy("Error",
          policy => policy.RequireRole("Admin", "User"));
     /*options.AddPolicy("Index",
          policy => policy.RequireRole("Admin", "User"));
     */
     options.AddPolicy("JobIsRunning",
          policy => policy.RequireRole("Admin", "User"));
     options.AddPolicy("Process",
          policy => policy.RequireRole("Admin", "User"));
     options.AddPolicy("Result",
          policy => policy.RequireRole("Admin", "User"));
     options.AddPolicy("Upload",
          policy => policy.RequireRole("Admin","User"));
 });
    
    
 builder.Services.AddSimpleRoleAuthorization<MySimpleRoleProvider>();
    
 builder.Configuration.AddUserSecrets(Assembly.GetExecutingAssembly(), true);
    
 //https://docs.microsoft.com/en-us/aspnet/core/security/app-secrets?view=aspnetcore-6.0&tabs=windows
 var conStrBuilder = new SqlConnectionStringBuilder(
         builder.Configuration.GetConnectionString("SmartCareConnection"));
 conStrBuilder.Password = builder.Configuration["SmartCareDBPassword"];
 var connection = conStrBuilder.ConnectionString;
    
    
 var app = builder.Build();
    
 // Configure the HTTP request pipeline.
 if (!app.Environment.IsDevelopment())
 {
     app.UseExceptionHandler("/Error");
     // The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts.
     app.UseHsts();
 }
    
 app.UseStatusCodePages("text/html", "<h1>Error! Status Code {0}</h1>");//use pages instead of browser error, if placed lower it might not work
    
 app.UseHttpsRedirection();
 app.UseStaticFiles();
    
 app.UseRouting();
    
 app.UseAuthentication();
 app.UseAuthorization();
    
 app.MapRazorPages();
    
 //app.MapGet("/", () => connection); //https://docs.microsoft.com/en-us/aspnet/core/security/app-secrets?view=aspnetcore-6.0&tabs=windows
    
 app.Run();

appsettings.json

 {
   "PageSize": 10,
   "Jobs": {
     "ToolkatToSmartCare": "ToolkatToSmartCare"
   },
   "ConnectionStrings": {
     "SmartCareConnection": "Server=10.20.36.6\\Journey; Database=JourneySmartCareTrain; User Id=mbreunig; Trusted_Connection=False;",
     "ToolkatConnection": "Data Source=sqlmaster;Integrated Security=True;Connect Timeout=60;Encrypt=False;TrustServerCertificate=True;Initial Catalog=Toolkat"
   },
   "FilePaths": {
     //"Local": "C:\\toolkat\\",
     "Local": "\\\\rds2\\toolkat\\",
     "Share": "\\\\rds2\\toolkat\\"
   },
   "Logging": {
     "LogLevel": {
       "Default": "Information",
       "Microsoft.AspNetCore": "Warning"
     }
   },
   "AllowedHosts": "*",
   "FileSizeLimit": 2097152
 }