In SQL Server 2005 and beyond, a login failure will log in more details under the infamous message number 18456. This error includes various types of states that gives a hint on why a login failure happens. I'm posting a pool of all states that I've collected over the past years. Hopefully answer is found here without going anywhere further.
Error State Error Description
---------------- ---------------------------------------------------------------------------------------------------------------------
1, 58 An attempt to login using SQL authentication failed. Security is set up with
Windows authentication only but user is trying to use standard SQL login
2, 5 Invalid userid (eg. wrong login name or login name does not exist)
6 Attempt to use a Windows login name with SQL Authentication
7 Login disabled and password mismatch
8 Password mismatch (eg. wrong password)
9 Invalid password
10 This is a rather complicated variation on state 9; as KB #925744 states, this means
that password checking could not be performed because the domain account being
used for the SQL Server service is disabled or locked on the domain controller. No
reason is given in the verbose message in the error log.
11, 12 Valid login but server access failure
13 SQL Server service paused
16 User does not have permissions to log into the target database, such as drop user
from default db, or default db is not online
18 Change password required
21
23 The most common one is that connections are being attempted while the service is
being shut down. However, if this error occurs and it is not surrounded in the log by
messages about SQL Server shutting down, and there is no companion reason along
with the message, look at KB #937745, which implies that this could be the result of
an overloaded server that can't service any additional logins because of connection
pooling issues. Finally, if there *is* a companion reason, it may be the message
indicated to the right, indicating that SQL Server was running as a valid domain
account and, upon restarting, it can't validate the account because the domain
controller is offline or the account is locked or no longer valid. Try changing the
service account to LocalSystem until you can sort out the domain issues.
27 Server could not determine the initial database for the session
28 Involves overloaded connection pooling and connection resets.
38 Failed to open the explicitly specified database. the database specified in the client
connection does not exist, or is offline.
40 The login's default database is offline or no longer exists. Resolve by fixing the
missing database, or changing the login's default database using ALTER LOGIN
(for older versions, use sp_defaultdb (deprecated)). This is reported as state 16 prior
to SQL Server 2008.
56 Not very common. like states 11 & 12, this could have to do with UAC, or that the
domain controller could not be reached. Try changing the service account for SQL
Server to a known domain or local account, rather than the built-in local service
accounts.
65 User specified the correct username and contained database, but entered an
incorrect password. (Denali)
------------------------------------------------------------------------------------------------------------------------------------------
Please also refer to the following sites for more info:
https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-18456-database-engine-error
https://sqlblog.org/2011/01/14/troubleshooting-error-18456
