Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 387
EiEiGuy Avatar asked Jul 05 '17 16:07

EiEiGuy


People also ask

What is the best way to store the connection string in a web application?

The best way to secure the database connection string is to encrypt the value within the configuration file. The application would then load the encrypted value from the config file, decrypt the value, and then use the decrypted value as the connection string to connect to the database.

Where do I put connection string in ASP.NET Core?

The connection string should be added to your application's App. config file (Web. config if you are using ASP.NET). If your connection string contains sensitive information, such as username and password, you can protect the contents of the configuration file using Protected Configuration.

Where is ASP.NET connection string stored?

Connection strings can be stored as key/value pairs in the connectionStrings section of the configuration element of an application configuration file. Child elements include add, clear, and remove. The following configuration file fragment demonstrates the schema and syntax for storing a connection string.

How would you enable secret storage in an ASP.NET Core project?

Enable secret storage By default, the inner text of UserSecretsId is a GUID. The inner text is arbitrary, but is unique to the project. In Visual Studio, right-click the project in Solution Explorer, and select Manage User Secrets from the context menu.


1 Answers

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.

like image 75
Tseng Avatar answered Sep 19 '22 14:09

Tseng