.NET Core 6 : to protect potentially sensitive information in your connection string

How do I move sensitive information below into the 'The Secret Manager Tool'?

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
=> optionsBuilder.UseSqlServer("Server=Test; column encryption setting=enabled;Database=Test;user id=User1;password='Password1';Trust Server Certificate=true");

I know I can right click on the solution name and select "Manage User Secrets", which then generates the secret Json file, but what I am pasting into this file?

And when I move this application over to the production server, do I copy & paste over the secret.json as well?

Thanks in advance.

You need to take a small step back and consider tools ASP.NET Core/.NET provides to work with configuration.

From DbContext Lifetime, Configuration, and Initialization doc you can see that one of the common pattern is to use dependency injection and setup the the connection string on application startup. This will require adding constructor to the context (and modifying/removing OnConfiguring overload - docs):

public class ApplicationDbContext : DbContext
{
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
        : base(options)
    {
    }
}

And:

builder.Services.AddDbContext<ApplicationDbContext>(
        options => options.UseSqlServer("ConnectionStringHere")); // or AddDbContextFactory

Next step is to read the connection string from the settings:

builder.Services.AddDbContext<ApplicationDbContext>(
        options => options.UseSqlServer(builder.Configuration.GetConnectionString("ConnStringName"))); // or AddDbContextFactory

Which will require connection string to be read from the configuration, for example from appsettting.json:

{
  "ConnectionStrings": {
    "ConnStringName": "ACTUAL_CONNECTION_STRING"
  },
}

Also you can move connection string to any of the supported configuration providers, for example - Secret Manager (note, it is for development environment only, for other environments is better to use either environment variables or secured key storages).

When running production apps in AWS, GCP, or Azure, we keep all secrets in the cloud provider's respective secret manager (e.g. AWS Secrets Manager) and use scripting when the runtime environment is created to inject those secrets into environment variables.

Note, by Secrets Manager, I don't mean the .NET Secret Manager, which merely stores secrets in a file on the development machine.

.NET can read configuration from the environment, including connection strings.

With this approach, only trusted DevOps personnel ever have the ability to view a connection string. It is never in a file, and certainly never in source control.

In the production server you need to create a configuration key with the same name with the production connection string.

Set the ASPNETCORE_ENVIRONMENT variable to "Production"

When environment "ASPNETCORE_ENVIRONMENT" is in "Production" the secrets.json source is ignored. You can check this in Default application configuration sources

According to ASP.NET Core documentation

WebApplication.CreateBuilder initializes a new instance of the WebApplicationBuilder class with preconfigured defaults. The initialized WebApplicationBuilder (builder) provides default configuration for the app in the following order, from highest to lowest priority:

1. Command-line arguments using the Command-line configuration provider.
2. Non-prefixed environment variables using the Non-prefixed environment variables configuration provider.
3. User secrets when the app runs in the Development environment.
4. appsettings.{Environment}.json using the JSON configuration provider. For example, appsettings.Production.json and appsettings.Development.json.
5. appsettings.json using the JSON configuration provider.
6. A fallback to the host configuration described in the next section.

Best that you can do for the connection string in the production server is to configure windows authentication.

1. There must be a network user configured. This user should be given permissions in the SQL Server.
2. When you configure your Web server pool, run this pool under identity created in #1
3. At this point your connection string will look like this Server=MyServerName;Database=MyDbName;Trusted_Connection=SSPI;Encrypt=false;TrustServerCertificate=true. As you see - no password or user

With this setup, unless attacker knows password to the account under which your pool runs, this is plenty secure.

If you insist in using Sql Authentication, you need to store your credentials in the secure secrets manager (even if homegrown) and then acquire them and build connection string at runtime.

The simplest solution can be

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            => optionsBuilder.UseSqlServer("Name=ConnectionStrings:DB1ConnectionString");

and your appsetting.json

{
  "ConnectionStrings": {
    "DB1ConnectionString": "Data Source=sqlServer;Initial Catalog=<databseName>;User=<UserName>;Password=<Password>;TrustServerCertificate=True"
    "DB2ConnectionString": ...
  }
}