c# - Migrating database connection string from appconfig.json to Environment Variable

Question

I have a .Net 5 Web API hosted as an Azure App Service. The app connects to a MySql database, also hosted on Azure (Azure Database for MySQL server).

Until now, I've had the connection string for the MySql database in the code's appsettings.json file. I would like to move this to an Environment Variable. (I'm aware of Azure Key Vault, but I got a bit lost trying to figure that out, so I thought I'd try using Environment Variables instead)

For now, I am testing this locally on my development PC (Windows 10 + Visual Studio Community 2019). Once I get it working there, I will do the same on production environment.

So like I mentioned, currently the connection string to the MySql database is stored in the appsettings.json file as follows:

"ConnectionStrings": {
    "MyDataContext": "Server=abc.mysql.database.azure.com;user id=someuser@abc;Pwd=somepassword;persistsecurityinfo=True;database=somedatabase;TreatTinyAsBoolean=false"
  },

So I created a System Variable called ConnectionStrings__MyDataContext on my development machine. I then deleted the above lines from appsettings.json to make sure my app is not reading from there anymore.

But now I'm a bit lost... I thought perhaps this was all I needed to do, as my Program.cs, which looks like this:

public static IHostBuilder CreateHostBuilder(string[] args) =>
    Host.CreateDefaultBuilder(args)
        .ConfigureWebHostDefaults(webBuilder =>
        {
            webBuilder.UseStartup<Startup>();
        })
        .ConfigureLogging(logging =>
        {
            logging.ClearProviders();
            logging.AddConsole();
            logging.AddDebug();
            logging.AddEventSourceLogger();
            logging.SetMinimumLevel(Microsoft.Extensions.Logging.LogLevel.Trace);
        })
   .UseNLog();
}

Contains the CreateDefaultBuilder() which, according to Microsoft docs, "loads app IConfiguration from environment variables". However, in my Startup.cs:

public void ConfigureServices(IServiceCollection services)
{
    ...
    services.AddDbContext<MyContext>(options =>
        options.UseMySql(Configuration.GetConnectionString("MyDataContext"), new MySqlServerVersion(new Version(5, 7, 29))));
    ...
}

Configuration.GetConnectionString("MyDataContext") returns null.

I also tried adding the connection string as a User Variable instead of an Environment Variable, but this made no difference.

Is there something else I need to do to get this to work? I haven't tried this in a production environment yet - I'd like to get it working on my development machine first.

Thanks

Answer 1

Try using LaunchSettings.json to specify ConnectionStrings__MyDataContext.

{
  "iisSettings": {
    "windowsAuthentication": false, 
    "anonymousAuthentication": true, 
    "iisExpress": {
      "applicationUrl": "http://localhost:64645",
      "sslPort": 44366
    }
  },
  "profiles": {
    "IIS Express": {
      "commandName": "IISExpress",
      "launchBrowser": true,
      "environmentVariables": {
        "ASPNETCORE_ENVIRONMENT": "Development"
      }
    },
    "ConnStringInEnv": {
      "commandName": "Project",
      "launchBrowser": true,
      "applicationUrl": "https://localhost:5001;http://localhost:5000",
      "environmentVariables": {
        "ASPNETCORE_ENVIRONMENT": "Development",
        "ConnectionStrings__MyDataContext": "Server=abc.mysql.database.azure.com;user id=someuser@abc;Pwd=somepassword;persistsecurityinfo=True;database=somedatabase;TreatTinyAsBoolean=false"
      }
    }
  }
}