SQL Server

SQL Error 18456 Login Failed for User ‘SA’ – Fixed

Synopsis: The agenda of this article is to solve the SQL error 18456 login failed for user ‘sa’ using the best method. However, users must be technically proficient to fix this error. The error mainly restricts users from logging in to the SQL Server. Therefore, along with errors, we will be adding the causes for users to avoid.

SQL is in the list of top RDBMS worldwide along with MySQL, Oracle, etc. However, just like any other database, errors are also present here. Microsoft SQL Server, Error 18456 is one of them. The error itself is not a severe one but can cause huge damage to SQL users.

We’re going to look at the major causes here first. Then, we can move ahead towards the available solutions. To follow the chronology of causes and solutions, users can have complete knowledge of this error in the system.

Why Uses Get SQL Error 18456 in the System

There are multiple factors & reasons why users get this error. But mostly, there are some common causes for it. As we can not mention them all, we’re mentioning the most common causes observed by SQL users, experts, & MVPs.

Database in Single-User Mode: If the SQL database is in single-user mode, it will not allow users to log in. Evidently, by running the Alter Database command, putting the database in multi-user mode is a necessity.

Windows Authentication Failure: the false configuration of Windows authentication causes this login error. The solution for this SQL error 18456 login failed for user ‘sa’ is mentioned below to configure authentication the right way.

Issues with Login ID & Passwords: Incorrect usernames & passwords are the most common cause of why users face this 18456 error in the SQL Server database. Make sure to cross the credentials before logging in.

Security Policies: There are SQL security policy that locks an account after a certain number of failed login attempts. Therefore, users cannot log in again until they unlock the account back to the normal or its original state.

Damaged Database Files: In case, the MDF files of the server are corrupted, users might face this error. Therefore, users need to go through the MDF Recovery process to get rid of the entire problem from its root.

Expired User SA Account: Trying to log in to an expired user account is another reason why error 18456 pops up. Make sure that the Windows user account linked to the SQL login is not expired & is running.

Permissions Barrier: Restricted permissions to the user account can cause the Microsoft SQL Server, Error 18456 in the system. The admin must grant access to that specific user for desired operations.

Miscellaneous Causes: Apart from these, there are plenty of other causes. This includes Failed Login Auditing, Changed Passwords, SQL Server configuration Manager Settings, Database Role Membership, etc.

Switching Authentication Mode to Fix SQL Error 18456 Login Failed

Verify the SQL Server Authentication mode first. Moreover, in case users find errors here, they need to fix them one after another respectively. Users need to make sure that the authentication mode is configured in such a way that it supports both Windows & SQL Server authentication.

1. Open SSMS (SQL Server Management Studio) in your system.
2. Go to the Object Explorer window and then Right-Click the Server Name.
3. Now, Select the Properties option to proceed.
4. Here, users need to Click the Security page option.
5. If Windows Authentication is the only mode, users get the SQL error 18456 error.
6. Log into SQL Server with Windows credentials or an SQL user name and password.
7. Users must allow SQL Server and Windows Authentication as per their requirements.

Tip: After making these changes, kindly restart the SQL Server to reflect the changes.

Cross Check User Permissions to Fix SQL Error 18456 Login Failed for User

Now, users need to verify if the user permissions are enabled or not. If not, follow the below procedure.

1. Firstly, Expand Security and then Logins under the Object Explorer option.
2. Find the troubled user-facing login problem. In case there is an ‘X’, it means this login is disabled for the user.
3. Enable login by Right-Clicking the user and selecting the Properties option.
4. Go to the status page & then Click on the Enable Login option for the user.
5. Now, users should be able to log in & the ‘X’ must be removed automatically. 
6. Just Right-click on the user & proceed to further troubleshoot user errors.
7. Choose the General Page under the Properties tab.
8. Enter a New Password before entering the verifying password.
9. Finally, Hit the OK button to save this new password.

Now, let’s move to the final check which is user mapping to fix the SQL error 18456 login failed for user error.

User Mapping Task to Connect User with SQL Database

After user mapping, users need to verify that as well. The users need to verify the user mapping to cross-check if the user has database access or not. The respective steps are mentioned below:

1. Open the SSMS to begin.
2. Simply Right-Click on the User.
3. Now, Select the Properties option.
4. Hit the User Mapping page & proceed.
5. Select the desired database from the list of all databases.
6. Set the memberships from the database role memberships.
7. Click the OK button to finish.

Bringing It All Together

Now we know how to fix SQL error 18456 login failed for user issue. There is no harm in using manual solutions until users are experts in SQL technicalities. Otherwise, select the automated solution to get better results. As this is quite a technical method, users should have a deep understanding of SQL Server databases.