How to correctly store connection strings in environment variables for retrieval by production ASP.Net Core MVC applications

Question

I am working on an ASP.NET Core MVC application and I am having an issue with my connection strings.

I have an ASPNETCORE_ENVIRONMENT variable set to Production on my production server and my production server is a Windows Server 2012R2 running IIS. I also have the DotNetCore.1.0.4_1.1.1-WindowsHosting.exe installed on the production server.

During development, I am using UserSecrets to hold my connection string. This is working properly.

For production, I want my connection strings in environment variables on my production server and this is where I am having an issue. I suspect that it may be in the way I am structuring the environment variable.

When I try to access the database in production I get an error indicating basically that it can't cannot parse the connection string.

An exception occurred in the database while iterating the results of a query.

System.ArgumentException: Keyword not supported: '"server'.
at System.Data.Common.DbConnectionOptions.ParseInternal(Dictionary`2 
parsetable, String connectionString, Boolean buildChain, Dictionary`2 synonyms)
at System.Data.Common.DbConnectionOptions..ctor(String connectionString, Dictionary`2 synonyms)
at System.Data.SqlClient.SqlConnectionString..ctor(String connectionString)

If I put the connection string in appSettings.json, the production server works just fine.

So, here is an example of my appSettings.json file showing the connection string that works in production;

{
  "ConnectionStrings": {
     "TestDb": "Server=TestServer;Database=TestDb;Persist Security Info=True;User ID=TestUser;Password=testpassword;MultipleActiveResultSets=true"
  },

    ...
    ...
    ...
  }
}

If I deploy this appSettings.json file to production, it works OK.

In my ASP.Net Core application, in the Startup.cs file, I have the following;

public Startup(IHostingEnvironment env)
{
    var builder = new ConfigurationBuilder()
        .SetBasePath(env.ContentRootPath)
        .AddJsonFile("appsettings.json", optional: false, reloadOnChange: true)
        .AddJsonFile($"appsettings.{env.EnvironmentName}.json", optional: true);

    if (env.IsDevelopment())
    {
        // For more details on using the user secret store see https://go.microsoft.com/fwlink/?LinkID=532709
        builder.AddUserSecrets<Startup>();
    }

    builder.AddEnvironmentVariables();
    Configuration = builder.Build();

}

My understanding is that the last builder.add... listed has priority, so in my case, if a connection string exists in the environment, it should take priority over anything in appsettings.

So in production, if I use the following appSettings.config file;

{
  "ConnectionStrings": {
     "TestDb": "Placeholder for connection string. Overridden by User Secrets in Development and Environment Variables in Production. "
  },

    ...
    ...
    ...
  }
}

It should not matter what I have as a value for ConnectionStrings:TestDb in that appsettings.json file if I have an environment variable for the connection string.

Listed below is the environment variable I am using;

Variable                    Value
ConnectionStrings:TestDb    "Server=TestServer;Database=TestDb;Persist Security Info=True;User ID=TestUser;Password=testpassword;MultipleActiveResultSets=true"

However, when I use this setup, I get an error when I try to access the database indicating that it can't parse the connection string.

I have to assume that the problem is the way I have the connection string specified in the environment variable, but after quite a while searching online, I have not been able to find an example of exactly what the environment variable value should look like. For example, do I need to put leading and trailing single quotes around the entire string? Does individual sections of the connection string need single or double quotes?

Any help, such as an example of a proper connection string defined in an environment variable, would be greatly appreciated.

Solution

There is a typo/wrong value set in your connection variables.

Which can be seen in this output you pasted:

Variable                    Value
ConnectionStrings:TestDb    "Server=TestServer;Database=TestDb;Persist Security Info=True;User ID=TestUser;Password=testpassword;MultipleActiveResultSets=true"

This likely happend while setting the variable via

$env:ConnectionStrings:MyDb = """Server=..."""

the correct way is to set it without the quotation marks.

$env:ConnectionStrings:MyDb = "Server=..."

Old answer (for other users who may search for similar issues)

The convention for connection strings is SQLCONNSTR_, MYSQLCONNSTR_, SQLAZURECONNSTR_ and CUSTOMCONNSTR_ which are used by Azure Web Apps, but should also work for self-hosting, VMs or any other cloud provider.

So if you have an environment variable called CUSTOMCONNSTR_TestDb it will be the same as defining it in appsettings.json in

{
    "connectionStrings": {
        "TestDb": "..."
    }
}

It will also override the value inside it, if AddEnvironmentVariables() is called after .UseJsonFile(...). Last registration wins.

var builder = new ConfigurationBuilder()
    .SetBasePath(env.ContentRootPath)
    .AddJsonFile("appsettings.json", optional: true, reloadOnChange: true)
    .AddJsonFile($"appsettings.{env.EnvironmentName}.json", optional: true)
    // This one needs to be last
    .AddEnvironmentVariables();

You can also use other variables to override configuration values. iirc. ASPNETCORE_ is the default prefix (but you can change it in the AddEnvironmentVariables("MY_")).

So a ASPNETCORE_MySettings overrides Configuration["MySettings"] (or Configuration.Get("MySettings")) and ASPNETCORE_My__Settings (use double underscore for level hierarchy on Linux, read where : is used to obtain the config - Linux disallows colon in variable names) overrides Configuration["My:Settings"] so same as

{
    "my": {
        "settings": "..."
    }
}

Unless they changed that recently.

FWIW: Environment variables/configuration key names are case-insensitive as far as I remember.

Comments

[EiEiGuy]

Yes, I understand that, but what I am asking is about the environment variable, which in my setup, should take priority over anything else because the line... builder.AddEnvironmentVariables(); is the last one in the chain for builder. I want to use environment variables to avoid having to deal with a file contain the connection strings in production.

[EiEiGuy]

Thanks for the reply but but I would really prefer to use the environment variables, if possible, so that I do not have to worry about a managing a file on the production system. That is the reason for my post.

[EiEiGuy]

Tseng - So you are saying that if my appSettings.json file has a working connection string defined as "ConnectionStrings": { "TestDb": "Server=TestServer;Database=TestDb;Persist Security Info=True;User ID=TestUser;Password=testpassword;MultipleActiveResultSets=t‌​rue" ... I would name my environment variable "ASPNETCORE_TestDB" ? Or should it be named SQLCONNSTR_TestDb" ?

[Tseng]

It doesn't have to be defined in appsettings.json, but helps with testing to have development connection string there and override it via Environment variable, i.e. CUSTOMCONNSTR_TestDb)

[EiEiGuy]

OK, I tried setting the environment variable name to CUSTOMCONNSTR_TestDb as the variable and the same connection string I have been using and I still get the error listed at the start of my original post. The error message includes the string "System.ArgumentException: Keyword not supported: '"server'.", which makes me think that my actual connection string value is not formatted properly for use as the value of an environment variable.

[EiEiGuy]

OK, it seems as though the Environment variable ASPNETCORE_TestDb is not getting examined at all by my code. The error I was getting was from the fact that I had "ConnectionStrings": { "TestDb": "Placeholder for connection string. Overridden by User Secrets in Development and Environment Variables in Production. " } in my production appSettings.json file to alert anyone looking at the file that the connection string should be coming from the Environment variable.

[Tseng]

Not ASPNETCORE_TestDb, this would assume that your connection string is in the root element of the appsettings.json, but it's in the connection string, so it should be ASPNETCORE_ConnectionStrings__TestDb or ASPNETCORE_ConnectionStrings:TestDb on Windows, since it support : in environment variables. Last thing I found was this change/announcement about it being changed to ASPNETCORE_, but you can remove the prefix if you don't like it by .AddEnvironmentVariables("")

[EiEiGuy]

SOLUTION - I changed the system environment variable to SQLCONNSTR_TestDb and my code then parsed the value of the environment variable to the correct connection string and everything worked. Thanks, Tseng

[Tseng]

Since early beta days there have been plenty of changes to the way environment variables and configuration works, hard to track it on these days and the docs seem a bit lacking on this topic. I only found about CUSTOMCONNSTR_ & Co after deploying an ASP.NET Core app to Azure and found it mentioned in some of the azure tutorials, not in the official docs

[Tseng]

Yea, the SQLCONNSTR_ already implies setting a DbProvider as seen [here]( github.com/aspnet/Configuration/blob/rel/1.1.1/src/…)

[Alisson Reinaldo Silva]

So we can use either SQLCONNSTR_TestDb, CUSTOMCONNSTR_TestDb or ASPNETCORE_ConnectionStrings__TestDb, or even ASPNETCORE_ConnectionStrings:TestDb if Windows?

[Tseng]

@Alisson: Yea, though first two are prefered if you host on Azure App Service since they are predefined in the UI

[Alisson Reinaldo Silva]

@Tseng I used SQLCONNSTR_TestDb and it worked. However, since I'm using Docker, the app can't read from environment variables in the server. I could set env variables in the docker container, but the only way I found to do that is by configuring it in config file, but that would be pushed into my rep. So I'm not sure what to do, I'm still researching other approaches. Thank you anyway!!!

[Tseng]

@Alisson: You should really open a new question for that. Basically to pass a variable from the server to docker you just need to define it (without assigning a value), i.e. when doing docker run -e SQLCONNSTR_TestDb .... (note that the variable is not assigned) see docs or use --env-file parameter. also docs.docker.com/engine/swarm/secrets/… for passing secrets to containers

[Alisson Reinaldo Silva]

@Tseng I really appreciate your help. I opened a new question.

[Laszlo Pinter]

If you add or change an environment variable, restart Visual Studio for the change to take effect. It is not enough to restart the application.

[Kraego]

__ is now the platform agnostic seperator, see stackoverflow.com/a/67719140/11473934