SQL Server error 18456: Login failed for user, fixed by state

Short answer: Microsoft SQL Server error 18456, “Login failed for user”, means the server was reached but refused the login, and the message hides why. The State number in the SQL Server error log names the reason: 8 wrong password, 5 unknown login, 58 Windows Authentication only, 7 disabled login, 38 database not available.

Cannot connect to localhost.
Login failed for user 'appuser'. (Microsoft SQL Server, Error: 18456)

2026-10-21 09:14:02.31 Logon       Error: 18456, Severity: 14, State: 8.
2026-10-21 09:14:02.31 Logon       Login failed for user 'appuser'. Reason: Password did not match that for the login provided. [CLIENT: 203.0.113.25]

Read the log first, then jump to the fix for that state below. The state meanings and the log messages here are taken from Microsoft’s reference page MSSQLSERVER_18456 (checked 21 October 2026). One useful thing an 18456 already tells you: the network path works. A server you cannot reach fails with a network or instance error instead, never with 18456.

Where to find the State number

SQL Server logs failed logins by default (Server Properties, Security, Login auditing: “Failed logins only”), in two places. You need access to the server itself, for example over Remote Desktop, but not a working SQL login.

The ERRORLOG file

The current error log is a text file called ERRORLOG without an extension, under C:\Program Files\Microsoft SQL Server\MSSQL<nn>.<INSTANCE>\MSSQL\Log. The number is the major version (15 for SQL Server 2019, 16 for SQL Server 2022) and the instance is MSSQLSERVER for a default instance or, for example, SQLEXPRESS. Older logs are ERRORLOG.1, ERRORLOG.2 and so on. In PowerShell as Administrator:

$log = Get-ChildItem 'C:\Program Files\Microsoft SQL Server\MSSQL*\MSSQL\Log\ERRORLOG'
$log.FullName
Select-String -Path $log.FullName -Pattern 'Login failed' -Context 1,0 | Select-Object -Last 5

-Context 1,0 prints the line before each match, which is the one carrying State: N. Once you can connect with some sysadmin login, SSMS shows the same log under Object Explorer, Management, SQL Server Logs, and T-SQL can search it:

EXEC sp_readerrorlog 0, 1, N'Login failed';   -- 0 = current log, 1 = SQL Server log

The Windows Application event log

Each failed login is also written to the Application log as event ID 18456, from the source MSSQLSERVER (or MSSQL$NAME for a named instance). The message carries the Reason text and the client address:

Get-WinEvent -FilterHashtable @{LogName='Application'; Id=18456} -MaxEvents 10 |
  Format-List TimeCreated, Message

Error 18456 states and what they mean

Microsoft adds that other states exist and point to unexpected internal errors. The Reason text in the log line usually makes the state unambiguous anyway:

• Reason: Password did not match that for the login provided. means state 8.
• Reason: Could not find a login matching the name provided. means the login does not exist on this instance.
• Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. means state 58.
• Reason: Failed to open the database 'shop' specified in the login properties is an unavailable default database; the client often shows error 4064, “Cannot open user default database”, instead.

State 58: turn on SQL Server Authentication

If SQL Server was installed with Windows Authentication mode, it rejects every SQL login, including sa, whatever the password. Check the mode first:

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

1. 1Connect with Windows Authentication

   On the server, start SSMS (as administrator if your Windows account has no login of its own) and connect to the instance with Windows Authentication.

2. 2Change the authentication mode

   In Object Explorer, right-click the server, choose Properties, open the Security page and select SQL Server and Windows Authentication mode. Click OK and acknowledge the note that a restart is needed.

3. 3Restart the SQL Server service

   Right-click the server in Object Explorer and choose Restart, or use PowerShell. The change does nothing until the service has restarted; SQL Server Agent, if it runs, has to be restarted too.

Restart-Service -Name 'MSSQLSERVER' -Force          # default instance
Restart-Service -Name 'MSSQL$SQLEXPRESS' -Force     # named instance, e.g. Express

-Force is needed because SQL Server Agent depends on the service and has to stop with it. Afterwards, Get-Service SQLSERVERAGENT shows whether the Agent of a default instance is running again; start it with Start-Service if not (Express has no Agent). The single quotes keep PowerShell from reading $SQLEXPRESS as a variable.

State 7: the login is disabled (usually sa)

Per Microsoft, when SQL Server is installed in Windows Authentication mode, setup disables sa, and it stays disabled after you switch to mixed mode. Logging in as sa then fails with state 7. Enable it and give it a new password:

ALTER LOGIN sa ENABLE;
ALTER LOGIN sa WITH PASSWORD = '<a long random password>';

Better still, leave sa disabled and give the application a login of its own with rights to its one database:

CREATE LOGIN appuser WITH PASSWORD = '<a long random password>';
GO
USE shop;
CREATE USER appuser FOR LOGIN appuser;
ALTER ROLE db_datareader ADD MEMBER appuser;
ALTER ROLE db_datawriter ADD MEMBER appuser;

States 8, 9 and 18: the password

State 8 is a wrong password, full stop. Before resetting it, rule out the connection string: a password containing ; or a quote character has to be enclosed in quotes in an ADO.NET connection string (and in braces in an ODBC one), or the driver cuts it off and sends a different password. To see a SQL login’s state and reset it:

SELECT name, is_disabled, default_database_name, is_policy_checked, is_expiration_checked
FROM sys.sql_logins WHERE name = 'appuser';

SELECT LOGINPROPERTY('appuser', 'IsLocked')     AS locked,
       LOGINPROPERTY('appuser', 'IsMustChange') AS must_change,
       LOGINPROPERTY('appuser', 'IsExpired')    AS expired;

ALTER LOGIN appuser WITH PASSWORD = '<new password>';
ALTER LOGIN appuser WITH PASSWORD = '<new password>' UNLOCK;   -- if it was locked out

A login created with CHECK_POLICY = ON follows the Windows password policy of the server, so if that policy sets an account lockout threshold, too many wrong attempts lock the login, and the client then reports error 18486 (“the account is currently locked out”) instead of 18456. State 18 appears for a login created with MUST_CHANGE: SSMS offers to change the password on connect, while an application simply fails until someone sets a new one.

States 2 and 5: no login with that name

Usually the login exists, but on another instance: the connection string points at localhost while the login was created on localhost\SQLEXPRESS, or at a test server instead of production. List the logins on the instance you actually reach:

SELECT name, type_desc, is_disabled
FROM sys.server_principals
WHERE type IN ('S', 'U', 'G')      -- SQL login, Windows login, Windows group
ORDER BY name;

States 38 and 46, or error 4064: the database

The login itself is fine, but the database it asks for is misspelt, offline, being restored, or the login has no user in it. Connect to master to get in (in SSMS: Options, Connection Properties, Connect to database), then fix the cause:

SELECT name, state_desc FROM sys.databases;                -- is it ONLINE?
ALTER LOGIN appuser WITH DEFAULT_DATABASE = master;          -- if the default database is gone
USE shop; CREATE USER appuser FOR LOGIN appuser;             -- if the login has no user there

On a server with a case-sensitive collation, Shop and shop are different database names, so check the spelling in the connection string exactly.

States 6, 11 and 12: Windows logins

State 6 means a Windows account name such as WIN-SRV\Administrator was typed into the SQL Server Authentication fields; choose Windows Authentication instead. States 11 and 12 mean the Windows login is known but access was refused. Microsoft’s typical case: the account only reaches SQL Server through the local Administrators group, and User Account Control does not hand over the administrator token. Start SSMS with Run as administrator, connect, and add the account as a login of its own so the workaround is not needed again:

CREATE LOGIN [WIN-SRV\Administrator] FROM WINDOWS;     -- your computer name\account
ALTER SERVER ROLE sysadmin ADD MEMBER [WIN-SRV\Administrator];

Locked out completely: single-user mode

If no login with sysadmin rights works any more (sa disabled, the password lost, the admin login removed), Microsoft’s documented way back in is single-user mode: any member of the local Administrators group can then connect as sysadmin. Stop SQL Server Agent first, or it may take the only connection. In PowerShell as Administrator, for a default instance:

net stop MSSQLSERVER
net start MSSQLSERVER /f /mSQLCMD

sqlcmd -E -S localhost -Q "CREATE LOGIN [$env:COMPUTERNAME\Administrator] FROM WINDOWS; ALTER SERVER ROLE sysadmin ADD MEMBER [$env:COMPUTERNAME\Administrator];"

net stop MSSQLSERVER
net start MSSQLSERVER

/mSQLCMD (upper case) admits only a client that identifies itself as sqlcmd; /f starts the instance with a minimal configuration. If the Windows login already exists, run only the ALTER SERVER ROLE part. In mixed mode you can reset the sa password in the same session with ALTER LOGIN sa WITH PASSWORD = '…'. The final stop and start bring the instance back to normal multi-user mode.

SQL Server on a Windows VPS: reaching it safely

On your own server, every fix above starts with a Remote Desktop session as Administrator (how to connect over RDP). From there:

• Check the service. Get-Service MSSQL* shows whether the instance runs. SQL Server Configuration Manager shows whether TCP/IP is enabled for it: Express installs as the named instance SQLEXPRESS, which uses a dynamic port until you set a fixed one.
• Open TCP 1433 only to addresses you trust. The default instance listens on 1433, and Windows Firewall keeps it closed until you add a rule. Add one with -RemoteAddress for your office or home IP; the guide to opening a port in Windows Firewall walks through it and the test from outside.
• Or do not open it at all. Run SSMS inside the RDP session, or reach the server through a VPN, and the database port never faces the internet.

New-NetFirewallRule -DisplayName "SQL Server 1433 from office" -Direction Inbound -Protocol TCP -LocalPort 1433 -RemoteAddress 198.51.100.7 -Action Allow

Hundreds of 18456 from addresses you do not know

A port 1433 open to the whole internet is found by scanners quickly, and the log fills with failed logins for sa and other common names. That is someone guessing passwords, not a fault. Count who is trying:

Get-WinEvent -FilterHashtable @{LogName='Application'; Id=18456} -MaxEvents 500 |
  ForEach-Object { if ($_.Message -match 'CLIENT: ([^\]]+)') { $Matches[1] } } |
  Group-Object | Sort-Object Count -Descending | Select-Object -First 10 Count, Name

The fix is to close the port to everyone but your own addresses, keep sa disabled (ALTER LOGIN sa DISABLE;) and use long random passwords on every SQL login. The same baseline applies to Remote Desktop itself, which is the other door into the server; securing a Windows RDP server covers it.

Frequently asked