Helpdesk: "Since this morning's release, the Finance portal on WEB02 shows 'Login failed for user 'finance_app'. (Microsoft SQL Server, Error: 18456)'. The vendor says to switch SQL01 to mixed mode and enable sa so they can test from their side. Can we just do that so the portal comes back?"
Don't enable sa yet. Error 18456 tells you SQL Server refused a login. The reason is in the server's error log, not in the message the app shows. Microsoft: "the error message that is returned to the client deliberately hides the nature of the authentication error. However, in the SQL Server error log, a corresponding error contains an error state that maps to an authentication failure condition." Turning on mixed mode and sa changes the server's attack surface and probably doesn't touch the real cause. It's one layer in a documented chain: the exact message, login name, instance, and time on the ticket → the error log line with the state and the Reason: text → the authentication type matches the server's authentication mode → the login exists, is enabled, and isn't locked, expired, or waiting on a password change → a Windows login has a server access path (explicit login or group) with no DENY in the way → the database the login asks for is available and has a user mapped to it → the Windows, Kerberos, and NTLM layer delivered a real identity → special identities (machine accounts, app pools, NT AUTHORITY\SYSTEM, Entra tokens) are the ones you granted. sp_readerrorlog, Get-SqlErrorLog, SERVERPROPERTY, sys.server_principals, sys.sql_logins, LOGINPROPERTY, xp_logininfo, sys.server_permissions, sys.databases, sys.database_principals, sys.dm_exec_connections, and setspn -Q are the sources for that chain. A password reset or a new sysadmin grant can make a test pass for the wrong reason. If the real problem was a dev connection string pointing at production, or a default database that's offline, the next release breaks the same way.
If the database behind the login is also refusing writes, read SQL Server Transaction Log Full (Error 9002) first. If Windows logins fail with an empty user name and the SQL host is having domain trouble, read Trust Relationship Between This Workstation and the Primary Domain Failed. This post covers "an app, job, or person gets 'Login failed for user' (error 18456, often with severity 14 and SQLSTATE 28000), and you need a read-first path through the error log state, the authentication mode, the login, the server access path, the database, and the Windows authentication layer before you enable sa, switch authentication modes, reset passwords, grant sysadmin, or register SPNs."
TL;DR
- The client message hides the reason on purpose. Microsoft: the state in the SQL Server error log "maps to an authentication failure condition." Read the log before you change anything.
- The name in quotes tells you which kind of login failed. Microsoft: "If the domain name isn't specified, the problem is a failing SQL Server login attempt. If the domain name is specified, the problem is a failing Windows user account login."
- Severity 14 is the security class, not a crash. Microsoft's severity table: 14 "Indicates security-related errors, such as permission denied."
-
Mixed mode needs a restart, and
sastays disabled. Microsoft: changing the mode requires restarting SQL Server, and if you switch from Windows Authentication mode, "thesalogin remains disabled." -
State 40 is a database problem. Microsoft's 4064 page logs
Error: 18456, Severity: 14, State: 40with "Failed to open the database ... specified in the login properties." Fixing the default database fixes the 18456. - If the TCP connection works, it's not the network. Microsoft: "Authentication problems appear after the TCP connection succeeds, as sign-in failures or SSPI errors."
Where in the chain did it break?
| Step | What should be true | Typical symptom when it breaks |
|---|---|---|
| 1. Symptom captured | Exact message, login name in quotes, instance, client host, time, what changed | "SQL is down" with no login, server, or time |
| 2. Error log read | The matching Error: 18456 line, its state, and the Reason: line |
Arguing about the client message instead of the log |
| 3. Authentication mode | SQL logins only when the server allows them; Windows names only with Windows authentication | State 58 or 6; sa and mixed mode requested first |
| 4. The login | Exists on this instance, enabled, not locked, not expired, no pending password change | State 2, 5, 7, 8, or 18 |
| 5. Server access path | Windows login granted directly or through a group, no DENY on connect | State 11 or 12; works for admins, not for users |
| 6. Database | Default or requested database online, accessible, user mapped | State 38 or 40; error 4064; orphaned user after a restore |
| 7. Windows authentication layer | Kerberos or NTLM delivered the real user |
NT AUTHORITY\ANONYMOUS LOGON, empty name, (null), error 18452 |
| 8. Special identities | The login granted matches the identity that actually connects |
IIS APPPOOL\..., CONTOSO\WEB02$, NT AUTHORITY\SYSTEM, token-identified principal |
| 9. Change | Only after Steps 1-8 agree: one layer, one change |
sa enabled, app still fails |
Step 1: Capture the exact message (read-only)
Microsoft's MSSQLSERVER_18456 page: event ID 18456, source MSSQLSERVER, symbolic name LOGON_FAILED, message text Login failed for user '%.*ls'.%.*ls. You get it "when a connection attempt is rejected because of an authentication failure." Write these on the ticket before anyone changes anything:
| Fact | Why it matters |
|---|---|
| The full message, including the name in quotes | Tells you SQL login, Windows login, machine account, or something stranger (table below) |
| Server and instance the client used | Microsoft lists DEV or QA connection strings deployed to production as a common cause |
| Client host and app | A web app, scheduled task, or SSIS package often runs as a different identity than the person reporting it |
| Exact time, with seconds if possible | You'll match it to the error log line in Step 2 |
| SQLSTATE, if the app shows one | ODBC defines 28000 as "Invalid authorization specification." Our reading: the driver is passing on an authentication refusal, so the network path worked |
| Who else can connect | Everyone failing points at the server; one app failing points at that app's login |
| What changed? | Release, password rotation, database restore or move, new app pool, service account change, domain move |
The name inside the quotes is your first branch:
| Name in the message | What it usually means | Start at |
|---|---|---|
finance_app (no domain) |
A SQL Server authentication login (Microsoft) | Steps 2-4 |
CONTOSO\jdoe |
A Windows login (Microsoft) | Steps 2, 5 |
NT AUTHORITY\ANONYMOUS LOGON |
Microsoft: double-hop delegation, SPN problems, or loopback protection | Step 7 |
'' (empty) |
Microsoft: SQL Server couldn't hand the credentials to LSASS, or no domain controller could be contacted | Step 7 |
(null) |
Microsoft: LSASS couldn't decrypt the token; "the SPN is associated with the wrong account" | Step 7 |
CONTOSO\WEB02$ |
A computer account. Microsoft's IIS docs: app pool identities and Network Service reach the network as the machine account | Step 8 |
IIS APPPOOL\FinancePool |
An IIS app pool identity (our reading: the app and SQL Server are on the same machine) | Step 8 |
NT AUTHORITY\SYSTEM |
Something running as LocalSystem on the SQL Server machine (our reading) | Step 8 |
<token-identified principal> |
A Microsoft Entra token login | Step 8 |
sa |
The built-in SQL login, disabled by default after a Windows-mode install | Steps 3-4 |
Step 2: Read the error log (read-only)
Microsoft: "To increase security, the error message that is returned to the client deliberately hides the nature of the authentication error." The detail goes to the SQL Server error log as two lines, like Microsoft's example:
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>]
Read it with the documented sp_readerrorlog. Its third and fourth parameters are filter strings (log 0 is the current ERRORLOG, product 1 is SQL Server):
-- Read-only. Current error log, SQL Server product, lines containing "Login failed"
EXECUTE sp_readerrorlog 0, 1, N'Login failed';
-- Read-only. The state lines that go with them
EXECUTE sp_readerrorlog 0, 1, N'18456';
-- Read-only. The previous log (ERRORLOG.1), if SQL Server restarted since the failure
EXECUTE sp_readerrorlog 1, 1, N'Login failed';
Microsoft's permissions for sp_readerrorlog: membership in securityadmin, or VIEW SERVER STATE (SQL Server 2019 and earlier). SQL Server 2022 and later also accept VIEW ANY ERROR LOG. Many scripts call xp_readerrorlog directly; it isn't documented on Microsoft Learn, so we stick to sp_readerrorlog.
The same from PowerShell (SqlServer module):
# Read-only. SqlServer module; add -TrustServerCertificate if your module version and TLS setup need it
Get-SqlErrorLog -ServerInstance 'SQL01' -Since Yesterday |
Where-Object { $_.Text -match 'Login failed|18456' } |
Format-Table Date, Source, Text -Wrap
If you can't connect at all, the log is a text file. Microsoft's default location is <drive>:\Program Files\Microsoft SQL Server\MSSQL.<n>\MSSQL\LOG\ERRORLOG (our note: on current versions the folder looks like MSSQL16.MSSQLSERVER). The current log has no extension, ERRORLOG.1 is the previous one, and Microsoft says you can open it with any text editor.
No 18456 lines at all? Check login auditing. Microsoft: it can write failed logins, successful logins, or both to the error log (Server Properties > Security > Login auditing), and "You must restart SQL Server before this option takes effect." If failed logins aren't audited, the log won't help until after a restart, so look at the client side and Steps 3-4 in the meantime.
Now match the state. Microsoft's table on the 18456 page (states 38 and 46 share a row; state 40 is from Microsoft's 4064 page):
| State | Microsoft's description | Go to |
|---|---|---|
| 1 | "Error information isn't available." Usually means you don't have permission to receive the details | Read the server log as an admin |
| 2, 5 | "User ID isn't valid." | Step 4 |
| 6 | "An attempt was made to use a Windows login name with SQL Server Authentication." | Step 3 |
| 7 | "Login is disabled, and the password is incorrect." | Step 4 |
| 8 | "The password is incorrect." | Step 4 |
| 9 | "Password isn't valid." | Step 4 |
| 11 | "Login is valid, but server access failed." | Step 5 |
| 12 | "Login is valid login, but server access failed." | Step 5 |
| 18 | "Password must be changed." | Step 4 |
| 38, 46 | "Couldn't find database requested by user." | Step 6 |
| 40 | Failed to open the database specified in the login properties (the 4064 case) | Step 6 |
| 58 | SQL authentication on a Windows-only server; "Another cause is when SIDs don't match." | Step 3 |
| 62 | Windows account into a contained database where "the SIDs do not match" | Step 6 |
| 102-111, 132-133 | "Azure AD failure." | Step 8 |
| 122-124 | "Failure due to empty user name or password." | Steps 1, 8 |
| 126 | "Database requested by user doesn't exist." | Step 6 |
Microsoft's table isn't complete: "Other error states exist and signify an unexpected internal processing error." When the state isn't in the table, the Reason: text on the next log line is the better evidence (our reading). The common ones, quoted from Microsoft's 18456 and 4064 pages:
Reason: text in the log |
Layer |
|---|---|
An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. |
Authentication mode (Step 3) |
Could not find a login matching the name provided. |
Login missing, misspelled, or wrong server (Step 4) |
Password did not match that for the login provided. |
Password (Step 4) |
Token-based server access validation failed with an infrastructure error. Check for previous errors. |
Server access path (Step 5) |
Failed to open the database '<db_name>' specified in the login properties. |
Default database (Step 6) |
Failed to open the explicitly specified database '<dbname>'. |
Database in the connection string (Step 6) |
Step 3: Authentication type vs the server's mode (read-only)
-- Read-only. 1 = Windows Authentication only; 0 = Windows and SQL Server Authentication (mixed)
SELECT SERVERPROPERTY('IsIntegratedSecurityOnly') AS IsIntegratedSecurityOnly;
Microsoft's SERVERPROPERTY page: 1 is "Integrated security (Windows Authentication)," 0 is "Both Windows Authentication and SQL Server Authentication." Any user can query it.
| Finding | What Microsoft says | What it means for the ticket |
|---|---|---|
1, SQL login fails with state 58 or the "Windows authentication only" reason |
SQL logins aren't accepted in Windows Authentication mode | Decide: switch the app to Windows authentication, or enable mixed mode on purpose (Step 9) |
| State 6 | A Windows name was typed into SQL Server Authentication | Someone put CONTOSO\jdoe in the user/password fields. Use Windows authentication instead |
0, but an ODBC app still gets "Windows authentication only" |
Microsoft's "more rare" cause: ODBC over TCP or named pipes without an explicit trusted connection | Microsoft's fix: TRUSTED_CONNECTION = TRUE in the connection string |
sa fails with state 7 after a mode change |
sa stays disabled when you switch from Windows mode to mixed mode |
Expected. See Step 4 before enabling it |
Microsoft's connection-string notes for integrated security: INTEGRATED SECURITY=SSPI for OLE DB, TRUSTED_CONNECTION=YES for ODBC, and the .NET provider accepts either.
Step 4: Read the login (read-only)
-- Read-only. Does the login exist on THIS instance, and what state is it in?
SELECT name, type_desc, is_disabled, default_database_name, create_date, modify_date
FROM sys.server_principals
WHERE name = N'finance_app';
-- Read-only. SQL logins only: is password policy or expiration enforced?
SELECT name, is_disabled, is_policy_checked, is_expiration_checked
FROM sys.sql_logins
WHERE name = N'finance_app';
-- Read-only. Lockout, expiry, must-change, and recent bad passwords
SELECT LOGINPROPERTY(N'finance_app', 'IsLocked') AS IsLocked,
LOGINPROPERTY(N'finance_app', 'IsExpired') AS IsExpired,
LOGINPROPERTY(N'finance_app', 'IsMustChange') AS IsMustChange,
LOGINPROPERTY(N'finance_app', 'BadPasswordCount') AS BadPasswordCount,
LOGINPROPERTY(N'finance_app', 'BadPasswordTime') AS BadPasswordTime,
LOGINPROPERTY(N'finance_app', 'DaysUntilExpiration') AS DaysUntilExpiration,
LOGINPROPERTY(N'finance_app', 'PasswordLastSetTime') AS PasswordLastSetTime;
Microsoft's notes that change how you read this: is_disabled 1 means disabled. LOGINPROPERTY returns NULL "if the login isn't a valid SQL Server login," and most of its properties are only available when both CHECK_POLICY and CHECK_EXPIRATION are on (PasswordLastSetTime is always available). DaysUntilExpiration is 0 if the password has expired or expires today, and NULL if CHECK_POLICY or CHECK_EXPIRATION is off. CHECK_POLICY defaults to ON and applies the Windows password policy of the computer running SQL Server.
| Finding | What it means | Next |
|---|---|---|
No row in sys.server_principals
|
Microsoft: "Login doesn't exist on the SQL Server instance you're trying to connect to" | Check the connection string's server first. Microsoft calls DEV/QA strings in production "a common issue" |
is_disabled = 1 |
Disabled login (state 7 when the password is also wrong) | Find out who disabled it and why before enabling |
IsLocked = 1 |
Locked by the Windows password policy (our reading: usually an app or job retrying an old password) | Fix the caller's stored password first, or it locks again |
IsExpired = 1 or DaysUntilExpiration = 0
|
Password expired | Planned password change, then update every caller |
IsMustChange = 1 |
State 18: "Password must be changed" | Most apps can't change passwords; set one deliberately |
BadPasswordTime matches the failures |
State 8 behind the scenes | Microsoft: check for a configuration file overriding the connection (SSIS has several levels) |
| Login present but the name in the message differs | Typo or a different login in the connection string | Fix the string, not the login |
About sa. Microsoft: "The sa account is a well known SQL Server account, and is often targeted by malicious users. Don't enable the sa account unless your application requires it." A vendor wanting to test isn't a requirement. Give them a named login with only the access the app needs.
Step 5: Windows logins: the server access path (read-only)
State 11 and the Token-based server access validation failed reason mean the Windows identity was recognized but didn't get server access. Microsoft's examples: logins denied or not granted, access only through the Administrators group, or "A group that the user belonged to, had DENY permissions in SQL Server."
-- Read-only. Which permission paths give this Windows account access? ('all' = every path)
EXECUTE xp_logininfo N'CONTOSO\jdoe', 'all';
How Microsoft says to read it:
xp_logininfo result |
What it means |
|---|---|
| Rows returned | The account has access through the listed permission path (a group, or the account itself) |
| Empty result | "a valid Windows user or group that isn't associated with a SQL Server login." No login or group gives access |
| An error | SQL Server can't resolve the name. Microsoft: possibly not in AD, or a domain controller problem |
Microsoft's limits: xp_logininfo "only returns information from Active Directory global groups, not universal groups," and for cross-domain access "the group must be in the SQL Server domain, and not the user domain." Running it needs CONTROL SERVER, or EXECUTE on xp_logininfo in master.
Then look for a DENY:
-- Read-only. Explicit DENYs on CONNECT SQL (server) or CONNECT (endpoint, class 105)
SELECT pr.name, pr.type_desc, pe.class_desc, pe.permission_name, pe.state_desc
FROM sys.server_permissions AS pe
JOIN sys.server_principals AS pr ON pr.principal_id = pe.grantee_principal_id
WHERE pe.state = 'D'
AND pe.permission_name IN ('CONNECT SQL', 'CONNECT');
Microsoft's sys.server_permissions page lists CONNECT SQL on the server and CONNECT on endpoints (class 105), and state D is Deny. A DENY on a group the user belongs to beats the grant they get elsewhere (our reading of standard DENY behavior).
The admin-only trap. Microsoft's state 11 note: "when the Windows user has access to SQL Server as a member of the local administrators' group, but Windows isn't providing administrator credentials." Microsoft's fix is to start the program with Run as administrator, then add the user as a specific login. Our reading: access through BUILTIN\Administrators only works elevated, so a login that "works for me in an elevated window" isn't proof the app's identity has access.
Group change just made? Microsoft's ALTER LOGIN page: "To force changes in the identity, such as a password reset or change in Windows group membership, the login must sign out from the authentication authority (Windows or SQL Server), and sign in again." Have the user sign out of Windows, or restart the app's service, before you re-test.
Step 6: The database (read-only)
A login can authenticate and still fail on the database it asks for. Microsoft's 4064 page shows the 18456 that goes with it: State: 40, Failed to open the database '<db_name>' specified in the login properties.
-- Read-only. The login's default database, and its state
SELECT sp.name, sp.default_database_name, d.state_desc, d.user_access_desc
FROM sys.server_principals AS sp
LEFT JOIN sys.databases AS d ON d.name = sp.default_database_name
WHERE sp.name = N'finance_app';
| Finding | Microsoft's cause | Next |
|---|---|---|
state_desc not ONLINE (offline, suspect, recovery pending, emergency) |
Default database unavailable | Fix the database, or change the default database (Step 9) |
user_access_desc SINGLE_USER or RESTRICTED_USER
|
Single-user mode in use, or restricted to db_owner / dbcreator / sysadmin
|
Finish the maintenance, or change the default database |
d.state_desc is NULL
|
"The default database no longer exists" (or the name is wrong) | Change the default database |
| State 38 with "explicitly specified database" | Wrong or unavailable database in the connection string | Microsoft: watch case sensitivity on case-sensitive collations |
| Database fine, login has no user in it | "No user mapping exists in the login's default database" | Check for an orphaned user (below) |
Microsoft's workaround for people who need in now: in SSMS, Options > Connection Properties > Connect to database, and enter master or another database the login can reach.
After a restore or move to another server, check for orphaned users. Microsoft's detection query, run in the user database:
-- Read-only. Users mapped to SQL logins that don't exist on this instance
SELECT dp.type_desc, dp.sid, dp.name AS user_name
FROM sys.database_principals AS dp
LEFT JOIN sys.server_principals AS sp ON dp.sid = sp.sid
WHERE sp.sid IS NULL
AND dp.authentication_type_desc = 'INSTANCE';
Microsoft: a user can be orphaned when the database is "restored or attached to a different instance of SQL Server where the login was never created," and "Even if the login is re-created, it has a different SID, so the database user is still orphaned."
Contained databases. Microsoft: a contained database user's connection string "must always contain a parameter for the user database," and a contained user "takes precedence over logins that have the same name" when a database is named in the connection. Contained users also need the contained database authentication option set to 1 on the instance. A login deleted after migrating to contained users is on Microsoft's 18456 cause list, and state 62 is a Windows account whose SID doesn't match in a contained database.
-- Read-only. Is the database contained, and is contained authentication on for the instance?
SELECT name, containment_desc FROM sys.databases WHERE name = N'FinanceDB';
SELECT name, value_in_use FROM sys.configurations WHERE name = N'contained database authentication';
containment_desc comes from Microsoft's contained database example, and the sys.configurations read is our way to check the option without changing it.
Step 7: The Windows authentication layer (read-only, only when the name points here)
Skip this step for SQL logins. SPNs and Kerberos only matter for Windows authentication. Microsoft: "Clients that use Windows authentication are authenticated by using NTLM or Kerberos," and "If the SQL Server client connects locally on the same machine where SQL Server resides, it always uses NTLM."
| Symptom | Microsoft's causes | Read-only check |
|---|---|---|
NT AUTHORITY\ANONYMOUS LOGON |
NTLM loopback, double-hop delegation with SPN problems, duplicate SPNs, the "Allow Local System to use computer identity for NTLM" policy, an expired ticket on a middle tier |
setspn -Q, setspn -X (below); is a linked server or web tier passing credentials on? |
Empty name ''
|
LSASS unavailable or no domain controller; SSPI codes 0x80090311 / 0x80090304
|
nltest /sc_query:CONTOSO on the SQL Server machine |
(null) |
"the SPN is associated with the wrong account" |
setspn -Q for the instance SPN |
| Error 18452, "login is from an untrusted domain" | Unrecognized Windows principal, untrusted domain, or SPN visibility across trusts | Which domain is the user in? Is there a trust? |
| "Cannot generate SSPI context" (separate error, same ticket) | Usually a misconfigured SPN | Same SPN checks |
# Read-only. Elevated on SQL01 or any domain-joined admin box
setspn -L CONTOSO\svc-sql01 # SPNs on the SQL Server service account
setspn -Q MSSQLSvc/SQL01.corp.contoso.com:1433 # which account owns the instance SPN?
setspn -X # duplicate SPNs in the domain
nltest /sc_query:CONTOSO # secure channel / DC the SQL host is using
Microsoft's setspn reference: -L lists registered SPNs, -Q "Queries for any existing SPNs," and -X "Performs a search of duplicate SPNs." The SPN format for a TCP connection is MSSQLSvc/<FQDN>:<port>. nltest /sc_query is the read-only half of Microsoft's 18456 guidance; /sc_reset switches domain controllers and is a change.
For a connection that does work, check which protocol it got:
-- Read-only. KERBEROS, NTLM, or SQL for the current connection
SELECT net_transport, auth_scheme
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;
Microsoft: auth_scheme "is KERBEROS if Kerberos is enabled." If the SPN maps to the wrong account, Microsoft's table says "Authentication fails"; if the lookup fails, "Local and remote connections use NTLM." Other Kerberos causes Microsoft lists: clock skew over five minutes, disjoint DNS namespaces, HOSTS file entries, SQL aliases, and users in very many groups.
Step 8: Special identities (read-only)
Many 18456 tickets are an identity mismatch: you granted one account, and a different one connects.
| Name in the log | Where it comes from | What to grant (our reading unless noted) |
|---|---|---|
CONTOSO\WEB02$ |
Microsoft's IIS page: app pool identities "use the machine account to access network resources," as Network Service does | A login for the computer account, or better, run the app pool as a dedicated service account and grant that |
IIS APPPOOL\FinancePool |
IIS creates a virtual account named after the pool (Microsoft). On a remote SQL Server it shows up as the machine account instead | Usually a sign the app and SQL Server share a host. Grant that exact name, or move to a dedicated account |
NT AUTHORITY\SYSTEM |
A service, task, or agent running as LocalSystem on the SQL host | Find the process first. Don't hand sysadmin to NT AUTHORITY\SYSTEM to quiet a log |
NT AUTHORITY\NETWORK SERVICE or LOCAL SERVICE
|
A local service account on the SQL host | Same: identify the caller, then decide |
<token-identified principal> |
Microsoft Entra authentication | Microsoft's SSMS guidance: if you were just added to an Entra group, clear the cached token (Help > Clear Entra ID Token Cache) and reconnect |
For Entra logins, Microsoft's 18456 state table lists 102-111 and 132-133 as "Azure AD failure." On Azure SQL, Microsoft's troubleshooting page adds that SSMS tries to connect to master by default, which fails with "Cannot open database "master" requested by the login" if the account has no access there. Set Connect to database to the user's database.
Common false starts (read these before you change anything)
| False start | Why it hurts | Read-first alternative |
|---|---|---|
Enabling sa for a vendor test |
Microsoft: sa "is often targeted by malicious users"; enable it only if the app requires it |
A named login with only what the app needs |
| Switching to mixed mode for a Windows-login failure | Changes nothing for CONTOSO\jdoe, needs a restart, and widens the attack surface |
Step 2 state first; Step 3 only for SQL logins |
| Resetting the password when the state isn't 8 | Disabled, locked, wrong server, or missing database all survive a reset | Match the state (Step 2) |
Adding the app login to sysadmin |
Hides a mapping or database problem behind the highest privilege on the server | Steps 5-6: access path and user mapping |
| Testing from an elevated SSMS window | Access via local Administrators only works elevated (state 11) | Test as the app's real identity |
| Dropping and re-creating a SQL login | The new login gets a new SID, so database users stay orphaned (Microsoft) |
ALTER USER ... WITH LOGIN (Step 9) |
Leaving CHECK_POLICY = OFF after an unlock |
Microsoft: turning it off clears password history and also turns off CHECK_EXPIRATION
|
Microsoft's unlock options, policy back on (Step 9) |
| Registering SPNs for a SQL-login failure | Kerberos isn't involved in SQL Server authentication | Steps 3-4 |
Setting DisableLoopbackCheck |
Microsoft calls BackConnectionHostNames the preferred option |
BackConnectionHostNames with the specific names |
| Running the app pool as LocalSystem | Swaps one identity problem for an over-privileged one (our reading) | Dedicated service account with a matching login |
| Trusting the client message over the log | Microsoft hides the reason from the client on purpose |
sp_readerrorlog (Step 2) |
Step 9: Change one layer, then watch (only after the read)
Pick the one change Steps 1-8 point at. T-SQL has no -WhatIf, so we use the same small wrapper as our 9002 post. It gives any T-SQL change -WhatIf and -Confirm:
# Change helper (ours). Prints the statement under -WhatIf; runs it only without -WhatIf
function Invoke-SqlChange {
[CmdletBinding(SupportsShouldProcess, ConfirmImpact = 'High')]
param(
[Parameter(Mandatory)] [string] $ServerInstance,
[string] $Database = 'master',
[Parameter(Mandatory)] [string] $Query
)
if ($PSCmdlet.ShouldProcess("$ServerInstance/$Database", $Query)) {
Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $Database -Query $Query
}
}
# Change (ALTER ANY LOGIN; CONTROL SERVER if the login is sysadmin). Pick ONE that matches your finding
# Disabled login (state 7), after you know why it was disabled
Invoke-SqlChange -ServerInstance 'SQL01' -Query 'ALTER LOGIN [finance_app] ENABLE;' -WhatIf
# Locked login: Microsoft's unlock with a new password (then update every caller)
Invoke-SqlChange -ServerInstance 'SQL01' -Query "ALTER LOGIN [finance_app] WITH PASSWORD = N'<new strong password>' UNLOCK;" -WhatIf
# Default database offline or gone (state 40 / error 4064)
Invoke-SqlChange -ServerInstance 'SQL01' -Query 'ALTER LOGIN [finance_app] WITH DEFAULT_DATABASE = [FinanceDB];' -WhatIf
# Orphaned user after a restore: remap the user to the existing login (run in the user database)
Invoke-SqlChange -ServerInstance 'SQL01' -Database 'FinanceDB' -Query 'ALTER USER [finance_app] WITH LOGIN = [finance_app];' -WhatIf
# Login authenticates but has no user in the database
Invoke-SqlChange -ServerInstance 'SQL01' -Database 'FinanceDB' -Query 'CREATE USER [finance_app] FOR LOGIN [finance_app];' -WhatIf
# The app really connects as the web server's computer account (Step 8)
Invoke-SqlChange -ServerInstance 'SQL01' -Query 'CREATE LOGIN [CONTOSO\WEB02$] FROM WINDOWS;' -WhatIf
Microsoft also documents unlocking without a password change by setting CHECK_POLICY = OFF and then ON again. If you use it, run both statements together. Leaving it off clears the password history and turns off expiration checks. Give a new user only the roles the app needs. If an explicit DENY from Step 5 is the cause, find out who set it and why before you REVOKE it.
Mixed mode, only when a SQL login is the right design (state 58): Microsoft's procedure is SSMS > server Properties > Security > Server authentication, then restart SQL Server. Its T-SQL alternative writes the LoginMode registry value with xp_instance_regwrite, and Microsoft warns that modifying the registry incorrectly can cause serious problems. Use SSMS. Either way, the restart is an outage for every app on the instance:
Microsoft's SSMS step for the restart is to right-click the server and select Restart, and "If SQL Server Agent is running, it must also be restarted." The PowerShell form below is ours:
# Change (maintenance window). Default instance names shown; check yours first with: Get-Service -Name 'MSSQL*', 'SQL*AGENT*'
Stop-Service -Name 'SQLSERVERAGENT' -WhatIf
Restart-Service -Name 'MSSQLSERVER' -WhatIf
Start-Service -Name 'SQLSERVERAGENT' -WhatIf
MSSQLSERVER and SQLSERVERAGENT are the default instance's service names. A named instance uses MSSQL$<InstanceName> and SQLAgent$<InstanceName> (our note). All three cmdlets support -WhatIf and -Confirm.
SPN fixes (AD admin, only for the Step 7 symptoms): use Kerberos Configuration Manager's diagnosis, or setspn. Microsoft: "you should use setspn -S instead because it verifies that there are no duplicate SPNs." setspn has no dry-run switch, so run setspn -Q for the exact SPN first, then remove it from the wrong account with -D and add it to the right one with -S, one SPN at a time.
Domain controller fixes: for the empty-name case, Microsoft's 18456 page uses nltest /sc_reset:CONTOSO\DC03 to switch the SQL host to a working DC. Fix or isolate the bad DC as well.
Read-only confirm afterwards:
-- No new failures for this login since the change
EXECUTE sp_readerrorlog 0, 1, N'Login failed', N'finance_app';
-- Login state and default database
SELECT name, is_disabled, default_database_name FROM sys.server_principals WHERE name = N'finance_app';
SELECT LOGINPROPERTY(N'finance_app', 'IsLocked') AS IsLocked;
Watch one full cycle: the app connects as its own identity (not an admin test), sp_readerrorlog shows no new 18456 lines for it through the next app pool recycle or job run, and for Windows logins auth_scheme shows what you expect (KERBEROS for remote connections where you registered SPNs).
When a change is justified
| Finding | Prefer | Avoid |
|---|---|---|
| State 58, the app needs a SQL login | Mixed mode in a maintenance window, a named app login | Enabling sa at the same time |
| State 58, the app could use Windows auth | Windows authentication in the connection string | Mixed mode "just in case" |
| State 2/5, wrong server in the string | Fix the connection string | Creating the login on the wrong server |
| State 7, login disabled | Find out why, then ALTER LOGIN ... ENABLE
|
Enabling every disabled login |
| State 8, password changed somewhere | Update the stored password everywhere it's used | Turning CHECK_POLICY off permanently |
| State 11, access only via local Administrators | Explicit login or group for the real identity | Running the app elevated |
| DENY through a group | Review the DENY with its owner | Adding the user to sysadmin
|
| State 40, default database offline | Fix the database or change the default database | Granting access to a different database to dodge it |
| Orphaned user after a restore | ALTER USER ... WITH LOGIN |
Dropping and re-creating the login |
CONTOSO\WEB02$ or IIS APPPOOL\...
|
Dedicated service account for the app pool, matching login | App pool as LocalSystem |
ANONYMOUS LOGON with SPN findings |
Correct the SPN with setspn -S
|
DisableLoopbackCheck |
<token-identified principal> after a group change |
Clear the SSMS token cache, reconnect | Recreating the Entra user |
FAQ
What does SQL Server error 18456 mean?
SQL Server rejected a login because authentication failed. Microsoft lists causes like invalid credentials, an expired password, and the wrong authentication mode. The message the client sees hides the reason. Read the matching Error: 18456, Severity: 14, State: n line and the Reason: text in the SQL Server error log with sp_readerrorlog.
How do I find the state for error 18456?
Run EXECUTE sp_readerrorlog 0, 1, N'18456'; as a member of securityadmin or with VIEW SERVER STATE (VIEW ANY ERROR LOG also works on SQL Server 2022 and later), or open the ERRORLOG file in the instance's LOG folder. Match the time to the failure, then look the state up in Microsoft's table (8 = wrong password, 7 = disabled, 58 = SQL login on a Windows-only server, 40 = default database unavailable).
Why does it say "Login failed for user 'sa'"?
Most often the sa login is disabled. Microsoft: if SQL Server was installed in Windows Authentication mode and later changed to mixed mode, sa "remains disabled," which logs state 7. On a Windows-only server it's state 58. Microsoft advises not enabling sa unless your application requires it. Use a named login instead.
Why does the error show my web server's computer account or an IIS APPPOOL name?
The app connects as its app pool identity, not as the user. Microsoft's IIS docs say app pool identities reach network resources as the machine account (DOMAIN\SERVER$). When SQL Server is on the same machine, you'll typically see IIS APPPOOL\<pool> instead. Grant that identity, or run the pool as a dedicated service account.
What do severity 14 and SQLSTATE 28000 mean with error 18456?
Severity 14 is SQL Server's class for "security-related errors, such as permission denied," so it's not a crash or corruption. SQLSTATE 28000 is the ODBC code for "Invalid authorization specification," which drivers report when a connection is refused at login. Neither tells you the reason. The state in the error log does.
Related posts
- SQL Server Transaction Log Full (Error 9002) (the same read-first approach for a database that stops accepting writes)
- Trust Relationship Between This Workstation and the Primary Domain Failed (when Windows logins fail with an empty name because the SQL host lost its secure channel)
- Active Directory Account Keeps Getting Locked Out (an app or job retrying an old service account password breaks SQL logins and locks the AD account)
Free checklist
More apps are moving from SQL logins to Microsoft Entra authentication, and that's when a <token-identified principal> ticket shows up. Once the database trusts Entra tokens, Conditional Access decides who gets a token in the first place. The free Conditional Access pre-enforcement checklist keeps break-glass accounts, pilot groups, and report-only review on one page:
If you want the rest of the Intune runway written down for the team (enrollment hygiene, compliance phasing, break/fix cards and read-only PowerShell snapshots), the Intune & M365 Admin Starter Pack pairs with this triage: https://cashflow4375.gumroad.com/l/joonf?utm_source=devto&utm_medium=article&utm_campaign=aps_first_sale&utm_content=sql-server-error-18456
Admin Pack Studio; not affiliated with Microsoft. Steps 1-8 favor read-only checks: sp_readerrorlog, Get-SqlErrorLog, SERVERPROPERTY, sys.server_principals, sys.sql_logins, LOGINPROPERTY, xp_logininfo, sys.server_permissions, sys.databases, sys.database_principals, sys.configurations, sys.dm_exec_connections, setspn -L/-Q/-X, and nltest /sc_query. Enabling, unlocking, or re-pointing logins, changing default databases, creating logins or users, remapping users, revoking DENYs, changing the authentication mode or login auditing, restarting SQL Server, registering SPNs, and resetting a secure channel are deliberate changes. Change one layer at a time, test as the app's real identity, and check current Microsoft Learn for version-specific behavior.
Top comments (0)