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

18456 Error overview

When connecting to Microsoft SQL Server Hosting 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.

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).

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.

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

I hope that helped you solve you 18456 error. If you know of another cause and solution than let us know and we will include it in the blog post.