Are you getting this error while trying to connect to the SQL Server, Microsoft SQL Server error 18456? This error code simply means a login failure, especially if you’re trying to connect using SQL Server Authentication. In this article, we will discuss the SQL Server error in detail and learn how to troubleshoot 18456 SQL Server authentication error by using a few easy DIY tricks.
Microsoft SQL Server Error 18456
Receiving error messages is one of the major SQL Server problems. Generally, an error message comes up with a description that gives you a hint about what has gone wrong. Unluckily, you get no hint on the screen during “18456 SQL Server authentication error”. As far as I’m concerned, the SQL Server error “Login Failed for User” means that you’ve entered the invalid credentials when logging into SQL Server.
There are various reasons highly responsible for this typical login error, such as
- The password you entered is incorrect;
- The login doesn’t exist or you typed it incorrectly;
- You’ve forgotten the password or login;
- The system administrator (SA) has mistakenly modified the password;
- A malicious activity has reset the password;
- Maybe you’ve migrated the database but not the logins;
- Or maybe the master database is corrupted.
Troubleshooting this SQL Server Error
Above we’ve gone through the most possible reasons for 18456 SQL Server authentication error. Now let’s discuss how to troubleshoot this SQL Server error. Remember, troubleshooting an error ain’t always an easy task. Sometimes an error can be difficult to track down and resolve, especially when you don’t know the real reason behind the error. However, you should try your best to resolve this SQL Server error. I’m sharing a few easy DIY tricks below:
Reset SQL Password
Since “SQL Server authentication error 18456” is primarily associated with the login failure, this means maybe you’re entering the incorrect password. So if you’ve forgotten the password, you should ask the database administrator (DBA) to reset SQL password. One of the easiest ways to reset SQL password is by using SQL Server Management Studio. Follow these simple steps:
Step 1: First, start SQL Server Management Studio. In the Object Explorer pane, click Security → Logins.
Step 2: Now right-click the user, and click Properties in the options.
Step 3: On the Login Properties dialog box, select the login and change the password.
Note: You can also use T-SQL to create users and change the password if you don’t want to use SQL Server Management Studio.
If you fail to reset SQL password through SSMS or T-SQL, you should consider using a SysInfoTools SQL Password Recovery tool. This tool can successfully unlock the SQL Server user account and reset a new password to access it.
Check SQL Server Authentication
If resetting the password doesn’t resolve this SQL Server error, you should check the SQL Server authentication. Maybe the SQL login authentication is disabled. Generally, there are two types of authentication in SQL Server:
- Windows Authentication mode
- SQL Server and Windows Authentication mode
If you’re trying to log in with the SQL login and the authentication is set to Windows, you’re likely to receive Microsoft SQL Server error 18456. In order to resolve this issue, you should change the authentication mode by using SQL Server Management Studio. Follow these simple steps:
Step 1: First, start SQL Server Management Studio. Connect to the SQL Server instance and open the server properties dialog.
Step 2: On the Properties dialog box, go to Security and change the Windows Authentication to SQL Server.
After that, you need to restart the Microsoft SQL Server program.
Note: If you don’t want to use SQL Server Management Studio, you can use the Registry Editor to modify the authentication mode.
If nothing is working out in your favor, maybe the master database is corrupted. You should use a SysInfoTools SQL Database Recovery tool to repair SQL database.
In this article, we’ve discussed 18456 SQL Server authentication error(Microsoft SQL Server Error 18456), one of the most common SQL Server problems. Also, we’ve learned how to troubleshoot this SQL Server error by using a few easy DIY tricks.