When I tried to log in to SQL Server using the sa
account, I encountered the following error
Login failed for user 'sa'. (Microsoft SQL Server, Error: 18456)
So, how do we fix this?
1️⃣ Review the Error Details
First, I checked the technical details of the error
Server Name: .
Error Number: 18456
Severity: 14
State: 1
Line Number: 65536
The error number 18456 indicates a login failure—that part is clear. But what's more interesting is the State = 1, which is very important to fix the problem.
Here's a reference table for common state values and their meanings
State Value | Cause |
---|---|
1 | General error; the exact cause can't be determined (most difficult to troubleshoot) |
2 | User does not exist |
5 | Invalid user name |
6 | Attempt to log in using a Windows account name as a SQL Server login |
7, 8 | Incorrect password |
11, 12 | Account is disabled or can't login |
18 | Password change is required |
Since State 1 is vague, we need to verify the server's authentication settings next.
2️⃣ Check SQL Server Authentication Mode
Ensure that SQL Server and Windows Authentication mode is enabled.
- Log in using a Windows authentication account
- Right-click the server instance and select Properties
- In the left-hand panel, click on Security
- Make sure SQL Server and Windows Authentication mode is selected.
Once this is enabled, try logging in again with the sa
account.
3️⃣ Check if the sa
Account is Enabled
If you still get error 18456 with State 1, check whether the sa
account is enabled.
- Log in using a Windows authentication account
- In Object Explorer, expand the Security, then expand the Logins. Find the
sa
account and double-click it. - In the left-hand panel, click on Status
- Make sure Enabled is selected
Try logging in again. Easy fix. Job done☑️
Thanks for reading!
If you like this article, please don't hesitate to click the heart button ❤️
or follow my GitHub I'd appreciate it.
Top comments (0)