DEV Community

FakeStandard
FakeStandard

Posted on

SQL Server Login Failed for 'sa' (Error 18456): How to Fix It?

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
Enter fullscreen mode Exit fullscreen mode

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.

  1. Log in using a Windows authentication account
  2. Right-click the server instance and select Properties
  3. In the left-hand panel, click on Security
  4. 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.

  1. Log in using a Windows authentication account
  2. In Object Explorer, expand the Security, then expand the Logins. Find the sa account and double-click it.
  3. In the left-hand panel, click on Status
  4. 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)