How to correctly store connection strings in envir

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.

Answer 1

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.

Answer 2

In your configuration you have this line:

.AddJsonFile($"appsettings.{env.EnvironmentName}.json", optional: true);

This is telling your configuration system that there might be a JSON file that exists with environment specific settings in it. So you only need a file that exists on the production box called appSettings.Production.config containing something like this:

{
  "ConnectionStrings": {
     "TestDb": "Server=...;Catalog=...;Etc=..."
  }
}

The values in here will override anything specified in the base JSON settings file.

Answer 3

if you don't want to store in appsettings, store it in the file C:\Windows\System32\inetsrv\config\applicationHost.config. It will be able to work.