entity framework web config file

Question

This code works fine:

<connectionStrings>
    <add name="EFDbContext" connectionString="Data Source=.\SQLEXPRESS; Initial Catalog=myDB;Integrated Security=SSPI;
 " providerName="System.Data.SqlClient" />
  </connectionStrings>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="Data Source=.\SQLEXPRESS; Integrated Security=True; MultipleActiveResultSets=True" />
      </parameters>
    </defaultConnectionFactory>
  </entityFramework>

but this code doesn't work:

<connectionStrings>
    <add name="EFDbContext" connectionString="Data Source=.\MSSQLSERVER2008; Initial Catalog=myDb;Integrated Security=SSPI;User ID=useradmin; Password=pass; " providerName="System.Data.SqlClient" />
  </connectionStrings>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="Data Source=.\MSSQLSERVER2008; Integrated Security=True; MultipleActiveResultSets=True" />
      </parameters>
    </defaultConnectionFactory>
  </entityFramework>

The second code must be run on a remote server with an MSQSERVER2008 instance and when the page is loaded, the following message appears:

An error occurred while getting provider information from the database. This can be caused by Entity Framework using an incorrect connection string. Check the inner exceptions for details and ensure that the connection string is correct.

Answer 1

The problem is with the Integrated Security parameter. When it is set to True, .Net try to open the connection with the current user event if you specify a user and password. So to open the connection with a specific user, set the Integrated Security to False and it will work.

<connectionStrings>
    <add name="EFDbContext" connectionString="Data Source=.\MSSQLSERVER2008; Initial Catalog=myDb;Integrated Security=SSPI;User ID=useradmin; Password=pass; " providerName="System.Data.SqlClient" />
  </connectionStrings>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="Data Source=.\MSSQLSERVER2008; Integrated Security=False; MultipleActiveResultSets=True" />
      </parameters>
    </defaultConnectionFactory>
  </entityFramework>

Answer 2

Here's what I had to do to make EF connect using dynamic connection string settings, although my settings are user input, not web config:

        // Set the properties for the data source.
        sqlBuilder.ConnectionString = "Integrated Security=SSPI;";
        sqlBuilder.DataSource = serverName;
        sqlBuilder.InitialCatalog = databaseName;
        sqlBuilder.ConnectTimeout = 60;
        sqlBuilder.MultipleActiveResultSets = true;
        if(!string.IsNullOrEmpty(userName) || !string.IsNullOrEmpty(password))
        {
            sqlBuilder.UserID = userName;
            sqlBuilder.Password = password;
            //sqlBuilder.IntegratedSecurity = false;
        }
        else
        {
            sqlBuilder.IntegratedSecurity = true;
        }