Finding more information about the SQL Server login error 18456: “Login failed for user NNN”

Sometimes, SQL Server login issues are hard to track down, but if you have access to the SQL Server's operating system and/or the Event Log stored there, you usually have the high ground.

Now, login issues usually start with the following error message, either from your application or from SQL Server's Management Studio:

Cannot connect to MYSERVERNAME.
Login failed for user 'MyUserName'.
(Microsoft SQL Server, Error: 18456)

Here, the key is the error text "Login failed for user" and the error code 18456. MSDN lists more information about this error here.

Now, the error message text is by design vague on the real error cause. But, if you can access the Windows Event Log of the server, go to the Application Log, and look for an event with following credentials:

Information	MSSQLSERVER	18456	Logon

This event provides the additional, often-needed information to really solve the issue at hand. For instance, if you tried to use an SQL Server account (SQL authentication) to connect to your SQL Server, the event log entry would read like this:

Login failed for user 'MyUserName'. Reason: An
attempt to login using SQL authentication failed.
Server is configured for Windows authentication only.
[CLIENT: clientmachinename]

That said, remember to check the Event Log if you get the SQL Server login error 18456 and the obvious – username and password – are indeed correct.