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.

How to *in-code..* , a nice wording of the question : ) – Irf Aug 08 '17 at 14:52 — Irf, Aug 08 '17 at 14:52

score 34 · Accepted Answer · edited Oct 05 '15 at 06:34

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
}

Thanks! The one piece that did not make this "Plug-and-play" was where to put this code. I finally realized that I needed to override the DataService CreateDataSource() — saunderl, Nov 17 '11 at 18:14
When you override the DataService CreateDataSource(), would it be public or private? — Jaiesh_bhai, Sep 25 '13 at 14:20

score 20 · Answer 2 · answered Sep 28 '15 at 21:26

20

I added a "dummy" password in the configuration file ("XXXXX"), then replaced that value with the real password in the entity constructor

public MyDatabaseContainer() : base("name=MyDatabaseContainer")
{
    Database.Connection.ConnectionString = Database.Connection.ConnectionString.Replace("XXXXX","realpwd");
}

answered Sep 28 '15 at 21:26

TheTall

298
3
7

1

thanks the solution .. I Use System.Environment.GetEnvironmentVariable to get the password for more secure – Abdu Imam Mar 03 '19 at 08:47

score 13 · Answer 3 · answered Dec 11 '13 at 08:21

Modify the constructor of the entities

 public sampleDBEntities() : base("name=sampleDBEntities")
    {
        this.Database.Connection.ConnectionString = @"Data Source=.\;Initial Catalog=sampleDB;Persist Security Info=True;User ID=sa;Password=Password123"; ;
    }

score 3 · Answer 4 · edited Jun 20 '15 at 12:24

My sample application was written in "Database First" mode and the "CreateNewConnectionString" method below works just fine (though it doesn't look all that elegant.)

The "CreateNewConnectionString2" method looks really elegant, BUT causes an exception telling me it's only valid in "Code First" mode.

So I'm providing both methods along with the constructor I modified to use my methods. NOTE AND BEWARE, I've modified code generated by a template and that is subject to being overwritten if the code is regenerated. To me it seems like the right place to put it.

If your application was generated in "Code First" mode, you may need to use "CreateNewConnectionString2" (I have not yet tested this option.)

I hasten to admit that I copied both code blocs from other postings as I don't yet know nearly enough about all this to write my own code.

private static string CreateNewConnectionString(string connectionName, string password)
        {
        var config = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("~").ConnectionStrings.ConnectionStrings[connectionName];
       //or:
       //var config = ConfigurationManager.ConnectionStrings[connectionName];
        var split = config.ConnectionString.Split(Convert.ToChar(";"));
        var sb = new System.Text.StringBuilder();

        for (var i = 0; i <= (split.Length - 1); i++)
        {
            if (split[i].ToLower().Contains("user id"))
            {
                split[i] += ";Password=" + password;
            }

            if (i < (split.Length - 1))
            {
                sb.AppendFormat("{0};", split[i]);
            }
            else
            {
                sb.Append(split[i]);
            }
        }
        return sb.ToString();
    }

    private static string CreateNewConnectionString2(string connectionName, string password)
    {
        var originalConnectionString = ConfigurationManager.ConnectionStrings[connectionName].ConnectionString;
        var entityBuilder = new EntityConnectionStringBuilder(originalConnectionString);
        var factory = DbProviderFactories.GetFactory(entityBuilder.Provider);
        var providerBuilder = factory.CreateConnectionStringBuilder();

        providerBuilder.ConnectionString = entityBuilder.ProviderConnectionString;

        providerBuilder.Add("Password", password);

        entityBuilder.ProviderConnectionString = providerBuilder.ToString();
        return entityBuilder.ProviderConnectionString;
    }

    public ChineseStudyEntities()
        : base(CreateNewConnectionString("ChineseStudyEntities", "put YOUR password here")) // base("name=ChineseStudyEntities")
    {
    }

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

4 Answers4

Linked