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
This question is related to
c#
asp.net
sql-server
iis
web-services
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).
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.
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.
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.
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.
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
USE MASTER;
ALTER DATABASE [my_db] SET MULTI_USER
GO
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:
This will create the database an run all the necessary migrations on it.
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
I had this problem and what solved it for me was to:
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]
Inspired by cyptus's answer I used
_dbContext.Database.CreateIfNotExists();
on EF6 before the first database contact (before DB seeding).
This Works for me.
Read this blog.
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.
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.
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.
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";
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.
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 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)
The source of the solution: https://www.codeproject.com/Tips/775607/How-to-fix-LocalDB-Requested-Login-failed
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.
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
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):
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.
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
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'.
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();
Source: Stackoverflow.com