How to Fix Login Failure SQL Server Error Code 18456?

Microsoft SQL Server is a database management software that holds a lot of data offered by multiple software applications. It has crucial role and very high demand in the digital world. With such complex functions by SQL Server, it is not completely immune from logical errors. The error like when you are trying to login on your SQL Server and instead of getting on to the server application, receives this message troubles a lot to the Server users.

The error says “Login failed for ‘username’” with error code 18456. By reading this error, any user can infer that there is some issue in login of the SQL Server and check out this error code resemblance online which says authentication failure generally. However, any reason behind this login failure is not stated in this error message. SQL Servers users or administrators can check details about the SQL Server 18456 error in the Event Log.

The SQL Server user facing this error message can conclude these reasons which could be responsible for this login failure error.

• Authentication (Windows or SQL Server) error – cross logins and settings (Login credentials of SQL Server and Windows Authentication is enabled)
• Invalid User Name for SQL Server account
• Invalid Password for SQL Server account
• Password has been modified
• Spelling Error in Username/Password
• Unavailable user login in SQL Server database
• User account password has been expired
• User is using different passwords for different users
• User account is deleted and unavailable on the server
• Failure in Server access
• Logins did not migrate in the migration
• Master database or login is damaged
• Incorrect combination with different passwords for different servers by the same user

As we know that the error does not state or explain the cause for the login issue, it is our responsibility and need to check upon the above possible reasons and make related corrections or perform the feasible solutions for it. Please read out and understand the next section for it.

Manual Methods to Resolve “Login Failure SQL Server Error Code – 18456” Issue

The primary solutions include restarting the SQL Service and try connecting the SQL Server using the Remote Desktop Connection. However, this does not resolve the issue most of the times for which we have some available manual fix for the error.

Please go through the below explained manual solutions to fix this login issue in the SQL Server.

1. Ensure Login Credentials are Correct

If you have selected the authentication as “SQL Server authentication” for logging on the SQL Server, make sure that login credentials, i.e. username and password are correctly entered with correct spelling. Also, you should make sure that the login is available in the database but not deleted owing to certain migration to some other database intervention.

2. Enable Mixed Authentication Manually – For SQL Authentication Access

It is quite a chance that for the first time SQL Server user who wants to login via SQL Server authentication; they are ignored by the fact that Windows Authentication is enabled in the Security section in Server properties. Users can check upon this condition even if they are not the first time user of SQL Server.
Follow these simple steps to check the enabled Authentication mode on your SQL Server.

1. Search Microsoft SQL Server Management Studio (MSSMS) on your system, right-click on it and select Run as Administrator option.
2. Once the MSSMS is opened, move to the Object Explorer pane on the left of the screen. Select your SQL Server and right-click on it. Then click on Properties option.
   
3. The Server Properties page will get open. Here, click on Security option on the left panel. Now under Server authentication section, select the option SQL Server and Windows Authentication mode. Click OK to set it.
   
4. Move again to the Security tab on the left panel, expand it and click on Logins option. Select your Server name, right-click on it and click on Properties.
5. Login Properties page will get open. Enter the password details and confirm it for your SQL Server account.
6. On the same Login Properties page, click on the Status tab on the left panel, choose Enabled option under Login section and click on OK.
   
7. Restart the SQL Server, go to the top of MSSMS Object Explorer pane, select the Server, right-click on it and click on Restart option.
8. Check if you can login to your SQL Server account now.
   Note: SQL administrators can also make use of the Windows Registry Editor path –
   machine\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLXX.MSSQLSERVER\MSSQLServer

   You can modify the value to 1 for Windows Authentication or 2 for mixed mode.

3. Provide Admin Rights to User – For Windows Authentication Access

To continue using the SQL Server via Windows Authentication without this login failure error, SQL Server administrator must assign Admin Rights to user by creating new login for the user account and assigning “sysadmin” under Server Roles to the user. Here are the steps.

1. Run the Microsoft SQL Server Management Studio as administrator.
2. On the Object Explorer pane on left, navigate to Security>>Login. Choose the desired login account, right-click on it and then select New Login option.
   
3. A new page namely Login-New will get open. Under Login Name section, select Windows authentication option and click on Search tab.
   
4. Enter the user name of the selected account with domain. Verify it by clicking on Check Names option and then click on OK.
   
5. Next on the Login-New page, click on Server Roles option from the left panel. Choose sysadmin option under Server Roles on the right panel.
6. Restart the SQL Server and login via Windows Authentication for the confirmation.

The above manual solutions are illustrated for helping users fix the “login failure SQL Server error code – 18456”. You can give a try to the solutions one-by-one and estimate yourself the best option for you. But, if you still don’t find the solution, you can directly contact to the SQL Server administrator or Microsoft Helpline for help.

These errors are logical and properties based which can be corrected with simple intervention of users systematically. Some SQL Server errors originate due to corruption in its database file, i.e. MDF or NDF files. Although, reasons behind the corruption in SQL database cannot be certain, the result is always frightening. And we are sorry to state that there is no perfect manual solution to fix corruption in SQL Server database files. The only reliable and assured solution is using an efficient third party database recovery tool. Check out the most robust and result-driven SQL Server Recovery tool which guarantee the successful and quick recovery of the SQL database file. The tool performs automatic and smart recovery from all SQL Server versions’ database files including the smartest filter options for selective SQL objects recovery. Stay relaxed as your SQL data is always safe with this advanced tool.

Download Now

Keep reading for more knowledge about SQL Server related topics and solutions!