EF connection error underlying provider failed on open

Question

<add name="EXAMEntities2" connectionString="metadata=res://*/LiveModel.csdl|res://*/LiveModel.ssdl|res://*/LiveModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=xx.xx.xxx.xx;initial catalog=EXAM;user id=admin;multipleactiveresultsets=True&quot;" providerName="System.Data.EntityClient" />

this is my connectionsstring isnt it valid?

The " in there seems funky though perhaps needed for this type of connection? — Eric J., Jul 11 '12 at 19:48
Your question might be a duplicate of this http://stackoverflow.com/questions/2475008/the-underlying-provider-failed-on-open — HatSoft, Jul 11 '12 at 19:49
ı tired this one but same error.. provider connection string='data source=xx.xx.xxx.xx;initial catalog=EXAM;user id=admin;multipleactiveresultsets=True'" — Mehmet, Jul 11 '12 at 19:53
it is same with me ı looked msdn connectionstring.. copy and paste my first comment. ı tired that one.. — Mehmet, Jul 11 '12 at 19:59

score 1 · Accepted Answer · answered Jul 11 '12 at 20:01

You are specifying a user id but no password.

<add name="EXAMEntities2" connectionString="metadata=res://*/LiveModel.csdl|res://*/LiveModel.ssdl|res://*/LiveModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=xx.xx.xxx.xx;initial catalog=EXAM;user id=admin;password=*****;multipleactiveresultsets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

score 0 · Answer 2 · answered Jul 11 '12 at 20:06

I'm not a fan of storing the entire entity framework connection string in any .config file, it's so unreadable. So instead I wrote a method that turns the standard string into a valid entityframework connection string:

private static string GetEntityConnectionString(string connectionString, 
                                                Type contextType)
{
    string result = string.Empty;

    string prefix = contextType.Namespace
                               .Replace(contextType.Assembly
                                                   .GetName()
                                                   .Name, 
                                        string.Empty);

    if (prefix.Length > 0
        && prefix.StartsWith("."))
    {
        prefix = prefix.Substring(1, prefix.Length - 1);
    }

    if (prefix.Length > 1
        && !prefix.EndsWith("."))
    {
        prefix += ".";
    }

    EntityConnectionStringBuilder csBuilder 
      = new EntityConnectionStringBuilder();

    csBuilder.Provider = "System.Data.SqlClient";
    csBuilder.ProviderConnectionString = connectionString.ToString();
    csBuilder.Metadata = string.Format("res://{0}/{1}.csdl|"
                                       + "res://{0}/{1}.ssdl|"
                                       + "res://{0}/{1}.msl",
                                       contextType.Assembly.FullName,
                                       prefix + contextType.Name);
    result = csBuilder.ToString();
    return result;
}

Now you get a nice connection string in the config file:

<add name="MyDbConnectionname" 
     connectionString="Data Source=localhost\sqlexpress;Integrated Security=SSPI;MultipleActiveResultSets=true" 
     providerName="System.Data.SqlClient" />

And you call it like:

var db = new DbContext(
               DbContext.GetEntityConnectionString(connectionString,
                                                   typeof(DbContext))

ı know it is too late but ı saw your answer now.. This is great man thank you — Mehmet, Jul 26 '12 at 20:34

EF connection error underlying provider failed on open

2 Answers2