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

Where have you added environment variables when configuring configuration? — Nkosi
– Nkosi, Commented Jan 11, 2021 at 12:35
You could configure your connection string in azure portal, so you need just to leave empty your connection string value in appconfig.json, the connection string will be replaced learn.microsoft.com/en-us/azure/app-service/… — invernomuto
– invernomuto, Commented Jan 11, 2021 at 12:45
Is Configuration used in Startup injected to built manually? — Nkosi
– Nkosi, Commented Jan 11, 2021 at 12:46
@Nkosi Thanks for the reply. Unfortunately, I don't quite understand your question. So I have added an Environment Variable to Windows on my development PC (as I'd like to get it working locally first). I have also added it in Azure App Service (under Configuration -> Application settings) as for production I assume I will need it there too. I guess I'm just not sure how to read these environment variables. I kinda thought it would happen automatically. — Fabricio Rodriguez
– Fabricio Rodriguez, Commented Jan 11, 2021 at 12:58
@FabricioRodriguez the default env variable prefixes are DOTNET_ and ASPNETCORE_. Have you tried DOTNET_ConnectionStrings__MyDataContext or ASPNETCORE_ConnectionStrings__MyDataContext ? — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jan 11, 2021 at 13:15

Tim · Accepted Answer · 2021-01-11 15:16:22Z

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"
      }
    }
  }
}

Fabricio Rodriguez · Accepted Answer · 2021-01-12 14:06:17Z

0

Ok, turns out the only problem was that I was running the Web API in debug mode (from within Visual Studio). Once published to Azure App Service, it worked. To recap:

In Azure App Service Configuration, I added an environment variable called ConnectionStrings__MyDataContext with the relevant value.

In my code's Startup.ConfigureServices() method, I read in the environment variable, and assign it, like this:

services.AddDbContext<LogContext>(options =>
    options.UseMySql(Configuration.GetConnectionString("MyDataContext"), new MySqlServerVersion(new Version(5, 7, 29))));

That's it!

answered Jan 12, 2021 at 14:06

Fabricio Rodriguez

4,34915 gold badges69 silver badges120 bronze badges

Collectives™ on Stack Overflow

Migrating database connection string from appconfig.json to Environment Variable

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related