How to fix "LocalDB - Requested Login failed"

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.