Click here to Skip to main content
14,599,948 members

How to fix "LocalDB - Requested Login failed"

Rate this:
4.92 (6 votes)
Please Sign up or sign in to vote.
4.92 (6 votes)
20 May 2014CPOL
How to fix LocalDB / SQL Server: "Cannot open database "XYZ" requested by the login. The login failed. Login failed for user ABC"

Introduction

This snippet will briefly explain why the error occurs and the steps you can take to fix it. The error applies to SQL Server (Express or Full) local databases that are created through Visual Studio (usually seen as .mdf files).

Background

I was writing a compiler project that needed to produce a debug database so used Visual Studio's LINQ to SQl and LocalDB built-in tools to create the DB design and database (.mdf) file. After a couple of months the whole thing died and gave me various login error messages. None of these error messages could have been true since my Windows account had full access rights to the database and was an admin account.

After much searching I couldn't find any documentation matching my situation and just reading about how LocalDB's are set up. Turns out, they are installed as part of Visual Studio and run under SqlServerExpress. I presumed (correctly) that I could connect to the DB instance using SQL Server Management Studio and hey-presto I found the real cause of the issue, as described below.

The Issue

The error presents itself as a message similar to this:

Cannot open database "DATABASE NAME" requested by the login. The login failed. Login failed for user XYZ.

The error cannot usually be rectified by a simple Visual Studio or full-computer restart.

The error can also be found as a seemingly locked database file.

The Fix

The solution is laid in the following steps. You will not lose any data in your database and you should not delete your database file!

Pre-requisite: You must have installed SQL Server Management Studio (Full or Express)

  1. Open SQL Server Management Studio
  2. In the "Connect to Server" window (File->Connect object explorer) enter the following:
    • Server type : Database Engine
    • Server name : (localdb)\v11.0
    • Authentication : [Whatever you used when you created your local db. Probably Windows Authentication).
  3. Click "Connect"
  4. Expand the "Databases" folder in the Object Explorer (View->Object Explorer, F8)
  5. Find your database. It should be named as the full path to your database (.mdf) file
    • You should see it says "(Pending Recovery)" at the end of the database name or when you try to expand the database it won't be able to and may or may not give you an error message.
    • This the issue! Your database has crashed essentially..
  6. Right click on the database then select "Tasks -> Detach...".
  7. In the detach window, select your database in the list and check the column that says "Drop Connections"
  8. Click OK.
  9. You should see the database disappear from the list of databases. Your problem should now be fixed. Go and run your application that uses your localdb.
  10. After running your application, your database will re-appear in the list of databases - this is correct. It should not say "Pending recovery" any more since it should be working properly.

The (Underlying) Issue - more detail

The error message isn't totally wrong. It is correct that you can't login and that login failed but misleading since it does not inform you that the reason you can't login is because the database instance has crashed. When it does, SQL Server seems to have a bug which means it never gets out of the "Pending Recovery" state, instead it just locks the database file. This is the issue. By detaching the database, you unlock the file and SQL server closes the DB's process. When you next run your application, the database is seen as a "new" database, and so is re-attached with a new process and thus works properly.

Why on earth a database ends up in "Recovery Pending" state is still a mystery to me...

History

20/05/2014 - Initial version.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Ed Nutting
Student
United Kingdom United Kingdom
No Biography provided

Comments and Discussions

 
QuestionFaild to login Sql server db 2008 r2 Pin
Member 1283445813-Dec-16 1:52
MemberMember 1283445813-Dec-16 1:52 
Questionconnection string to generate the database in SQL Server Express under Windows Authentication Pin
Kelum Priyadarshane12-Sep-16 0:47
professionalKelum Priyadarshane12-Sep-16 0:47 
SuggestionCannot open database requested by the login. The login failed. Login failed for user ‘FADAO. Pin
Fadao9-Jun-15 10:16
professionalFadao9-Jun-15 10:16 
SuggestionGood Article but a small suggestion Pin
Thava Rajan20-May-14 3:37
professionalThava Rajan20-May-14 3:37 
GeneralRe: Good Article but a small suggestion Pin
Ed Nutting20-May-14 3:50
MemberEd Nutting20-May-14 3:50 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Tip/Trick
Posted 20 May 2014

Stats

91.7K views
7 bookmarked