.svg)
.svg)
.svg)
.svg)
.svg)
.svg)
.svg)
Updated and tested 16 October 2026
The API went down and the only thing its logger produced was Login failed for user 'apiuser'. (Microsoft SQL Server, Error: 18456). That message names the user and the error number, and that is the whole of it. Your client library isn't withholding that detail. SQL Server never sent it, and that one design decision is why every 18456 investigation starts in the same place: the server error log.
MSSQL error 18456 covers an enormous range of failures. The login failed for user SQL Server writes to the client is the same string for a wrong password, a disabled login, an offline database, the wrong authentication mode, a Microsoft Entra token that didn't validate or an empty user name from a misconfigured connection pool. Roughly two dozen documented states sit behind that one message, so guessing during an outage is wasted time. Get the state, then fix the thing the state names.
SQL Server error 18456 login failed for user, client side and server side
The client side of the failure is this, in full:
Login failed for user 'apiuser'. (Microsoft SQL Server, Error: 18456)Expand the detail in SSMS and you pick up one extra token, State: 1. That value is a decoy. State 1 is what the engine reports to every client on every authentication failure regardless of the real cause, and Microsoft's reference page for MSSQLSERVER_18456 says so outright: "To increase security, the error message that is returned to the client deliberately hides the nature of the authentication error."
On the server the same failure is written as a pair of lines, and the second one carries the client IP so you can tell which machine tried:
2007-12-05 20:12:56.34 | Logon | Error: 18456, Severity: 14, State: 8.
2007-12-05 20:12:56.34 | Logon | Login failed for user '<user_name>'. [CLIENT: <ip address>]State 8 there is a bad password. Severity 14 never changes for this error, so ignore it. The state number is the entire payload of the message, and everything below depends on having read it.
Find the SQL Server error log
Error log path on Linux
The Database Engine writes to /var/opt/mssql/log/errorlog, and the directory is root-only, so you need sudo even to list it:
sudo cat /var/opt/mssql/log/errorlogIf that comes back as spaced-out garbage with a null byte between every character, the file is UTF-16LE and your terminal is reading it as UTF-8. Don't fight it. Convert a copy:
sudo iconv -f UTF-16LE -t UTF-8 /var/opt/mssql/log/errorlog -o /tmp/errorlog.txt
grep -a "Error: 18456" /tmp/errorlog.txt | tail -20On any box where I've done an SQL Server install on Ubuntu, that grep sits in my shell history. The last twenty failures are almost always enough to see a pattern.
Error log path on Windows
On Windows the log lives under the instance directory, and the number in the folder name tracks the major version:
C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOGMSSQL17 is SQL Server 2025, MSSQL16 is 2022, MSSQL15 is 2019, MSSQL14 is 2017. For a named instance the second half of the folder name carries the instance name, so a default SQLEXPRESS install on 2022 lands at MSSQL16.SQLEXPRESS. Open ERRORLOG with any editor that handles Unicode. It has no extension, and the archived copies are ERRORLOG.1 through ERRORLOG.6.
Six archives is the default and a new log opens on every service restart, which means a box that has been up for a year keeps the whole year in one enormous file. Nobody I have asked knows why six. I have never changed it. If you would rather stay in a GUI, the same file shows up under Management, SQL Server Logs, once the console is pointed at the instance (SQL Server Management Studio setup guide).
Error 18456 state codes
These are the states Microsoft documents. Anything outside the list is, in the reference page's own words, "an unexpected internal processing error", which in practice means open a support case or start reading the log around the failure for context.
States 2 and 5: The user ID isn't valid
No login by that name exists on the instance. The two states mean the same thing and the split between them is historical. Look for a domain prefix that shouldn't be there, a trailing space picked up from a config file or a login that exists on your staging server and was never scripted out to production.
States 6, 58 and 62: Windows and SQL authentication are crossed
State 6 means a Windows login name was passed on a SQL Server Authentication connection. State 58 means the server is set to Windows Authentication only and a client tried SQL authentication, or that SIDs don't match. State 62 is a Windows account hitting a contained database where the SIDs don't line up. All three are the same class of problem. On Windows the fix is usually mixed mode.
Check which mode you're in before changing anything:
SELECT SERVERPROPERTY('IsIntegratedSecurityOnly') AS IsWindowsAuthOnly;1 means Windows Authentication only. 0 means both modes are accepted.
States 7, 8 and 9: Password problems, and a disabled login
State 8 is a wrong password. State 9 means the password isn't valid. State 7 means the login is disabled and the password is incorrect, so re-enabling the login on its own won't get you in.
State 18: The password must be changed
The login was created or altered with MUST_CHANGE, or password expiration caught up with it. Some client drivers can prompt for a new password, most can't and a connection pool never will. Reset it from another session with ALTER LOGIN.
States 11 and 12: Login valid, server access failed
Both states mean the credentials were fine and the engine then refused the session. The common case is a Windows user who is a sysadmin only through membership of the local Administrators group, on a box where UAC isn't elevating the client. There's a section on that further down.
States 38, 46 and 126: The database can't be found
States 38 and 46 mean the database requested by the user couldn't be found. State 126 means it doesn't exist. In practice this is nearly always a login whose default database has been dropped, renamed, taken offline for a restore or set to a database the login has no user in. Fix the login. On some drivers a connection string that names a database explicitly still fails when the default database is unreachable.
An older Microsoft blog, now in the Learn archive, also documents state 40 as "Failed to open the database specified in the login properties". It isn't on the current reference page, so treat it as archived guidance. The fix is identical to states 38 and 46.
States 102 to 111 and 132 to 133: Microsoft Entra failures
These are token and directory failures, so nothing you do with ALTER LOGIN will help. Look at the identity provider side: token lifetime, tenant, conditional access, the authentication mode your driver negotiated. I have only ever chased one of these as far as the token lifetime, so take the rest of that list as what the documentation says and not as something I have watched fail.
States 122 to 124: Empty user name or password
An empty credential reached the server. Print the string your app builds at runtime with the password masked, because the bug is almost always in how the MSSQL connection string gets assembled and not in what someone typed. I have seen this once from a human and dozens of times from an application that read a blank environment variable and connected anyway.
State 1 and undocumented states
State 1 in the server log (not the client message) means the error information isn't available, usually because the account reading the detail lacks permission. Other numbers show up occasionally. State 142 circulates on forums with several contradictory explanations attached, none of them documented by Microsoft.
Switch to mixed mode authentication on Windows
If the log gave you state 58 and you need SQL logins to work, the instance is running in Windows Authentication mode and has to be changed. In SSMS, right-click the server in Object Explorer, open Properties, go to the Security page and pick SQL Server and Windows Authentication mode. The equivalent from T-SQL writes the registry value directly:
EXECUTE xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2;LoginMode 2 is mixed, 1 is Windows only. Both routes need a service restart before anything changes. The setting is read at startup, so until net stop MSSQLSERVER and net start MSSQLSERVER have run you keep getting state 58 against a server that looks correctly configured in the GUI. Both paths sit on Microsoft's page for changing the server authentication mode.
Enable the sa login and reset its password
A SQL Server sa login failed on a fresh Windows box usually means sa was never enabled at all. Installing in Windows Authentication mode leaves it disabled, and turning mixed mode on later doesn't enable it for you. Two statements, in this order:
USE [master];
GO
ALTER LOGIN sa ENABLE;
GO
ALTER LOGIN sa WITH PASSWORD = '<enterStrongPasswordHere>';
GOThe password policy is inherited from Windows on Windows hosts: at least eight characters, drawing on three of the four sets (uppercase, lowercase, base-10 digits, symbols), up to 128 characters. A server with remote connections enabled on SQL Server starts seeing sa attempts soon after going live, because sa is the one login name every scanner on the internet tries. Get an application off it as soon as you can, then disable it again.
Unlock a login and clear an expired password
Repeated bad passwords against a login with CHECK_POLICY on will trip the Windows account lockout policy, and the login stays locked until you clear it. If you know the new password:
ALTER LOGIN [Mary5] WITH PASSWORD = '<enterStrongPasswordHere>' UNLOCK;
GOIf you want to unlock without touching the password, toggle the policy off and back on. It looks like a hack and it's the documented method:
ALTER LOGIN [Mary5] WITH CHECK_POLICY = OFF;
ALTER LOGIN [Mary5] WITH CHECK_POLICY = ON;
GOCHECK_POLICY defaults to ON and CHECK_EXPIRATION defaults to OFF, which surprises people who assume expiry is on by default. For a service account that must never expire mid-deployment, set CHECK_EXPIRATION = OFF explicitly and rotate the password on your own schedule.
Set a default database the login can open
States 38, 46 and 126 usually resolve to a login pointed at a database that has gone. Repoint it at something that will always be there:
ALTER LOGIN [apiuser] WITH DEFAULT_DATABASE = [master];
GOThen work out why the real database is unavailable. SELECT name, state_desc, user_access_desc FROM sys.databases; answers that in one line: ONLINE and MULTI_USER is healthy, RESTORING or OFFLINE or SINGLE_USER is your problem.
Create a login and map it to a database user
The fix I see done badly most often is granting sysadmin because the mapping is fiddly. Don't. A login is a server-level principal, a user is a database-level principal, and you need both:
USE [master];
GO
CREATE LOGIN [apiuser] WITH PASSWORD = '<enterStrongPasswordHere>', DEFAULT_DATABASE = [orders];
GO
USE [orders];
GO
CREATE USER [apiuser] FOR LOGIN [apiuser];
GO
ALTER ROLE db_datareader ADD MEMBER [apiuser];
ALTER ROLE db_datawriter ADD MEMBER [apiuser];
GOSkip the CREATE USER step and the login authenticates fine, then fails the moment it touches a table. That's error 916, filed with the other messages that look like 18456 and aren't on the SQL Server troubleshooting cheat sheet. It is also why people wrongly conclude their 18456 fix didn't work.
