Can I Add ConnectionStrings to the ConnectionStringCollection at Runtime?

Question

Is there a way where I can add a connection string to the ConnectionStringCollection returned by the ConfigurationManager at runtime in an Asp.Net application?

I have tried the following but am told that the configuration file is readonly.

ConfigurationManager.ConnectionStrings.Add(new ConnectionStringSettings(params));

Is there another way to do this at runtime? I know at design time I can add a connection string to the web.config; however, I'm looking to add something to that collection at run time.

Thanks

EDIT: One of the reasons why I'm attempting to do this is due to a security requirement that prevents me from placing ConnectionStrings in the web.config (even encrypted). I would like to use elements like Membership and Profiles on my project; however, I am looking into an alternative to doing such w/o writing a custom provider. Custom Provider's aren't all that bad, but if I can find an easier solution, I'm all for it.

Solution 1

var cfg = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration(@"/");
cfg.ConnectionStrings.ConnectionStrings.Add(new ConnectionStringSettings(params));

cfg.Save();

Be Advised this will cause your website to recycle since it modifies the config file. Check out http://msdn.microsoft.com/en-us/library/4c2kcht0(VS.80).aspx

Solution 2

You can use reflection to disable the private bReadOnly field (bad idea, etc.):

typeof(ConfigurationElementCollection)
    .GetField("bReadOnly", BindingFlags.Instance | BindingFlags.NonPublic)
    .SetValue(ConfigurationManager.ConnectionStrings, false);
ConfigurationManager.ConnectionStrings.Add(new ConnectionStringSettings());

This is similar to the technique required to modify an existing connection string, and added as a comment there by Brian Rodgers.

Solution 3

Just to point out whoever gave you the "security requirement" that you can't place the connection string in the web.config is an idiot. The web.config can never be accessed by anything other than your application or remote access to the server.

This sounds entirely like a requirements problem and should be solved there.

Solution 4

If you are trying to edit the connection strings in your web.config at runtime take a look at WebConfigurationManager.

If you are just trying to modify the configuration at runtime to inject a connection string then you are out of luck. The ConfigurationManager object tree is meant to be a direct reflection of the configuration files, if you were able to change that the state would then be inconsistent.

I would recommend creating a simple facade class that you could use to retrieve your connection strings. This way you could have the facade return a connection string from your on the fly collection or if one doesn't exist then it could grab it from the ConfigurationManager.

class ConnectionStringProvider
{
    Dictionary<string, System.Configuration.ConnectionStringSettings> _localStrings = new Dictionary<string, System.Configuration.ConnectionStringSettings>();

    public void AddLocalConnectionString(string name, string connstring)
    {
        System.Configuration.ConnectionStringSettings cs = new System.Configuration.ConnectionStringSettings(name, connstring);
        _localStrings.Add(name, cs);
    }

    public void RemoveLocalConnectionString(string name)
    {
        _localStrings.Remove(name);
    }

    public System.Configuration.ConnectionStringSettings this[string name] {
        get 
        { 
            return _localStrings.ContainsKey(name) ? _localStrings[name] : System.Configuration.ConfigurationManager.ConnectionStrings[name]; 
        }
    }
}

Or you could always go quite a bit heavier and use something like the Enterprise Library Configuration Block.

Solution 5

Haven't tried it yet, but perhaps you can alter the value of an existing connection string at runtime? For example, instead of:

ConfigurationManager.ConnectionStrings.Add(new ConnectionStringSettings(params));

Maybe you could do something like:

ConfigurationManager.ConnectionStrings["myconnection"].ConnectionString = "something";

If so, you could specify the connection string "variables" in config, but set them to false or empty connection strings:

<add name="myconnection" connectionString="SET AT RUNTIME" ... />

View more solutions

Comments

[Thomas Koelle]

I've tried on this one. That particular requirement came from the team in charge of DMZ security and doesn't want to risk having it exposed in case of a machine breech...even in an encrypted state since they would have the machine key at that point in a worst case scenario as well.

[JoshBerke]

Hmm if they have breached that box, they'll find your SQL server soon enough...you have to have the connection string on the server in some form or another...this is a silly requirement

[Chris Marisic]

I fully agree with Josh, once they root your webserver your database will be soon to follow. This is protected by proper intrusion detection systems that you have a change to disable your servers which is at that point the only fool proof solution to the database being compromised.

[joshperry]

I feel your pain, but the credentials of the ASP.NET user needs to have the right to whatever method your code uses to get the connection string. So even if your custom code is getting the connection string from a remote server or via some webservice, the hacker will be able to use the same method.

[user423430]

Modification requires reflection: david.gardiner.net.au/2008/09/programmatically-setting.html

[RickAndMSFT]

web.config is considered source code, and secrets should never be checked into source see Best practices for deploying passwords and other sensitive data to ASP.NET and Azure Websites asp.net/identity/overview/features-api/…

[Chris Marisic]

@RickAnd-MSFT that merely stores them in a separate config file. Nowhere did i say the connection strings must be recorded in your source control system.

[Chris Marisic]

–1 that's not true at all, it just causes you your site to recycle.

[Paťo Debute Krehák]

Ohh..come on!! Sure that you can it's an xml file, but is a very bad practice. .NET hasn't an API to modify the config files....ask yourself why. "-)

[RickAndMSFT]

separate config file virtually impossible to get into source control and our secrets out of source control. web.config is part of your source, are you saying it would not be under source control? The web.config with secrets can be accessed by everyone with access to your source control, which is why you move them out.

[Chris Marisic]

incorrect again, it absolutely has APIs for modifying the config files OpenWebConfiguration/Save stackoverflow.com/a/719941/37055

[Chris Marisic]

You act as if you can't have a build process that merges in config data from a walled garden. They built the entire config transformation concept to directly achieve this. This question was just worded very poorly as the accepted answer contradicts the premise of the question which is to directly add the data to the web.config (which further validates my answer, it's idiotic to not put them in the web.config). Ultimately no matter what, the keys will exist on disk somewhere at sometime. Likely somewhere at all times to allow builds without human intervention.

[Paťo Debute Krehák]

Didn't know that. The answer is quite old and anyway I think (IMHO) that is not a good practice to follow.

[Chris Marisic]

even the accepted answer here is OpenWebConfiguration, ironically i didn't scroll up before linking to another question. I certainly agree with you that everyone should not do this, but can't is an absolute. Absolutes are almost always wrong in every situation in software... unless it involves Oracle but I digress

[RickAndMSFT]

Who is they? MS security recommends the approach I've documented. On private disk is better than public source. Read my Best practices for deploying passwords and other sensitive data to ASP.NET and Azure Websites asp.net/identity/overview/features-api/… which has been vetted by the security team. Consider updating your answer which leaves leakage via GIT wide open.

[RickAndMSFT]

On my Azure build server, I use powershellcookbook.com/recipe/PukO/… to encrypt credentials to disk - but then I only have dev/test pw's, not production pw. Please update your response as your assertion "whoever gave you the "security requirement" that you can't place the connection string in the web.config is an idiot. The web.config can never be accessed by anything" is incorrect as it omits pw leakage via everyone with access to source, leakage via GIT, etc.

[RickAndMSFT]

Awesome hack! You are correct, this is a bad idea - but it does work for adding con strs. The motivation for the question is how to remove conStrs from web.config - which is a security best practice so the secrets don't leak out of the source code. See asp.net/identity/overview/features-api/… for how to move them out ( Best practices for deploying passwords and other sensitive data to ASP.NET and Azure Websites)

[RickAndMSFT]

The motivation for the question was moving the connection string out of web.config - which is a security best practice because you don't want to leak secrets to everyone with access to the source code. See Best practices for deploying passwords and other sensitive data to ASP.NET and Azure Websites asp.net/identity/overview/features-api/…

[Chris Marisic]

@RickAnd-MSFT absolutely not changing my answer. You're conflating two entirely separate issues. Source control containing the keys vs the web.config containing the key. No where does this question talk about keeping the keys out of source control it was purely about keeping them out of the web.config itself which is ridiculous. The accepted answer to the question merely puts them in the web.config. Methods for keeping the keys out of source control are wholly unrelated to what was specifically asked.

[RickAndMSFT]

Your assertion "The web.config can never be accessed by anything other than your application or remote access to the server." misses the leakage problem - no where do you point that out. No where do you say don't check web.config into source or the alternative use a transform that is not checked in. You are calling a security best practice "idiot" because you missed the leakage problem.

[Chris Marisic]

@RickAnd-MSFT once again for the thousandth time, OP never once asked about how to not check keys into source control. Maybe that's what he meant, who knows? I don't have a crystal ball. I answered the question that was asked.

[RickAndMSFT]

That's because he, like you, didn't know that leaks passwords. Now that you know it leaks passwords and you know your assertion "the "security requirement" that you can't place the connection string in the web.config is an idiot." is incorrect, why not update your answer. You should at least include, while IIS won't serve a .config file, you can leak PW's from source.Have you read my asp.net/identity/overview/features-api/… - and if so, what part of it don't you understand?

[RickAndMSFT]

@Chris Marisic you assert "Ultimately no matter what, the keys will exist on disk somewhere at sometime. Likely somewhere at all times to allow builds without human intervention." That's incorrect. I use powershellcookbook.com/recipe/PukO/… to store encrypted PW on disk for unattended builds.

[Chris Marisic]

@RickAnd-MSFT your assumptions are flatly wrong. Just stop. The OP specifically declined config encryption. Your solution of using Azure, guess what? Those keys are sitting on disk in Azure somewhere (hopefully encrypted).... "to allow builds without human intervention". You're conflating unrelated things over and over and over again. No where did I say unencrypted keys either. No matter how you do key management a person with sufficient permissions always has access. Even with config encryption the person who can pull the cert out of the box can decrypt the keys.