EF6 migrations (LocalDb) update-database login failed
Solution 1
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.
Solution 2
I'm working with VS 2013 and EF6.1 .
I got similar kind of error when I work with migration and delete database. I was unable to recreate database again. Found that error due to SQLExpress local database instance is running on background and keeping the deleted database connection with it.
following are the steps to handle this situation.
- Delete the database files (.mdf and .ldf) files.
- Delete Migration Folder from your application.
- Delete any database connection to that database from server Explorer data connections.
- Go to windows Command Prompt (IF VS 2012 or earlier you have to use the VS Command prompt).
- Run "sqllocaldb.exe stop v11.0" (This will stop the v11.0 service)
- Run "sqllocaldb.exe delete v11.0" (This will delete the v11.0 service)
- Go to Package Manager Console in Visual studio.
- Run "Enable-Migrations" (This will create Migration folder and content)
- Run "Add-Migration init" (This will scaffold the changes to next migration)
- Run "Update-Database" (This will create the database again)
Solution 3
I change Standard Security to Trusted Connection
Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;
in appsetting.json you can write this code
`{
"ConnectionStrings": {
"onlineStoreDB": "Server=.;Database=onlineStore;Trusted_Connection=True"
},
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft": "Warning",
"Microsoft.Hosting.Lifetime": "Information"
}
},
"AllowedHosts": "*"
}`
It worked for me.
Solution 4
Exchange from connection string
Data Source=(LocalDb)\v11.0;
to
Server=.\SQLExpress;
This helped me for Code First technique during migration
Joe
Updated on July 28, 2022Comments
-
Joe over 1 year
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
toAttachDbFilename=|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 withupdate-database
on the development side?Update 1:
The problem is not with migrations but with LocalDb
MyUser
has full rights inSQLExpress
(sysadmin
). I can log intoSSMS
underMyUser
and fully manageDBs
. I ran several tests. Even though I created the initial DB for the tutorial asMyUser
it now throws the login failed error if I rebuild the app from scratch and usedatabaseInitialzer
. If I runVS
underAdmin
bothdatabaseInitialzer
andupdate-database
work without any problem. If I then copy theDBs
from theAdmin's
user profile root toMyUser's
and then runVS
, not asAdmin
, while logged on asMyUser
both thedatabaseInitialzer
andupdate-database
then work if theDB
is prior existing.If I change the
connectionString
toAttachDbFileName
and run eitherdatabaseInitialzer
andupdate-database
MyUser
can create aDB
in theApp_Data
folder provingMyUser
hasDB
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?
-
Annie Lagang about 7 yearsI had the same error when I was going through that tutorial. It was stated there in the first tutorial/initial steps the db will be recreated everytime the app is run and that will be changed in the next steps. For those who are still in the initial steps, all you have to do is to disconnect the LocalDb in the Server Explorer to be able to see the new email column in the Student table. But doing @Daniil Ivanov's suggestion above also works.
-
Dannie Vasileva about 2 yearsFixed My issue! Thank you.