How To Fix SQL Server Error 18456 in Simple Ways

SQL Server Error 18456 Rectified With Ease

Database admins might encounter SQL Server error 18456 every once in a while. Users receive this error message when their attempt to connect to a live server is denied due to invalid credentials. It is a frustrating issue that reduces productivity.

Therefore, it becomes all the more essential to find a solution to resume business workflow. Thus, we wrote this article to provide users with the causes, solutions, and best practices to avoid this error. Let us begin our discussion by discovering this problem's underlying causes.  

Why Does Error 18456 SQL Server Authentication Occur?

The SQL Server error 18456 is further classified into separate states numbering from 1 to 133. These cover several different but related issues. Here we will go through the ones that are frequently encountered.

Login Disabled: (Error: 18456, Severity: 14, State: 1) There could be a case where a particular user has been locked out of the SQL Server. The DBA does it to prevent the user from accessing sensitive data.

Incorrect Credentials: (Error: 18456, Severity: 14, State: 5) Most users get this error for the first time when they try to log in with invalid credentials. It can either be an incorrect user id, a wrong password, or both.

Authentication Mismatch: (Error: 18456, Severity: 14, State: 6) Users may be attempting to log in via Windows authentication, whereas, by default, the server is set to SQL Server authentication. Users can verify it and change it accordingly on the SQL Server management console.

Database Not Found:(Error: 18456, Severity: 14, State: 38) In this state, the server refuses to open the database requested by the user. There are two different reasons for this. The first one is some issue in the connection string itself, and the other involves login via an expired password. 

Other minor reasons exist, such as remote login being disabled, permission issues, security policy updates, etc. In the end, most of these issues that cause error 18456 SQL Server authentication involve incorrect login credentials. So, figuring it out will fix the problem. It's now time to go over the various steps to solve this error.

SQL Server Error 18456 State 1 and the Steps To Fix It

This is the most basic error, so the user needs to follow only a simple procedure to resolve it.

Step-1. Ensure proper login credentials. It may seem very basic, but sometimes the simplest things are the ones that we forget to check.

Step-2. In state 1, the user is usually locked out of the server. To check whether a user is locked out, DBA can run the following query: 

ALTER LOGIN [UserLoginName] WITH CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF;

This command will temporarily disable passwords for that particular user. It is now the task of the DBA to reset the permissions by executing the following query:

ALTER LOGIN [UserLoginName] WITH PASSWORD = 'NewPassword';    

Procedure To Resolve State 5 of Error 18456 in SQL Server

This state indicates that the login attempt failed due to an invalid user account. Here are detailed steps to fix this error: 

Step-1. First of all, validate if the user account is still present on the server or not. It can be done by executing the following query on the SSMS:

USE master;

SELECT name FROM sys.sql_logins WHERE name = 'UserLoginName';

Step-2. Now if the user account is absent, simply create it using the following T-SQL command to clear up SQL Server error 18456:

USE master;

CREATE LOGIN [UserLoginName] WITH PASSWORD = 'UserPassword'; 

Step-3. Once the account is created, it’s time to grant the required Server-Level permissions for that the following command will do the trick.

USE master;

ALTER SERVER ROLE [sysadmin] ADD MEMBER [UserLoginName];

The above command adds the login account as a member of the sysadmin fixed server role, which grants it full administrative privileges. If a user has a different server role or needs specific permission, adjust the command accordingly.

Step-4. Next, check the default database assigned to the login account. Execute the following query:

USE master;

SELECT name, default_database_name FROM sys.sql_logins WHERE name = 'UserLoginName';

Step-5. Admin should make sure that the default database specified is valid and accessible. If the default database is incorrect or unavailable, they can change it by using the following query:

USE master;

ALTER LOGIN [UserLoginName] WITH DEFAULT_DATABASE = [UserDatabaseName];

Step-6. Ask the user to retry logging in with the fixed login credentials. Ensure that they provide the correct username, password, and default database (if necessary).

Apart from these steps, additional queries can be used to address the rest of the states. For the users who are facing this error. It is likely that invalid login credentials are not the only issue troubling their SQL Server. Therefore, users are recommended to go for the below-mentioned tool for complete recovery of their SQL server.

Complete Automated Solution for Error 18456 of SQL Server Authentication

There is no doubt that the manual procedure to figure out SQL Server error 18456 is complex and inefficient. However, users need not worry as the SysTools SQL Recovery Manager can sort out all issues. It is a must-have professional utility for those users who lack technical skills and want to solve their SQL Server problems. Given below are its easy-to-implement steps.

Step-1. Open the tool on your machine and choose the option as per your requirement.

Step-2. Browse and attach the master MDF file. This will start the scanning process.

Step-3. A list of all users on the SQL Server appears with the password column.

Step-4. Select the user you want the new password for, and then click on OK. 

Step-5. Start your SQL Server and verify the changed passwords for users.

Best Practices To Avoid SQL Server Error 18456

These expert-recommended guidelines are given to help users evade most of the situations that cause Error 18456. Also, by following these best practices, users can ensure secure and reliable logins every time.

1. Using strong and unique passwords for all users. Password should not be shared and include upper and lowercase letters, special characters, and numerical values.
2. Update and review login credentials regularly. This is to ensure that permissions are only available on a need-to-know basis and outdated credentials are revoked.
3. Monitor and limit login attempts for all users. As it is critical that unauthorized access is reported and important data is protected.
4. Maintenance of  Server connections. It is done so that the server does not lose connection, especially during working hours, as it can prove to be catastrophic.
5. Configure firewalls and implement network security. These measures are required to restrict access to the SQL Server from untrusted sources.

Conclusion

In this article, together with the causes of SQL Server error 18456, we also discussed the available solutions. Apart from this, we made users aware of the amazing qualities of a professional SQL management tool. By utilizing the tool and ensuring the best practices, users can ensure that their SQL server remains in the best condition.