Weird Entity Framework error: login failed for user

Question

I have an application with multiple DbContext subclasses that share a connection string (this is to avoid EF's horrible startup time with a single large DbContext type). At certain points (repeatable for me and somewhat for others), I get the following error when trying to make a database query:

System.Data.EntityException: "The underlying provider failed on Open."
with inner exception:
System.Data.SqlClient.SqlException "Login failed for user 'username'"

The problem appears to go away if I switch back to using a single giant DbContext.

Does anyone know what this means/how to fix it? Would it help if I reused the same DbConnection object across DbContexts (as opposed to just using the same connection string)? This connection string has already succeeded in making several queries in the same request, so it can't be that the credentials are bad.

I am using ASP.NET MVC 3, EF 4.3.1, .NET 4.0, VS 2010.

The relevant stack trace is below:

 [SqlException (0x80131904): Login failed for user 'testing_net'.]
    System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +6351920
    System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() +412
    System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1363
    System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +53
    System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, TimeoutTimer timeout) +6366878
    System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, TimeoutTimer timeout, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +6366793
    System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean  redirectedUserInstance) +352
    System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +831
    System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +49
    System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +6368598
    System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +78
    System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +2194
    System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +89
    System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +6372110
    System.Data.SqlClient.SqlConnection.Open() +300
    System.Data.EntityClient.EntityConnection.OpenStoreConnectionIf(Boolean openCondition, DbConnection  storeConnectionToOpen, DbConnection originalConnection, String exceptionCode, String attemptedOperation, Boolean& closeStoreConnectionOnFailure) +67

 [EntityException: The underlying provider failed on Open.]
    System.Data.EntityClient.EntityConnection.OpenStoreConnectionIf(Boolean openCondition, DbConnection storeConnectionToOpen, DbConnection originalConnection, String exceptionCode, String attemptedOperation, Boolean& closeStoreConnectionOnFailure) +11108990
    System.Data.EntityClient.EntityConnection.Open() +142
    System.Data.Objects.ObjectContext.EnsureConnection() +97
    System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption) +57
    System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator() +47
    System.Linq.Enumerable.Single(IEnumerable`1 source) +156

A rough outline of the code is as follows:

// db context:
public class MyDbContext<T> : DbContext {
    public MyDbContext(string connectionString) : base(connectionString) { }

    protected override OnModelCreating(DbModelBuilder builder)
    {
        // constructs the model base on the type of T
        // code first POCO entities are annotated with an attribute that links
        // them to one or more types T
    }
}

I use AutoFac's DI to inject my DbContext's into the services my data access layer. The lifespan of the context is length of the HttpRequest.

The actual exception occurs on a call to Queryable.Single().

EDIT: I think this question might be relevant, but I'm not sure what to make of the race condition described.

EDIT: Now that I understand the issue, I can post the offending piece of code:

MyDbContext<T1> db1 = ...
var connectionString = db1.Database.Connection.ConnectionString; 
var dbContext2 = new MyDbContext<T2>(connectionString);

Answer 1

It turns out that the problem was related to the PersistSecurityInfo connection string property. From MSDN:

PersistSecurityInfo: Gets or sets a Boolean value that indicates if security-sensitive information, such as the password, is not returned as part of the connection if the connection is open or has ever been in an open state.

My connection string originally had a username and password in it. I would initialize a DbContext with this string, then later initialize a different context using the same string in the initial context's Database.Connection property. However, because PersistSecurityInfo was set to false, in some scenarios the password silently vanished from the connection's reference to the connection string, leading to login failure on the new DbContext instance.

The possible solutions I've thought of are to: 1. Set PersistSecurityInfo to true 2. Keep a separate reference to the connection string and use that 3. Use a different form of authentication that doesn't put the username and password in the connection string

Answer 2

As far as I know, the user should have some roles that are db_datareader, db_datawriter, db_ddladmin, db_owner or db_securityadmin. About more roles information, please refer to:

http://msdn.microsoft.com/en-us/library/ms189121(v=SQL.105).aspx

Answer 3

I had similar issue, I had forgotten to change the integrated security to false and add the local user id and password to the connection string in the web config file.