5

We are creating an .NET Core 2.1 application with a Web API project that connects to a SQL Server database.

I am trying to get my head around the proper use of environment variables when it comes storing my connection strings in the most secure location possible.

When I heard that I could use environment variables to store things like a connection string that sounded really good. It is secure and we can have the variables differ from developer to developer and on staging and production environment. Which is useful as they do not all have the same name for their SQL Server instance.

But when I now add a connection string to my Windows environment variables like so:

Enter image description here

or through the PowerShell like so:

setx CUSTOMCONNSTR_MyContextDb "Server=.\\MYSQLSERVER;Database=MyDb_local;Trusted_Connection=True;ConnectRetryCount=0"

And I try to get it like so:

var connectionString = Configuration.GetConnectionString("MyContextDb");

But this does not work. The string is null.

I can only get this line of code to work if I add the CUSTOMCONNSTR_MyContextDb variable and the value to the environment variables when I go to the properties of the project and than enter the environment variable there on the Debug tab. Which basically just sets them in the launchSettings.json file. This hardly seems a better location than appsettings.json (or is it?).

Have I misunderstood the use of environment variables in .NET Core? Can I not use the windows-defined environment variables? Or can I only use real Windows environment variables on my IIS server?

It looks like I can, because I see a list of over 80 environment variables when I debug and look at the Configuration object.

For reference, this post with a short YouTube clip helped me to better understand the concepts around this, but I do not fully grasp everything, and I hope you can help.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Daniël Tulp
  • 1,745
  • 2
  • 22
  • 51

3 Answers3

4

Configuration.* reads from Application Configuration files. You're looking for

Environment.GetEnvironmentVariable("CUSTOMCONNSTR_MyContextDb")

For the question of "Where should connection strings be stored", is not so easy to answer. It is a bit of an opinion question, and as usual it depends.

If the only bit of configuration data that was expected to change, I would consider sticking to using just the appsettings.json for convenience. If more configuration options were of concern, like running locally and on Azure, I may consider an overriding approach like this. Another consideration would be the number of team members working on the same project. Each might have their own local database instance with different settings. In that case SecretManager might be a good option.

There's lots of tools and methods to the madness, as you've probably seen so far. Unfortunately there isn't a catch-all solution I can recommend above others. I would stick with what you're trying now until it doesn't work.

I don't believe you've misunderstood the concept overall, except "windows defined environment variables" and the other phrase "real environment variables on my IIS server". Windows and IIS are still just software that someone else wrote. They don't have any more or less value than your environment variables.

I've never set the environment variables in the tab you mentioned, so I had no idea that's stored in the launchSettings.json. I'll have to dig more on that later.

Adam Vincent
  • 3,281
  • 14
  • 38
  • this does indeed work, but does not quite answer all my questions also, be aware that this requires a restart of Visual Studio! (I did not vote you down by the way) – Daniël Tulp Nov 28 '18 at 21:29
  • 1
    this only requires a restart of visual studio because visual studio, as all applications, only retrieves the env variables on start. If you were to say launch your application with 'dotnet run' you wouldn't run into the issue. – Adam Vincent Nov 28 '18 at 21:32
  • ok, so we have a VPS with IIS running and I want to securely store my connectionstrings somewhere on that machine, I can not find any good suggestion for that (everybody says to use Azure and we don't do that). Do you have any suggestions? – Daniël Tulp Nov 29 '18 at 08:24
  • No Azure keyvault? The best recommendation I can think of is encrypting the settings file on the on premise box. There is an example of how to create a custom configuration provider on [this post](https://stackoverflow.com/questions/36062670/encrypted-configuration-in-asp-net-core) – Adam Vincent Nov 29 '18 at 12:53
  • In the end I added the environment variable to the IIS website through the IIS manager: Select the website, open the Configuration editor, open settings for system.webServer/aspNetCore and there you can set the environment variables (restart of the website is required) – Daniël Tulp Jan 07 '19 at 13:26
1

I think there's a clear misunderstanding of environment variables here.

When talking about the configuration of a single ASP application, especially since Docker is a thing, etc., an environment variable means a configuration variable for that specific application stored in appsettings.json or launchsettings.json.

The Windows environment variables are not meant here and usually are not taken into account for ASP.NET Core applications for quite a few reasons.

Your snippet there:

var connectionString = Configuration.GetConnectionString("MyContextDb");

is explicitly reading data from the "ConnectionString" section in appsettings.json. You can't overwrite that.

The best approach to have "per user" settings is in my eyes to use one global appsettings.json with values valid for all developers/users and every user has its own appsettings_develop.json containing his/her personal connection string and so on. The personal one is added to gitignore and not committed and only living on the user's machine.

I'd advise not to use any environment variables from the host system (your Windows User environment variables there, for example) as you'd need to make sure they exist everywhere you want to use your application, if you put it in Docker the Docker container needs to have them, etc., can't commit environment variables using GitHub or TFS. JSON configurations are your best option I'd say.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Tim Cadenbach
  • 1,446
  • 1
  • 9
  • 21
  • GetConnectionString can also read from launchsettings.json if you have set AddEnvironmentVariables in the WebHost builder – Daniël Tulp Nov 29 '18 at 08:22
1

The code for the GetConnectionString method looks like this:

public static string GetConnectionString(this IConfiguration configuration, string name)
{
    return configuration?.GetSection("ConnectionStrings")?[name];
}

This shows that the connection-string itself must live inside a ConnectionStrings section. As your IConfiguration instance represents what is essentially a dictionary of string to string, this ends up looking for a key named ConnectionStrings:[name].

To resolve the problem with your environment variable not being found, you should name it ConnectionStrings:MyContextDb (you can also use __ instead of :, but : is much more common).

When I first read your question, I was surprised that adding it to the launchSettings.json file made it work, but I've since discovered that this value of CUSTOMCONNSTR_MyContextDb is being converted into ConnectionStrings:MyContextDb for you. You can confirm this by using something like this:

new ConfigurationBuilder()
    .AddEnvironmentVariables()
    .Build()
    .AsEnumerable()
    .ToList()
    .ForEach(x => Console.WriteLine("{0} => {1}", x.Key, x.Value));

That's just a very cheap and cheerful way of viewing all of the key/value pairs that have been added to an IConfiguration instance.

Kirk Larkin
  • 84,915
  • 16
  • 214
  • 203