question

MaxPowers1982-8385 avatar image
0 Votes"
MaxPowers1982-8385 asked AgaveJoe edited

.NET 6 Razor Pages use secret in EF Core Connection String

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.

224448-image.png

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
 }



dotnet-entity-framework-coredotnet-aspnet-core-razor
image.png (302.5 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

AgaveJoe avatar image
0 Votes"
AgaveJoe answered AgaveJoe edited

You are not using the connection string you built.

   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;
     
     
  builder.Services.AddDbContext<JourneySmartCareQAContext>(options =>
  {
      options.UseSqlServer(connection);
  });
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I get this error when attempting what you have suggested (connection has the password, I'm just hiding it in the screenshot)
224860-image.png


0 Votes 0 ·
image.png (155.1 KiB)

This works though, since it is already a connection string
options.UseSqlServer(connection);

0 Votes 0 ·
AgaveJoe avatar image AgaveJoe MaxPowers1982-8385 ·

Sorry the code should be

   builder.Services.AddDbContext<JourneySmartCareQAContext>(options =>
   {
       options.UseSqlServer(connection);
   });
0 Votes 0 ·