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.
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 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
|State 2 and State 5
||Attempt to use a Windows login name with SQL Authentication
||Login disabled and password mismatch
|State 11 and State 12
||Valid login but server access failure
||SQL Server service paused
||Change password required
We need you!
By becoming a proud member we can:
Become a proud member
- Switch off annoying ads
- Create more articles and videos
- Develop more useful features supporting your learning
- Provide FREE webinars (for members)
- Feel our hard work since 2011 is appreciated
and get exclusive features.
If 2% of our 64,943 visitors (Last 30 days) become a proud member than we can quit our jobs and work full time on this website.
We started membership in May 2014 and so far 0.07% of our visitors are members and we would like to say THANK YOU to those who become a proud member and support our efforts.
Thanks to members:
- Education is for FREE and we create more FREE articles and videos
- We provide new features (members) that can speed up learning.
- 53% of our page views (82,000 each month) do not have page ads.
- We switched video ads from 14% of our videos (50 out of 345).
We have invested 4 years of our time in this website and we estimate it will take about 5 years before we can quit our jobs and dedicate all our time
to develop considerably our website which should help to truly achieve our vision of free education for everyone; all thanks to support of our members only.
Become a proud member