Microsoft sql server error 18456 login failed for user




In this blog post I will show you reasons why you might be getting SQL Server error 18456 and show you how you may be able to resolve it.

Contents

18456 Error overview

 Microsoft sql server error 18456 login failed for userWhen connecting to Microsoft SQL Server and trying to use usually SQL Authentication method you may get event ID error 18456 login failed for user you provided. See below example.


This usually means that your connection request was successfully received by the server name you specified but the server is not able to grant you access for a number of reasons and throws error: 18456. This eventID sometimes provides also state 1 which actually isn’t that useful as due to security reasons any error is converted to state 1 unless you can check logging on the server. Microsoft does not provide very useful message boxes so below are some explanations why you get the error.
 

Want to easily improve your SQL Server Skills?

Before you dive in

If you are NOT a DBA (Server Administrator) then read this.

We had a number of online private chats using our website chat feature and we have identified there are a number of situations where you cannot do anything about it! We are aware some of you might be very new to this so I will provide you with simple list of questions or steps to check if you are in the 'cannot do anything about it' group.

Before you start "fixing" it please go through the list below:

  1. Are you the person managing the server or the person who installed sql server (e.g. personal pc/laptop)?
    • If no or you don't know, then there is a high chance you don't have permissions to fix it. 
    • What does it mean? It means you can only do what you are in control of (typing username, password) or trying steps that don't involve changing server configuration. If nothing works contact your server administrator (DBA).
  2. Are you connecting from an app (Visual Studio etc.) and not SSMS?
    • If yes then try to connect from SSMS if it works then problem might be in the app.
    • If no then is there any user account that works?
      • Yes? 
        • Then username may not exist or might be mispelled.
        • Password might be incorrect.
        • Account may be disabled.
      • No?
        • Try next point.
  3. Using SQL Sever Management Studio (SSMS), Are you able to connect using Windows Authentication option (Not SQL Authentication, so without username and password)?
    • If yes then check if SQL Authentiction is enabled.
      • If yes then check if you can create a new SQL Account and test it out. If it doesn't work than maybe service wasn't restarted (see previous link). If it does work then problem is with the account you have (incorrect password or might be disabled?)
    • If no and you get this error:
      • EventID: 18456
        Login failed for user MYDOMAIN\MYLOGIN'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]
      • Than try to login using account that was used to install SQL Server and consider removing and adding back your login that doesn't work. For more info visit this forum thread.
  4. Does it work with new login?
    • If nothing worked then create a new login and connect using SSMS.
      • If works then problem is with your original login and not server config.
      • If doesn't work then problem is most likely with server (unrelated to login).

Final option is to log in to the server (if you have permissions and are allowed) and check state of the error. If you are DBA than this is also a good starting point :)

Our article did not help you but you have found a solution? Please let us know! so we can improve our article and help other people with the same error.

Potential causes

Below is a list of reasons and some brief explanation what to do:

SQL Authentication not enabled: If you use SQL Login for the first time on SQL Server instance than very often error 18456 occurs because server might be set in Windows Authentication mode (only).

How to fix? Check our SQL Server and Windows Authentication Mode page.


Invalid userID: SQL Server is not able to find the specified UserID on the server you are trying to get. The most common cause is that this userID hasn’t been granted access on the server but this could be also a simple typo or you accidentally are trying to connect to different server (Typical if you use more than one server)

Invalid password: Wrong password or just a typo. Remember that this username can have different passwords on different servers.

less common errors: The userID might be disabled on the server. Windows login was provided for SQL Authentication (change to Windows Authentication. If you use SSMS you might have to run as different user to use this option). Password might have expired and probably several other reasons…. If you know of any other ones let me know.

18456 state 1 explanations: Usually Microsoft SQL Server will give you error state 1 which actually does not mean anything apart from that you have 18456 error. State 1 is used to hide actual state in order to protect the system, which to me makes sense. Below is a list with all different states and for more information about retrieving accurate states visit Understanding "login failed" (Error 18456) error messages in SQL Server 2005

18456 Error State List

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

Hope that helps

Take care

Emil




Did you find this page useful? +23  |  -0
(23 Votes)

yes
Because I saw Kaie&Emil in my Google search. Regards, Conrad
to the point
Solved my Issue regarding SQL Server Authentication. I couldn't get the answer through multiple searches.
The troubleshooting steps fixed my issue. I did not have SQL Authentication on.
Pretty sure I found the solution to my problem..
it solved my issue with sql server