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

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

We need a whole lot more information before we can help diagnose this. — sblom, Apr 04 '10 at 21:17
I had this issue and closed visual studio and opened it again and it started working.. — trees_are_great, Oct 31 '18 at 16:01

score 62 · Accepted Answer · edited Oct 11 '16 at 13:59

62

Well, the error is pretty clear, no? You are trying to connect to your SQL Server with user "xyz/ASPNET" - that's the account your ASP.NET app is running under.

This account is not allowed to connect to SQL Server - either create a login on SQL Server for that account, or then specify another valid SQL Server account in your connection string.

Can you show us your connection string (by updating your original question)?

UPDATE: Ok, you're using integrated Windows authentication --> you need to create a SQL Server login for "xyz\ASPNET" on your SQL Server - or change your connection string to something like:

connectionString="Server=.\SQLExpress;Database=IFItest;User ID=xyz;pwd=top$secret"

If you have a user "xyz" with a password of "top$secret" in your database.

edited Oct 11 '16 at 13:59

Aage

5,932
2
32
57

answered Apr 04 '10 at 21:16

marc_s

732,580
175
1,330
1,459

16

Not "is not allowed to connect to SQL Server", but "is not allowed to use database "test"". – wRAR Apr 05 '10 at 18:27
@wRAR: true - but I guess the chance the user login exists on the server, but isn't enabled in this particular database, is probably slim (at least I would think) – marc_s Apr 05 '10 at 18:33
See also "Database=IFItest" vs "test", though that may be a misprint. – wRAR Apr 05 '10 at 18:36
6

One point you never touched upon is that the DB Name could be wrong too. I was getting thrown a ditto looking exception message from my Azure SQL Server, and turned out I had given wrong name for my DB in one of the connectionstrings in the C# code in my ASP.NET project. – Ron16 Sep 28 '17 at 18:55
2

Ron, that was my issue. I had even double checked the spelling but missed it even then. – Darrell Lloyd Harvey Feb 06 '20 at 16:22
3

@DarrellLloydHarvey It is always funny when somebody, like in this answer, states "the error is pretty clear" and in reality, there are so often, so many edge cases in which error messages happen to be misleading. – Mateusz Stępniak Jan 20 '22 at 15:10
Thank you so much! I was going absolutely BONKERS trying to figure out why I could connect to a db from management studio, but my script was throwing this error (but I could connect to a different db on the same server, in the same script.) Answer: the underscore character is not the same as a dash! :D Was extremely confident it was a permission issue... it was not, it was a typo. – neminem Sep 08 '22 at 22:54

score 42 · Answer 2 · answered Apr 05 '10 at 10:21

42

Either: "xyz\ASPNET" is not a login (in sys.server_principals)
Or: "xyz\ASPNET" is set up but not mapped to a user in the database test (sys.database_principals)

I'd go for the 2nd option: the error message implies the default database is either not there or no rights in it, rather than not set up as a login.

To test if it's set up as a login

SELECT SUSER_ID('xyz\ASPNET') -- (**not** SUSER_SID)

If NULL

CREATE LOGIN [xyz\ASPNET] FROM WINDOWS

If not NULL

USE test
GO
SELECT USER_ID('xyz\ASPNET')

If NULL

USE test
GO
CREATE USER [xyz\ASPNET] FROM LOGIN [xyz\ASPNET]

answered Apr 05 '10 at 10:21

gbn

422,506
82
585
676

1

This is just great!, a simple workflow to detect and solve the problem. – Mazen el Senih Sep 10 '19 at 12:04
2

What table is "SELECT USER_ID(..." run against? Master? – Sam Oct 02 '20 at 15:00

score 20 · Answer 3 · answered May 14 '15 at 08:10

20

I had this problem and what solved it for me was to:

Go to the Application pools in the IIS
Right click on my project application pool
In Process Model section open Identity
Choose Custom account option
Enter your pc user name and password.

answered May 14 '15 at 08:10

ParPar

7,355
7
43
56

After so much headache, this was the answer that worked for me. Thank you! Just to add to step 2, `Right click on my project application pool` and click `Advanced Settings...`. – hijinxbassist Jun 30 '22 at 20:23

score 19 · Answer 4 · answered Feb 15 '18 at 08:25

19

For me the database was not created and EF code first should have created it but always endet in this error. The same connection string was working in aspnet core default web project. The solution was to add

_dbContext.Database.EnsureCreated()

before the first database contact (before DB seeding).

answered Feb 15 '18 at 08:25

cyptus

3,346
3
31
52

this is true especially when you are seeding data and have identity check activated – Ahmed Khalid Kadhim Sep 15 '21 at 10:28
This is it. I was running a project with EF Core Code First and thought it was making the "update database" command, creating and applying the pending migrations automatically and it wasn't. I fixed it applying the "dotnet ef database update" command – Gonzo345 Nov 25 '21 at 08:54
This was it! At least for my sample project. – Ioan Bucur Dec 01 '21 at 14:33

score 10 · Answer 5 · edited Jan 29 '14 at 05:28

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):

In SQL Server Management Studio, open Object Explorer and expand the folder of the server instance in which to create the new login.
Right-click the Security folder, point to New, and then click Login.
On the General page, enter the name of a Windows user in the Login name box.
Select Windows Authentication.
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.

score 9 · Answer 6 · answered Mar 26 '15 at 19:58

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

score 6 · Answer 7 · answered Mar 15 '16 at 18:16

6

This Works for me.

Go to SQL Server >> Security >> Logins and right click on NT AUTHORITY\NETWORK SERVICE and select Properties
In newly opened screen of Login Properties, go to the “User Mapping” tab.
Then, on the “User Mapping” tab, select the desired database – especially the database for which this error message is displayed.
Click OK.

Read this blog.

http://blog.sqlauthority.com/2009/08/20/sql-server-fix-error-cannot-open-database-requested-by-the-login-the-login-failed-login-failed-for-user-nt-authoritynetwork-service/

answered Mar 15 '16 at 18:16

Kamran

181
3
13

thanks, using "NT AUTHORITY\SYSTEM" solved my problem. – Farzad Karimi Dec 05 '19 at 09:19
1

thanks, it solved my issue. Additionally I have given permission for dbowner – Iswar Jan 15 '21 at 09:57

score 6 · Answer 8 · answered Dec 20 '17 at 07:14

6

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

answered Dec 20 '17 at 07:14

Wolf

6,361
2
28
25

1

Stupid mistakes are the most difficult ones. I my case the DB was detached. – IvanH Jun 10 '22 at 09:32

Anton Lyhin · Answer 9 · 2017-04-27T21:11:56.357

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)

Open SQL Server Management Studio
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).
Click "Connect"
Expand the "Databases" folder in the Object Explorer (View->Object Explorer, F8)
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..
Right click on the database then select "Tasks -> Detach...".
In the detach window, select your database in the list and check the column that says "Drop Connections"
Click OK.
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.
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

The problem is I can't even find that DB. – Shimmy Weitzhandler Nov 30 '17 at 17:50 — Shimmy Weitzhandler, Nov 30 '17 at 17:50

score 4 · Answer 10 · edited Sep 18 '13 at 07:42

4

I tried to update the user, and it worked. See the command below.

USE ComparisonData// databaseName
EXEC  sp_change_users_login @Action='update_one', @UserNamePattern='ftool',@LoginName='ftool';

Just replace user('ftool') accordingly.

edited Sep 18 '13 at 07:42

totymedli

29,531
22
131
165

answered Sep 18 '13 at 07:17

shashank

85
8

score 3 · Answer 11 · answered May 20 '21 at 20:41

I had this problem when I created a WPF .NET Core + Entity Framework Core project and then cloning it on a a new laptop.

Using:

update-database

in the package manager console simply solved it.

To open package manager console go to:

Tools-> Nuget Package Manager -> Package Manager console

score 2 · Answer 12 · edited Jun 28 '16 at 14:36

2

I used Windows authentication to connect to local database .mdf file and my local server was sql server 2014. My problem solved using this connection string:

string sqlString = " Data Source = (LocalDB)\\MSSQLLocalDB;" + "AttachDbFilename = F:\\.........\\myDatabase.mdf; Integrated Security = True; Connect Timeout = 30";

edited Jun 28 '16 at 14:36

Machavity

30,841
27
92
100

answered Jun 28 '16 at 14:34

Ehsan

21
2

I had to remove this part "User Instance=True" to make my local DB working – Anonymous Sep 26 '16 at 03:31

score 2 · Answer 13 · answered Sep 06 '19 at 07:46

In my case it is a different issue. The database turned into single user mode and a second connection to the database was showing this exception. To resolve this issue follow below steps.

Make sure the object explorer is pointed to a system database like master.
Execute a exec sp_who2 and find all the connections to database ‘my_db’. Kill all the connections by doing KILL { session id } where session id is the SPID listed by sp_who2.

USE MASTER;
EXEC sp_who2

Alter the database

USE MASTER;
ALTER DATABASE [my_db] SET MULTI_USER
GO

score 2 · Answer 14 · answered Jan 22 '20 at 02:07

I ran into this issue when attempting to write to the default database provided in the asp.net mvc template. This was due to the fact that the database hadn't been created yet.

To create the database and make sure that it is accessible follow these steps:

Open up the Package manager console in Visual Studio
Run the command "update-database"

This will create the database an run all the necessary migrations on it.

score 1 · Answer 15 · answered Jan 13 '14 at 16:40

I have not seen this mentioned in the previous issues, so let me throw out another possibility. It could be that IFItest is not reachable or simply does not exist. For example, if one has a number of configurations, each with its own database, it could be that the database name was not changed to the correct one for the current configuration.

Goodwill Monoto · Answer 16 · 2018-07-26T16:33:33.803

NB: If using a windows service to host the webservice.

You have to insure that your webservice is using the right Log on account to connect to SQL Server.

Open services(I assume the windows service has been install)
Right click on the service and goto properties.
Click on "Log On" Tab
Click on "This account" radio button
Click "Browse"
Enter Pc user name in Text Field and click "Check Name" Button to the right.
Click on text in Text Field, press "OK" button
enter login password and Apply

score 1 · Answer 17 · answered Jun 17 '19 at 18:01

1

Inspired by cyptus's answer I used

_dbContext.Database.CreateIfNotExists();

on EF6 before the first database contact (before DB seeding).

answered Jun 17 '19 at 18:01

Jesse Hufstetler

583
7
13

score 1 · Answer 18 · answered Jun 21 '19 at 06:26

1

If you haven't created the database in your server you will get the same login error.Make sure that the database exist before you login.

answered Jun 21 '19 at 06:26

AmirHossein Rezaei

1,086
1
16
20

score 1 · Answer 19 · answered Feb 10 '21 at 12:54

1

it's not a login issue most times. The database might not have been created. To create the database, Go to db context file and add this.Database.EnsureCreated();

answered Feb 10 '21 at 12:54

Gerald

31
3

score 0 · Answer 20 · answered Oct 26 '12 at 15:22

0

I found that I also had to set the UserMapping option when creating a new login and this solved the problem for me. Hope that helps anyone that also found themselves stuck here!

Edit: Setting the login as db owner solved the next problem, too

answered Oct 26 '12 at 15:22

DevDave

6,700
12
65
99

score 0 · Answer 21 · answered Apr 04 '10 at 21:24

0

The best option would be to use Windows integrated authentication as it is more secure than sql authentication. Create a new windows user in sql server with necessary permissions and change IIS user in the application pool security settings.

answered Apr 04 '10 at 21:24

Giorgi

30,270
13
89
125

score 0 · Answer 22 · answered Dec 07 '14 at 13:54

Some times this trouble may appear if you open this db in another sql server (as example, you launch sql managment studio(SMS) and add this db), and forget stop this server. As result - you app try to connect with user already connected in this db under another server. To fix that, try stop this server by Config. dispatcher sql server.

My apologies about bad english. Kind regards, Ignat.

score 0 · Answer 23 · edited May 23 '17 at 12:10

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.

score 0 · Answer 24 · answered May 05 '17 at 06:29

0

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'.

answered May 05 '17 at 06:29

Chris Halcrow

28,994
18
176
206

score 0 · Answer 25 · answered Jul 23 '19 at 20:24

In my case, I was running a Windows Service under "System" identity. The error was:

System.Data.SqlClient.SqlException (0x80131904): 
Cannot open database "MyDbName" requested by the login. The login failed.
Login failed for user 'MYDOMAINNAME\HOSTNAME$'.

The problem is that the error is very misleading. Even after I added 'MYDOMAINNAME\HOSTNAME$' login to the database, and granted this login sysadmin access and added a user for that login on my target database, and made that user dbowner, I was still getting the same error. Apparently I needed to do the same for 'NT AUTHORITY\SYSTEM' login. After I did that, I was able to login without a problem. I don't know why the error message complains about 'MYDOMAINNAME\HOSTNAME$'. I deleted that login and the corresponding user and everything still works.

score 0 · Answer 26 · edited Jul 16 '21 at 13:34

0

When using EF Code First, make sure the database exists. You could run the update-database command first.

edited Jul 16 '21 at 13:34

Grigory Zhadko

1,484
1
19
33

answered Jul 15 '21 at 17:31

sinetemba

33
8

score 0 · Answer 27 · answered Sep 01 '21 at 11:02

If none of the above solution is working.

I encountered with the same error message but my issue was completely different. I had just restore my database and the database was in restoring mode. So if your database is in rstoring mode just apply following query.

RESTORE DATABASE MyDatabase
   FROM DISK = 'pathToYourDbBackup\MyDatabase.bak'
   WITH REPLACE,RECOVERY

score 0 · Answer 28 · answered Nov 05 '21 at 12:43

0

I had this happen to me when I deleted the DB and forgot to recreate it. It happens sometimes, since Database folder needs refresh.

answered Nov 05 '21 at 12:43

GeorgiG

1,018
1
13
29

This is such a non-answer. And others already answered in the line of "check if the database exists". All too trivial to mention. – Gert Arnold Nov 05 '21 at 13:12

score 0 · Answer 29 · answered Jul 12 '22 at 15:12

0

If you didn't have any problems before and you get this error only in the package manager console, you don't need to do anything special. Just open the sql server object explorer window and connect and run the command again in the console.

answered Jul 12 '22 at 15:12

jafar

27
2
10

score 0 · Answer 30 · answered Apr 01 '23 at 16:18

I experienced this same error but I didn't need to create any password or username in sql. I just needed to fix my pyodbc.connect string.

'Driver={SQL Server};'
'Server=ExactServerName;'
'Database=ExactDatabaseName;'
'Trusted_Connection=yes;'

Once I passed it correct data it worked fine.

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

30 Answers30

The Issue

The Fix

Linked

Related