c# - How to in-code supply the password to a connection string in an ADO.Net Entity Data Model

Question

I've been following this tutorial on how to create an OData service.

http://www.hanselman.com/blog/CreatingAnODataAPIForStackOverflowIncludingXMLAndJSONIn30Minutes.aspx

And it works flawlessly ... but, in the Entity Data Model Wizard, when it asks you to "Choose Your Data Connection" it gives you this warning.

"This connection string appears to contain sensitive data (for example, a password) that is required to connect to the database. Storing sensitive data in the connection string can be a security risk. Do you want to include this sensitive data in the connection string?"

If I choose: "No, exclude sensitive data from the connection string. I will set it in my application code."

I do not see where I can, "in my application code" insert the password. (My company stores them encrypted in the registry)

Plus, I have multiple DBs that I need to connect to, depending on the environment (Dev, CA, or Prod) and I need to know what DB is referenced in the connection string to get the correct password.

Thanks.

Answer 1

When you create your context, you can set a connection string. To build this connection string, you can parse the connection string without the password with an EntityConnectionStringBuilder and then parse the inner connection string with an other ConnectionStringBuilder, depending on your browser. Then you can set the password and pass it to the constructor.

var originalConnectionString = ConfigurationManager.ConnectionStrings["your_connection_string"].ConnectionString;
var entityBuilder = new EntityConnectionStringBuilder(originalConnectionString);
var factory = DbProviderFactories.GetFactory(entityBuilder.Provider);
var providerBuilder = factory.CreateConnectionStringBuilder();

providerBuilder.ConnectionString = entityBuilder.ProviderConnectionString;

providerBuilder.Add("Password", "Password123");

entityBuilder.ProviderConnectionString = providerBuilder.ToString();

using (var context = new YourContext(entityBuilder.ToString()))
{
    // TODO
}