Unable to login to SQL Server + SQL Server Authentication + Error: 18456

Question

You need to enable SQL Server Authentication:

1. In the Object Explorer, right click on the server and click on "Properties"

2. In the "Server Properties" window click on "Security" in the list of pages on the left. Under "Server Authentication" choose the "SQL Server and Windows Authentication mode" radio option.

3. Restart the SQLEXPRESS service.

By default login failed error message is nothing but a client user connection has been refused by the server due to mismatch of login credentials. First task you might check is to see whether that user has relevant privileges on that SQL Server instance and relevant database too, thats good. Obviously if the necessary prvileges are not been set then you need to fix that issue by granting relevant privileges for that user login.

Althought if that user has relevant grants on database & server if the Server encounters any credential issues for that login then it will prevent in granting the authentication back to SQL Server, the client will get the following error message:

Msg 18456, Level 14, State 1, Server <ServerName>, Line 1
Login failed for user '<Name>'

Ok now what, by looking at the error message you feel like this is non-descriptive to understand the Level & state. By default the Operating System error will show 'State' as 1 regardless of nature of the issues in authenticating the login. So to investigate further you need to look at relevant SQL Server instance error log too for more information on Severity & state of this error. You might look into a corresponding entry in log as:

2007-05-17 00:12:00.34 Logon     Error: 18456, Severity: 14, State: 8.
or

2007-05-17 00:12:00.34 Logon     Login failed for user '<user name>'.

As defined above the Severity & State columns on the error are key to find the accurate reflection for the source of the problem. On the above error number 8 for state indicates authentication failure due to password mismatch. Books online refers: By default, user-defined messages of severity lower than 19 are not sent to the Microsoft Windows application log when they occur. User-defined messages of severity lower than 19 therefore do not trigger SQL Server Agent alerts.

Sung Lee, Program Manager in SQL Server Protocols (Dev.team) has outlined further information on Error state description:The common error states and their descriptions are provided in the following table:

ERROR STATE       ERROR DESCRIPTION
------------------------------------------------------------------------------
2 and 5           Invalid userid
6                 Attempt to use a Windows login name with SQL Authentication
7                 Login disabled and password mismatch
8                 Password mismatch
9                 Invalid password
11 and 12         Valid login but server access failure
13                SQL Server service paused
18                Change password required


Well I'm not finished yet, what would you do in case of error:

2007-05-17 00:12:00.34 Logon     Login failed for user '<user name>'.

You can see there is no severity or state level defined from that SQL Server instance's error log. So the next troubleshooting option is to look at the Event Viewer's security log [edit because screen shot is missing but you get the

idea, look in the event log for interesting events].

I had this same problem, however mine was because I hadn't set the Server authentication to "SQL Server and Windows Authentication mode" (which you had) I just wanted to mention it here in case someone missed it in your question.

You can access this by

• Right click on instance (IE SQLServer2008)
• Select "Properties"
• Select "Security" option
• Change "Server authentication" to "SQL Server and Windows Authentication mode"
• Restart the SQLServer service
  • Right click on instance
  • Click "Restart"

You can access this by

Right click on instance (IE SQLServer2008)
Select "Properties"
Select "Security" option
Change "Server authentication" to "SQL Server and Windows Authentication mode"
Restart the SQLServer service
    Right click on instance
    Click "Restart"

Just for anyone else reading this: This worked for me on 2012 SQL Server too. Thanks

After enabling "SQL Server and Windows Authentication mode"(check above answers on how to), navigate to the following.

1. Computer Mangement(in Start Menu)
2. Services And Applications
3. SQL Server Configuration Manager
4. SQL Server Network Configuration
5. Protocols for MSSQLSERVER
6. Right click on TCP/IP and Enable it.

Finally restart the SQL Server.