ef core migration can't use secret manager

Question

When I create .net core web applications, I use the secret manager during testing. I am generally able to create a new web project (mvc and web api), right click on the project and select "manage user secrets". This opens a json file where I add the secrets. I then use this in my startup.cs something like this:

services.AddDbContext<ApplicationDbContext>(options =>
    options.UseMySql(Configuration["connectionString"]));

The website works fine with this and connects well to the database. However when I try using ef core migration commands such as add-migration, they don't seem to be able to access the connection string from the secret manager. I get the error saying "connection string can't be null". The error is gone when I hard code Configuration["connectionString"] with the actual string. I have checked online and checked the .csproj file, they already contain the following lines:

<UserSecretsId>My app name</UserSecretsId>

And later:

<ItemGroup>
<DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="2.0.1" />
<DotNetCliToolReference Include="Microsoft.Extensions.SecretManager.Tools" Version="2.0.0" />

Is there anything I need to add so the migrations can access the connection string?

Update

I only have one constructor in the context class:

public ApplicationDBContext(DbContextOptions<ApplicationDBContext> options) : base(options)
{
}

Answer 1

I am currently coming across this exact problem as well. I have come up with a solution that works for now, but one may consider messy at best.

I have created a Configuration Class that provides the Configuration Interface when requested:

public static class Configuration
{
    public static IConfiguration GetConfiguration()
    {
        return new ConfigurationBuilder()
            .AddJsonFile("appsettings.json", true, true)
            .AddUserSecrets<Startup>()
            .AddEnvironmentVariables()
            .Build();
    }
}

In the Migration, you can then get the Configuration File and access its UserSecrets like this:

protected override void Up(MigrationBuilder migrationBuilder)
{
    var conf = Configuration.GetConfiguration();
    var secret = conf["Secret"];
}

I have tested creating a SQL Script with these User Secrets, and it works (you obviously wouldn't want to keep the Script laying around since it would expose the actual secret).

Update

The above config can also be set up into Program.cs class in the BuildWebHost method:

 var config = new ConfigurationBuilder().AddUserSecrets<Startup>().Build();

 return WebHost.CreateDefaultBuilder(args).UseConfiguration(config)...Build()

Or in the Startup Constructor if using that Convention

Update 2 (explanation)

It turns out this issue is because the migration scripts runs with the environment set to "Production". The secret manager is pre-set to only work in "Development" environment (for a good reason). The .AddUserSecrets<Startup>() function simply adds the secrets for all environment.

To ensure that this isn't set to your production server, there are two solutions I have noticed, one is suggested here: https://docs.microsoft.com/en-us/ef/core/miscellaneous/cli/powershell

Set env:ASPNETCORE_ENVIRONMENT before running to specify the ASP.NET Core environment.

This solution would mean there is no need to set .AddUserSecrets<Startup>() on every project created on the computer in future. However if you happen to be sharing this project across other computers, this needs to be configured on each computer.

The second solution is to set the .AddUserSecrets<Startup>() only on debug build like this:

return new ConfigurationBuilder()
   .AddJsonFile("appsettings.json", true, true)
#if DEBUG
   .AddUserSecrets<Startup>()
#endif
   .AddEnvironmentVariables()
   .Build();    

Additional Info

The Configuration Interface can be passed to Controllers in their Constructor, i.e.

private readonly IConfiguration _configuration;
public TestController(IConfiguration configuration)
{
    _configuration = configuration;
}

Thus, any Secrets and Application Setting are accessible in that Controller by accessing _configuration["secret"].

However, if you want to access Application Secrets from, for example, a Migration-File, which exists outside of the Web Application itself, you need to adhere to the original answer because there's no easy way (that I know of) to access those secrets otherwise (one use case I can think of would be seeding the Database with an Admin and a Master Password).

Answer 2

To use migrations in NetCore with user secrets we can also set a class (SqlContextFactory) to create its own instance of the SqlContext using a specified config builder. This way we do not have to create some kind of workaround in our Program or Startup classes. In the below example SqlContext is an implementation of DbContext/IdentityDbContext.

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Design;
using Microsoft.Extensions.Configuration;

public class SqlContextFactory : IDesignTimeDbContextFactory<SqlContext>
{
    public SqlContext CreateDbContext(string[] args)
    {
        var config = new ConfigurationBuilder()
            .AddJsonFile("appsettings.json", optional: false)
            .AddUserSecrets<Startup>()
            .AddEnvironmentVariables()
            .Build();

        var builder = new DbContextOptionsBuilder<SqlContext>();
        builder.UseSqlServer(config.GetConnectionString("DefaultConnection"));
        return new SqlContext(builder.Options);
    }
}