EF6 migrations (LocalDb) update-database login failed
asp.net-mvc-5, entity-framework, entity-framework-migrations, localdb
Solution
If you delete the DB file, it still stays registered with SqlLocalDB. If LocalDb gets hung up with can’t attach (after manually deleting the DB file) or Login failed see JSobell’s and CodingwithSpike’s answers here:
ef5-cannot-attach-the-file-0-as-database-1.
Run ‘sqllocaldb.exe stop v11.0’ and ‘sqllocaldb.exe delete v11.0’ from the PM Console
UPDATE:
Easier yet use SSMS. Server name: (local)\v11.0, Windows Authentication. This is really the best way to manage your localdb databases.
Problem
VS 2013 MVC5 code first project. I’m working through the ASP.NET Getting Started with EF6 using MVC5 tutorial to get up to speed on the latest changes and I’m having a problem with migrations. In the first module the database is created using a database initializer: ``` <contexts> <context type="ContosoUniversity.DAL.SchoolContext, ContosoUniversity"> <databaseInitializer type="ContosoUniversity.DAL.SchoolInitializer, ContosoUniversity" /> </context> </contexts> ``` and this connection string: ``` <connectionStrings> <add name="SchoolContext" connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=ContosoUniversity1;Integrated Security=SSPI;" providerName="System.Data.SqlClient"/> </connectionStrings> ``` This works fine. In the Code First Migrations and Deployment module migrations are setup. The initializer is commented out and the name of the DB is changed to ContosoUniversity2: ``` <connectionStrings> <add name="SchoolContext" connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=ContosoUniversity2;Integrated Security=SSPI;" providerName="System.Data.SqlClient"/> </connectionStrings> ``` When `update-database` is then run from the Packager Manager Console it fails with the error message: ``` Cannot open database "ContosoUniversity2" requested by the login. The login failed. Login failed for user 'MyMachine\MyUser'. ``` I’ve re-run both scenarios several times with the same user and the same results. If I change `Initial Catalog` to `AttachDbFilename=|DataDirectory|\ContosoUniversity2;` `update-database` succeeds (the DB is now in the App-Data folder of the project rather the root of the users profile): ``` <connectionStrings> <add name="SchoolContext" connectionString="Data Source=(LocalDb)\v11.0; AttachDbFilename=|DataDirectory|\ContosoUniversity2;Integrated Security=SSPI;" providerName="System.Data.SqlClient"/> </connectionStrings> ``` You have to use `Initial Catalog` when deploying though. The production connectionString is set separately in the Web.Release.Config so that is workable. The question is why the need for the fix, why doesn’t `Initial Catalog` work with `update-database` on the development side? Update 1: The problem is not with migrations but with LocalDb `MyUser` has full rights in `SQLExpress` (`sysadmin`). I can log into `SSMS` under `MyUser` and fully manage `DBs`. I ran several tests. Even though I created the initial DB for the tutorial as `MyUser` it now throws the login failed error if I rebuild the app from scratch and use `databaseInitialzer`. If I run `VS` under `Admin` both `databaseInitialzer` and `update-database` work without any problem. If I then copy the `DBs` from the `Admin's` user profile root to `MyUser's` and then run `VS`, not as `Admin`, while logged on as `MyUser` both the `databaseInitialzer` and `update-database` then work if the `DB` is prior existing. If I change the `connectionString` to `AttachDbFileName` and run either `databaseInitialzer` and `update-database` `MyUser` can create a `DB` in the `App_Data` folder proving `MyUser` has `DB` create rights. `MyUser` obviously has full rights to the root of its own user profile. There is something amisss with the LocalDb implementation. Does any one have any insight on this question?