Login failed for User is a general sql error message and may have several causes. When a connection attempt is rejected because of an authentication failure that involves invalid credentials and this message is returned to the client. To increase security, the error message that is returned to the client deliberately hides the nature of the authentication error .
In Sql Server, user accounts and credentials are set at the server level, not at the database level. When you deploy an SQL database, only the database info moves over. You need to connect to the Database Server in your deployed environment , set up the username and password you want to use, and give the account appropriate permissions into the database. So, first you have to make sure that the user exist on SQL Server , that the user is enabled, and has access (mapped) to the correct database.
Integrated Security=False
In most cases, just set “Integrated Security=False” and it will work.
When set to Integrated Security=False , User ID and Password are specified in the connection. When true, the current Windows account credentials are used for authentication. If you want to use Windows Authentication for SQL Server, don’t specify User ID and Password in your connection string. Take those two parameters out completely. By adding those parameters in, SQL Server thinks you want to use SQL authentication instead. If you specify both Integrated Security=true as well as a user name and password, the user name and password will be ignored and Windows authentication will be used.
Mixed mode authentication
You can also get this error System.Data.SqlClient.SqlException: ‘Login failed for user , if your SQL Server that has not been configured to use Mixed mode authentication, it doesn’t actually tell you that this is not enabled!. Make sure that you have the Mixed-Mode authentication enabled and here is how to enable it.
- Open SQL Server Management Studio and right click on Server Node and select Properties.
- Now go to Security Tab and go to Server Authentical Area. Select SQL Server and Windows Authentication Mode.
- Click OK to close the Server Properties window.
- Right-click the SQL instance and choose Restart.
- Click Yes to restart the SQL server.
Integrated Security=SSPI
To connect using the Windows account of the currently logged in user, specify Integrated Security=SSPI and omit the User ID and Password. Integrated Security=SSPI (Security Support Provider Interface ) means that its using the windows account which is setup for the Anonymous User in IIS. For SQL authentication , specify a SQL login (which doesn’t include a domain) and password. Either one of three things you will need to do to correct this issue:
- Add the user used by the worker process to the SQL database and assign the appropriate permissions.
- Change the Connection String to use a predefined user name and password.
- Change the Anonymous authentication credentials or the credentials used by the worker process.
IIS and ASP.NET
If you want to access SQL Server Using Windows Integrated Security ; You should firstly make sure your IIS server and the sql server is inside the same intranet and use the same domain. Then you should make sure you have set the right application pool identity account which has the permission to access the sql database.
- Domain account “domain\account” in sql server to make sure it has the read and write permission.
In IIS, make sure that the Windows Authentication module is added and enabled. Also make sure your application pool is running under a domain account , not a local account.
In ASP.NET make sure the authentication mode attribute is set to “Windows” .
<system.web> <authentication mode="Windows"/> </system.web>
Conclusion
Hope you find benefit from above article and it can solve your problem. We will back with other interesting tutorial. Happy coding!
Andriy Kravets is writer and experience .NET developer and like .NET for regular development. He likes to build cross-platform libraries/software with .NET.
