Cannot open database “test” requested by the login

Question

I have created a web service which is saving some data into to db. But I am getting this error:

Cannot open database "test" requested by the login. The login failed. Login failed for user 'xyz\ASPNET'.

My connection string is

Data Source=.\SQLExpress;Initial Catalog=IFItest;Integrated Security=True

Answer 1

It also happen when you type wrong name of DB

ex : xxx-db-dev to xxx-dev-db

Sometime, it's just a stupid mistake . I take about more than 1 hours to find out this :( because i just try alot of difficult thing first

Answer 2

In my case the asp.net application can usually connect to database without any problems. I noticed such message in logs. I turn on the SQL server logs and I find out this message:

2016-10-28 10:27:10.86 Logon       Login failed for user '****'. Reason: Failed to open the explicitly specified database '****'. [CLIENT: <local machine>]
2016-10-28 10:27:13.22 Server      SQL Server is terminating because of a system shutdown. This is an informational message only. No user action is required.

So it seems that server was restarting and that SQL server whad been shutting down a bit earlier then ASP.NET application and the database was not available for few seconds before server restart.

Answer 3

Most times, it's not a login issue, but an issue with creating the database itself. So if there is an error creating your database, it would not be created in the first place. In which case if you tried to log in, regardless of the user, login would fail. This usually happens due to logical misinterpretation of the db context.

Visit the site in a browser and REALLY read those error logs, this can help you spot the problem with you code (usually conflicting logic problems with the model).

In my case, the code compiled fine, same login problem, while I was still downloading management studio, I went through the error log, fixed my db context constraints and site started running fine....meanwhile management studio is still downloading

Answer 4

Even if you've set the login as DB owner and set the user mapping for the database that will use the login, check that the actual DB user (not just the login) has the role of 'owner'.

Answer 5

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 source of the solution: https://www.codeproject.com/Tips/775607/How-to-fix-LocalDB-Requested-Login-failed

Answer 6

The best solution for the login problem is to create a login user in sqlServer. Here are the steps to create a SQL Server login that uses Windows Authentication (SQL Server Management Studio):

1. In SQL Server Management Studio, open Object Explorer and expand the folder of the server instance in which to create the new login.
2. Right-click the Security folder, point to New, and then click Login.
3. On the General page, enter the name of a Windows user in the Login name box.
4. Select Windows Authentication.
5. Click OK.

For example, if the user name is xyz\ASPNET, then enter this name into Login name Box.

Also you need to change the User mapping to allow access to the Database which you want to access.