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.
Contents
When 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.
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 Windows Authentication (only) is set in Server properties (security section).
To Access Server Properties, Open SSMS, go to Object Explorer pane (use view if you can't see it). Use the connect button to connect to database engine for your server. Once connected you will see it in object explorer. Right click server and click properties. Server Properties window will appear.
See below screenshot that might be causing SQL login to fail
You should set Server Authentication to SQL Server Windows Authentication Mode (see below). Once you select SQL Server Authentication you have to restart the server.
Restart required: You have to restart the service when you change server authentication but bear in mind if someone hasn't restart the service this might be the reason why you get 18456. Remember service restart should be carefully planned on production servers as it will clear certain information (cache) and may impact performance of the server.
NOTE: I recently tried to restart service using SSMS 2012 on Windows 7 and it didn't work.... actually the message below was hidden behind SSMS window and I just did not see it so ensure you get this message when you decide to restart the service.
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
|
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.
Take care
Emil
