SQL Server "Login failed for user" (error 18456): the state tells you why

1. Read the log

If you can log in with another account, use the query above, or in SSMS go to Management → SQL Server Logs.

If you can’t log in at all, the log is a plain text file:

C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\ERRORLOG    # Windows (16 = SQL Server 2022)
/var/opt/mssql/log/errorlog                                                      # Linux

In a container:

docker logs mssql 2>&1 | grep -A1 18456

Ignore the state in the client’s error details: it’s usually 1, which only means “details withheld”.

2. What the state means

3. Wrong password, or no such login

Check that the login exists, and whether it’s enabled, as an administrator:

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

No row means there’s no such login. Create it, then give it access to its database:

CREATE LOGIN app WITH PASSWORD = 'new-password-here';
USE shop;
CREATE USER app FOR LOGIN app;
ALTER ROLE db_datareader ADD MEMBER app;
ALTER ROLE db_datawriter ADD MEMBER app;

Otherwise set a new password:

ALTER LOGIN app WITH PASSWORD = 'new-password-here';

SQL Server can enforce the Windows password policy, so a weak password fails with a different error. The Password Generator gives you one that passes, and with shell-safe symbols it won’t break a connection string either.

A login with MUST_CHANGE set fails with its own error, 18488: The password of the account must be changed. An application can’t answer that prompt, so set the password yourself, as above.

4. Windows and SQL logins are different things

State 58: the server is in Windows authentication mode. SQL logins, including sa, are refused until you allow them. In SSMS: right-click the server → Properties → Security → SQL Server and Windows Authentication mode. Or with T-SQL:

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
  N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2;

Either way, restart the service for it to apply:

Restart-Service MSSQLSERVER        # a named instance: 'MSSQL$SQLEXPRESS'

State 6: DOMAIN\user typed into the SQL login box. A Windows account doesn’t use a password here. Choose Windows Authentication in SSMS, or in a connection string use Integrated Security=true instead of a user and password.

5. A disabled login

sa is the usual case: it’s disabled by default if you installed with Windows authentication only.

ALTER LOGIN sa WITH PASSWORD = 'new-password-here';
ALTER LOGIN sa ENABLE;

A locked-out login (too many wrong passwords under the policy) is unlocked the same way:

ALTER LOGIN app WITH PASSWORD = 'new-password-here' UNLOCK;

For applications, prefer a login of their own over sa.

6. A valid login without access

Your Windows account is known, but has no way in. This is often a Windows group that was removed, or an admin who isn’t running SSMS elevated, since User Account Control hides the Administrators group until you do. Right-click SSMS → Run as administrator, or add the account directly:

CREATE LOGIN [CONTOSO\alex] FROM WINDOWS;

7. The login is fine, but the database isn’t

States 38 and 40 mean the login worked, and opening the database didn’t: it was renamed, dropped or is offline, or the login has no user in it. The log names the database:

Reason: Failed to open the explicitly specified database 'shop'.

Check the database, then check the mapping:

SELECT name, state_desc FROM sys.databases;

USE shop;
CREATE USER app FOR LOGIN app;

If it’s the login’s default database that’s gone, connect by naming another one (in SSMS: Options → Connection Properties → Connect to database, or sqlcmd -d master), then fix it:

ALTER LOGIN app WITH DEFAULT_DATABASE = master;

After restoring a database on another server, its users may have lost their logins. ALTER USER app WITH LOGIN = app; reconnects them.

8. Locked out completely

No administrator can log in? On Windows, a local administrator can get in through single-user mode:

net stop MSSQLSERVER
net start MSSQLSERVER /m"SQLCMD"
sqlcmd -S . -E -Q "CREATE LOGIN [CONTOSO\alex] FROM WINDOWS; ALTER SERVER ROLE sysadmin ADD MEMBER [CONTOSO\alex];"
net stop MSSQLSERVER
net start MSSQLSERVER

On Linux, sudo systemctl stop mssql-server, then sudo /opt/mssql/bin/mssql-conf set-sa-password, and start it again.