Click here to Skip to main content
12,760,251 members (32,489 online)
Click here to Skip to main content
Add your own
alternative version

Stats

43K views
7 bookmarked
Posted 19 May 2014

How to fix "LocalDB - Requested Login failed"

, 20 May 2014 CPOL
Rate this:
Please Sign up or sign in to vote.
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
Hi,

I am Edward Nutting, born March 1996 and I am currently a Computer Science and Electronic Engineering undergraduate at the University of Bristol, UK. I've been self-teaching programming since I was eight years old and have dabbled in many different areas from web development (where I had my own advertising startup) to low level operating system stuff (my current project Fling OS).

The articles and code on this profile are some of my early work (from when I was aged about 13 to 16) but despite my young age, it has been well received by CodeProject users. My more recent work has been for companies or on projects hosted on GitHub, CodePlex or BitBucket.

I keep an eye on many things techy and am open to any and all ideas, so please feel free to contact me if you think I can help! Smile | :)

Cheers,
Ed Nutting

You may also be interested in...

Pro
Pro

Comments and Discussions

 
QuestionFaild to login Sql server db 2008 r2 Pin
Member 1283445813-Dec-16 2:52
memberMember 1283445813-Dec-16 2:52 
Questionconnection string to generate the database in SQL Server Express under Windows Authentication Pin
Kelum Priyadarshane12-Sep-16 1:47
memberKelum Priyadarshane12-Sep-16 1:47 
SuggestionCannot open database requested by the login. The login failed. Login failed for user ‘FADAO. Pin
Fadao9-Jun-15 11:16
professionalFadao9-Jun-15 11:16 
SuggestionGood Article but a small suggestion Pin
Thava Rajan20-May-14 4:37
memberThava Rajan20-May-14 4:37 
GeneralRe: Good Article but a small suggestion Pin
Ed Nutting20-May-14 4:50
memberEd Nutting20-May-14 4: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.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170217.1 | Last Updated 20 May 2014
Article Copyright 2014 by Ed Nutting
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid