EF4 Code-First causes InvalidOperationException

Question

I'm having an issue when trying to run my project each time it builds. It seems the initializer runs, but when it comes to the first query - it dies with the following InvalidOperationException.

This operation requires a connection to the 'master' database. Unable to create a
connection to the 'master' database because the original database connection has
been opened and credentials have been removed from the connection string. Supply
an unopened connection.

For reference, I'm using the EF Code First CTP4, imported directly with NuGet. Connecting to a SQL Server 2008 R2

What I want to happen is to re-create the database if there are any model amendments and seed it with a few values for the lookup table. Both of these things seem to be supported* out of the box.

My setup is like so:

Global.asax

 protected void Application_Start()
 {
    Database.SetInitializer<CoreDB>(new CoreDBInitialiser());
    // et al...
 }

CoreDB.cs

public class CoreDB : DbContext
{
    public DbSet<User> Users { get; set; }
    public DbSet<Login> Logins { get; set; }
    public DbSet<Permission> Permissions { get; set; }
    public DbSet<Role> Roles { get; set; }
    public DbSet<RolePermission> RolePermissions { get; set; }
    public DbSet<UserRole> UserRoles { get; set; }
    public DbSet<Setting> Settings { get; set; }
}

public class CoreDBInitialiser : RecreateDatabaseIfModelChanges<CoreDB>
{
    protected override void Seed(CoreDB context)
    {
        var settings = new List<Setting>
        {
            new Setting
            {
                SettingName = "ExampleSetting",
                SettingValue = "This is a sample setting value",
            }
        };

        settings.ForEach(d => context.Settings.Add(d));
    }
}

When it runs, it dies on a line similar to this, which is basically the first query it comes across after creating the database.

User data = (from u in _data.Users where u.Username == userName  select u).SingleOrDefault();

Things I don't think it is:

• It's not permissions: I've deleted the actual database itself within the SQL Server. The application recreates it around about the same time as that query is attempted to run (the initializer is set, then obviously it holds off creating until it's needed). I've also logged on to SQL Server as the user that is specified in my Web.config and they have full read/write access to the database. In fact, they probably should do as that account creates the databases also.
• The database is being created: Deleting the DB and it automatically recreates fine.
• The connection string is correctly defined, including the providerName attribute.

<add name="CoreDB" connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=TheDatabase;User Id=TheUsername;Password=ThePassword;" providerName="System.Data.SqlClient" />

• It doesn't appear to be a bug in my code/logic, as once the query has failed successfully, the code will start properly until the next time the application is rebuilt. It's obviously could be, and likely I'd have to apply a work around in my code no matter what anyway. :)

What to do?

Ideally, I'd like to "not think about the database schema" much. I'd like it to be as it seemed to be in Scott Gu's excellent blog post (and follow up for working with existing databases) where things just worked out and disappeared away. For the most part this is true. It seems to be an issue with the connection not being closed at some point, but I can't find out how to rectify this issue.

A few forum / SO posts do imply the issue I'm having is basically because the initializers aren't working exactly as planned and the connection might be left open. The solution in other places appears to be to simply "don't create your own initializer", which isn't the greatest solution - but unless anyone has any ideas, I'll probably have to do until CTP5 maybe.

*yep, I know it's a CTP, so "supported" is probably not the word :)

Answer 1

I know that it is to late for answer, but this post is high on Goolge and answer may be useful for someone. The problem is missing credentials. To prevent this you need to change your connection string to have:

Trusted_Connection=False;Persist Security Info=True 

Based on this article

Answer 2

As requested I am posting my comment as an answer.

My solution was very similar to bizon where the Trusted_Connection and Persist Security Info needed correction but I accomplished it through visual studio properties by going to:

Server Explorer -> Modify Connection -> Advanced -> Then check both Persist Security Info and TrustServerCertificate as True, and it formatted the connection string correctly

Answer 3

Just run into this problem while I follow this under VS2012 and EF6. My solution is quite simple:

In the Connection Properties dialog which pops up when choosing "Reverse Engineer Code First", check "Save my password".

Problem solved, but I don't know the details about it...

Answer 4

Add this to your connection string. It worked for me.

Integrated Security=True;Persist Security Info=True;