The client only sees ‘Login failed for user’. The reason is in the SQL Server error log, as a state number.

EXEC sp_readerrorlog 0, 1, N'Login failed';

What the state means

• State 2 or 5: the login does not exist. Wrong name, or a Windows user who is not in any group that has a login.
• State 6: a Windows login name was used with SQL authentication.
• State 7: the login is disabled and the password was also wrong.
• State 8 or 9: wrong password.
• State 11 or 12: the login is valid but server access failed, often a Windows login without CONNECT SQL permission, or denied access.
• State 18: the password must be changed.
• State 38: the database named in the connection string could not be opened (it doesn’t exist, is offline, or the login has no user in it). Error 4060 often appears alongside it.
• State 40: the login’s default database is unavailable.
• State 58: SQL authentication was used but the server only allows Windows authentication.

Quick checks

SELECT name, type_desc, is_disabled, default_database_name
FROM sys.server_principals
WHERE name = N'LoginName';

SELECT LOGINPROPERTY(N'LoginName', 'IsLocked') AS is_locked,
       LOGINPROPERTY(N'LoginName', 'BadPasswordCount') AS bad_password_count,
       LOGINPROPERTY(N'LoginName', 'PasswordLastSetTime') AS password_last_set;

-- 1 = Windows authentication only, 0 = mixed mode
SELECT SERVERPROPERTY('IsIntegratedSecurityOnly') AS windows_auth_only;

Unlock a locked SQL login (NEEDS APPROVAL)

ALTER LOGIN [LoginName] WITH PASSWORD = N'<your password here>' UNLOCK;

Repeated failures from one host straight after a password change usually mean an application, job or service is still using the old password. Find the host in the error log message and update it there, or the account will lock again.

From my SQL Server DBA handbook.