Unable to open test database requested by login. Login failed. Login failed for user "xyz \ ASPNET"

Question

I created a web service that saves some data in db. But I get this error:

Unable to open database “test” requested by login. Login failed. Login failed for user "xyz \ ASPNET".

My connection string

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

Answer 1

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

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

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

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

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

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

Answer 2

• Or: "xyz \ ASPNET" is not a login (in sys.server_principals)
• Or: "xyz \ ASPNET" is configured but not displayed to the user in the database test (sys.database_principals)

I would choose the second option: the error message implies that the default database either does not exist or does not have rights in it, and is not installed as an input.

To check if it is configured as 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] 

Answer 3

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

• In SQL Server Management Studio, open Object Explorer and expand the server instance folder where the new login will be created.
• Right-click the Security folder, select Create, and then click Sign In.
• On the General page, enter your Windows username in the Username field.
• Select Windows Authentication.
• Click OK.

For example, if the username is xyz\ASPNET , enter that name in the Login Name box.

You also need to change the user mapping to allow access to the database that you want to access.

Answer 4

I had this problem and for me it solved:

• Go to application pools in IIS
• Right click on my project application pool.
• In the "Process Model" section, "Identification" is open
• Select "User Account"
• Enter the username and password of your PC.

Answer 5

In most cases, this is not a login problem, but a problem with creating the database itself. Therefore, if an error occurs while creating your database, it will not be created in the first place. In this case, if you tried to log in, regardless of the user, the login will fail. This usually happens due to a logical misinterpretation of the db context.

Visit the site in a browser and REALLY read these error logs, this will help you identify a problem with your code (usually conflicting logical problems with the model).

In my case, the code compiled in the order, the same registration problem, while I was still loading the management studio, I looked at the error log, fixed my db context restrictions, and the site started working fine ... meanwhile the management studio is still loading

Answer 6

For me, the database was not created, and the EF code first had to create it, but always Endet this error. The same connection string worked in aspnet's default web project. The solution was to add

 _dbContext.Database.EnsureCreated() 

before the first contact with the database (before sowing the database).

Answer 7

Problem

The error is a message similar to this:

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

• The error usually cannot be fixed with a simple reboot of Visual Studio or a full computer.
• The error can also be found as a locked database file.

Correction

The decision is made in the following steps. You will not lose data in your database, and you should not delete your database file!

Prerequisite: You must install 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: [Regardless of what you used to create the local db. Perhaps Windows Authentication).
• Click Connect
• Expand the Databases folder in Object Explorer (View-> Object Explorer, F8)
• Find your database. It should be called the full path to the database file (.mdf)
  • You should see that it says “(Pending recovery)” at the end of the database name or when you try to deploy the database, it cannot and may or may not give you an error message.
  • This is problem! Your database has crashed significantly.
• Right-click on the database and select "Tasks → Disconnect ...".
• In the disconnect window, select your database from the list and check the "Drop Connections" column
• Click OK.
• You should see the database disappear from the list of databases. Your problem should now be fixed. Run and run the application that uses your localdb.
• After starting the application, your database will again appear in the list of databases - this is correct. He should not say “Pending recovery” anymore, as he should work properly.

Solution source: https://www.codeproject.com/Tips/775607/How-to-fix-LocalDB-Requested-Login-failed

Answer 8

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.

Answer 9

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

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

Answer 10

In my case, this is another problem. The database went into single-user mode, and a second connection to the database showed this exception. To resolve this issue, follow these steps:

1. Ensure that the object explorer is directed to a system database, such as master.
2. Run exec sp_who2 and find all database connections' my_db. Kill all connections by running KILL { session id } , where the session ID is the SPID specified by sp_who2.

 USE MASTER; EXEC sp_who2 

3. Change database

 USE MASTER; ALTER DATABASE [my_db] SET MULTI_USER GO 

Answer 11

A better option would be to use integrated Windows authentication, as it is more secure than sql authentication. Create a new Windows user on the sql server with the required permissions and change the IIS user in the application pool security settings.

Answer 12

I have not seen this in previous releases, so let me release another opportunity. It is possible that IFItest unavailable or simply does not exist. For example, if you have several configurations, each with its own database, it may be that the database name has not been changed to the correct one for the current configuration.

Answer 13

This works for me.

• Go to SQL Server -> Security -> Logins and right-click NT AUTHORITY \ NETWORK SERVICE service and select "Properties"
• In the newly opened Login Properties screen, go to the User Mapping tab.
• Then, on the User Mapping tab, select the database you want — 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 /

Answer 14

This also happens when you enter the wrong DB name

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

Once this is just a stupid mistake. I take more than 1 hour to find out :( because I just try a lot of hard in the first place

Answer 15

Inspired by the cyptus answer I used

 _dbContext.Database.CreateIfNotExists(); 

on EF6 until the first contact with the database (until the database is full).

Answer 16

I found that I also had to set the UserMapping parameter when creating a new login, and this solved the problem for me. Hope this helps anyone who is here too!

Edit: setting login as db owner also resolved the following problem.

Answer 17

In some cases, this problem may occur if you open this db on another sql server (for example, you start sql management studio (SMS) and add this db) and forget to stop this server. As a result, you are trying to connect to a user already connected to this db under another server. To fix this, try stopping this server using Config. sql dispatcher server.

I apologize for the bad English. Best regards, Ignat.

Answer 18

In my case, the asp.net application can usually connect to the database without any problems. I noticed such a message in the magazines. I turn on SQL server logs and I recognize 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 the server is rebooting, and that the SQL server that used to close the bit, then the ASP.NET application and the database was not available for several seconds before the server was restarted.

Answer 19

Even if you set the login as the owner of the database and set the user mapping for the database that will use the login, make sure that the actual database user (and not just the login) has the "owner" role.

Answer 20

NB. When using a Windows service to host a web service.

You must ensure that your web service uses the correct login account to connect to SQL Server.

• Open Services (I assume a Windows service has been installed)
• Right-click on the service and go to properties.
• Click on the Login tab
• Click on the button "This account"
• Click Browse
• Enter your PC username in the text box and click the "Check Name" button on the right.
• Click on the text in the text box, click OK
• Enter login and password

Answer 21

If you did not create the database on your server, you will get the same error when logging in. Make sure the database exists before logging in.

Answer 22

In my case, I started Windows Service under the name "System". 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 the login 'MYDOMAINNAME \ HOSTNAME $' to the database, granted sysadmin access to this login, added the user for this login to my target database, and made this dbowner user, I still got the same error. Obviously, I needed to do the same to log into NT AUTHORITY \ SYSTEM. After I did this, I was able to log in without problems. I do not know why the error message complains about 'MYDOMAINNAME \ HOSTNAME $'. I deleted this login and the corresponding user, and it still works.