What is error 18456
Error 18456 means "Login failed for user". SQL Server received the connection, but it refused the login. The message to the client is kept vague on purpose. The real reason is in the SQL Server error log as a State number.
Find the real reason
Check the error log and Event Viewer
- In SQL Server Management Studio open Management > SQL Server Logs > Current.
- Search for
18456. - Read the line with the State value, for example
Error: 18456, Severity: 14, State: 8.
You can also read the logs in the Windows Event Viewer.
Common state values:
| State | Meaning |
|---|---|
| 2 or 5 | The user name is not valid |
| 6 | A Windows login was used with SQL authentication |
| 7 | The login is disabled and the password is wrong |
| 8 | The password is wrong |
| 9 | The password is not valid |
| 11 or 12 | The login is valid but has no permission to connect |
| 13 | The SQL Server service is paused |
| 16 | The login cannot open the default database |
| 18 | The password must be changed |
| 38 or 40 | The database requested in the login cannot be opened |
| 58 | SQL authentication is used but the server accepts only Windows authentication |
Fixes
1. Check the user name and password
- Type them again. Watch for caps lock, extra spaces and the right domain (
DOMAIN\user). - Reset the password if you are not sure:
ALTER LOGIN [username] WITH PASSWORD = 'NewStrongPassword';2. Check that the login is enabled and not locked
ALTER LOGIN [username] ENABLE;
ALTER LOGIN [username] WITH PASSWORD = 'NewStrongPassword' UNLOCK;3. Check the authentication mode
If you use SQL logins but the server accepts only Windows logins, the login fails (state 58).
- In SSMS right-click the server and choose Properties > Security.
- Choose SQL Server and Windows Authentication mode.
- Restart the SQL Server service.
4. Check the permissions
- Make sure the login exists under Security > Logins.
- Make sure it has CONNECT SQL permission and access to the database.
- Map the user to the database:
USE MyDatabase;
CREATE USER [username] FOR LOGIN [username];
ALTER ROLE db_datareader ADD MEMBER [username];5. Check the default database
If the default database is gone or offline, set another one:
ALTER LOGIN [username] WITH DEFAULT_DATABASE = master;6. Check the network and the server name
Connect to the right instance (SERVER\INSTANCE), and check that the SQL Server Browser service and TCP/IP are on if you use a named instance.
Prevent the error
- Use strong, unique passwords and store them safely.
- Monitor the logs for repeated failed logins. They can show an attack.
- Set a lockout policy for failed attempts.
- Choose the authentication mode that fits your needs. Windows authentication is safer when it is possible.
- Keep SQL Server updated.
- Review the permissions from time to time and give only the rights that are needed.
Quick recap
- Read the State in the SQL Server log to find the exact reason.
- Fix the password, the authentication mode or the permissions.