When accessing a database through MS SQL Server, you may come across Error 18456. This error occurs mostly because of an authentication failure that triggers the rejection of a connection attempt. Several reasons may lead to this issue, such as:
- Outdated password
- Invalid credentials
- Issues with SQL server authentication
- Unavailable database
- disabled user account
- The server configured only for Windows authentication
Whenever the system flashes the error code, it will accompany the description. This will let you fix the underlying issue and proceed with the login again. Some of the common examples of this error are:
• Login failed for user ” or login failed for user ‘\’
• Login failed for user ‘NT AUTHORITY\ANONYMOUS’ LOGON
• Login failed for user ’empty’
• Login failed for user ‘(null)’
You get this error message when a connection attempt is rejected because of an authentication failure. User logins can fail for many reasons, such as invalid credentials, password expiration, and enabling the wrong authentication mode. In many cases, error codes include descriptions.
You may also use the DBCC CHECKDB command when restoring a database to determine if it contains error 18456. Before restoring the database, fixing the error becomes inevitable, if you find any. While the manual way is common, automated third-party tools are also becoming popular to perform SQL recovery in any such event.
Fixing SQL Server Database Error 18456 in SSMS
Let us deal with Error 18456 with the help of SQL Server Management Studio. Here is the step-by-step procedure.
Step 1: Login remotely with the correct credentials
Start resolving the error by connecting directly to the SQL Server with the help of a Remote Desktop Connection. Make sure to enter the correct credentials. If you have any doubt about your password, reset it while abiding by the password policy requirements of the SQL Server.
Step 2: Start Microsoft SQL Server Management Studio
After logging in to the SQL Server, run MS SQL Server Management Studio, the ideal tool to configure and manage MSSQL. By default, the tool has Windows Authentication enabled. To remove the error, you should login as the Windows Administrator. Alternatively, you can also login with the SQL Admin account used during the time of installation and configuration of MSSQL.
Step 3: Check the availability of the database
Are you unable to access your desired database due to its inaccessibility or offline status? In any such situation, you should bring the database online and make it accessible before proceeding with the solution for Error 18456. In case of any issues, connect with your database administrator for further assistance.
Step 4: Check the Server Authentication Mode
After you login to SSMS through Windows Authentication, the next step is to inspect the security settings. This will ensure that MSSQL allows both the authentication modes – SQL and Windows authentication. Logging in with a disabled or incorrect mode will flash error 18456. To check the mode and enable both modes follow these crucial steps:
- Launch SQL Server Management Studio.
- Connect with the default authentication mode (Mostly Windows)
- In the Object Explorer, look for the Server instance on the top
- Right-click the Server name and select Properties
- In the Server Properties dialog box, click Security
- Look for Server Authentication at the top
- If you find Windows Authentication Mode checked by default, it can be the reason behind Error 18456, Login Failed for User ‘’
- Change the Server Authentication option to SQL Server and Windows Authentication mode
- Click OK to allow you to login through the user name and password of both SQL Server and Windows user account.
Step 5: Restart the SQL Server
To ensure that your new setting work fine, you need to restart your server by going through the below easy steps:
• In SSMS, under the Object Explorer, right the Server instance
• Select and click Restart to apply the new Server Authentication setting
• Disconnect and then reconnect to SSMS
• From the authentication drop-down options, choose SQL Server authentication
• Provide your SQL Server login credentials and click the connect button.
Step 6: Reset user permission
When going through the SQL user permissions, try to find out if:
• The user account is enabled
• The user has permission for database access
• The user has a valid password
If you are unable to find positive answers to all these pointers in the SQL Server, you need to reset or update the user access. Here are the steps for the same:
• Open SQL Server Management Studio
• Connect your Server
• In the Object Explorer panel, Click on ‘+’ sign corresponding to Security to expand it
• Click on the ‘+’ sign corresponding to Logins to expand it.
Find the user that is unable to log in. A red ‘x’ sign denotes disabled login for the user
You need to revoke the login permission for the user. For this, do the following:
• Right-click the user -> Properties -> Status Page
• Under the Login option, click Enabled ->OK
• If required, reset the password by clicking the General tab
Step 7: User Mapping to the Database
One last step is to check the role permissions and memberships to verify proper user access.
• In the Login Properties, Click User Mapping
• Select the desired database from the given list
• Check out the database role memberships and choose the membership that you desire.
To find more reasons behind authentication failure, which leads to Error 18456, check the SQL server error log.
• In SSMS under the Object Explorer panel, expand the server instance
• Click on Management -> SQL Server Logs
• Click the selected files to find the entries around the time when you faced errors. It will give you detailed reasons for the failure.
Fix SQL Server Database Error 18456 using a SQL Recovery Tool
Manual recovery is a time-consuming process and it may not fix the SQL Server Database Error 18456 completely. A comparably better alternative is to use Stellar Repair for MS SQL. The third-party software from Stellar helps you to fix the error and recover your database quickly. It is an ideal solution especially if
- DBCC CHECKDB command fails to remedy the situation with its multiple repair options.
- DBCC CHECKDB returns an error when you try to access severely corrupt SQL database files.
Stellar Repair for MS SQL can fix all such types of corruption errors arising in SQL databases including Error 18456.
To buy the software, you can click through the official website of Stellar Info. Different types of versions are available for purchase depending on your requirement for database repair and restore.
Conclusion
Encountering SQL Server Database Error 18456 is common when accessing the database. To resolve this issue, seeking the help of SQL Server Management Studio would be a wise move. The step-by-step procedure is easy to handle and can help to fix the error. You may also use the DBCC CHECKDB command to deal with this error using different repair options.
However, they may consume considerable time and give no assurance for complete SQL recovery. Using Stellar Repair for MS SQL is an ideal alternative. Recommended by Microsoft MVPS and tried successfully by countless small and big businesses, this third-party tool works wonders in fixing SQL Server Database Error 18456.
Besides, it also fixes several other errors that are hard to fix manually, by using the SSMS interface or through the DBCC CHECKDB command. Purchase any of its paid versions from the official website of Stellar Info. Once you buy the software, your complicated database tasks will become much easier and convenient to handle.
