How to Fix SQL Server Error 18456: Cannot Open Database Requested by the Login

Why does SQL Server Authentication Error 18456  Occur?

This SQL Server error can occur due to one of the following reasons:

• Default Database is Offline: The explicitly specified database for SQL Server login is offline, unavailable, or corrupted.
• Missing User Permissions: The user doesn't have permission to the requested database.
• Missing or Incorrect Credentials in Connection String: The connection attempt can fail if the connection string has incorrect syntax, server name, or user credentials. The State 38 means that the database explicitly specified in the connection string or in SSMS is incorrect, offline, or does not exist.
• No Admin Privileges: You might not be running the SQL application (SSMS) with admin credentials.
• Deleted Login Post-Migration: The Login was deleted after migrating the database to a contained database user.

• Incorrect Password: You're using SQL Server Authentication, but the password you specified for SQL Server login is incorrect
• Incorrect SQL Login Account: The SQL instance may reject the connection if it fails to find a login matching the provided name. This usually happens if the account was never created on the server or the username is typed incorrectly.
• Disabled Authentication Type: SQL logins are not enabled. For example, you're trying to use SQL Server Authentication when the server instance is configured for Windows Authentication mode only.
• Group Membership and Permission Issues: The connection can fail when you try to access the SQL Server via a Windows group. It usually happens if the group lacks server permissions, the user identity cannot be resolved in Active Directory (AD) due to domain controller connection issues, or you might be accessing a cross-domain group located in the wrong domain.

Check SQL Server Logs for 18456 Error

SQL Server intentionally hides the exact reason and nature of the authentication error 18456 on the client side to keep the system safe. However, it records a specific State number in the logs. You need to check the SQL server logs for the error state that indicates the cause of failure. For example, 2 or 5 indicates an invalid user ID, 8 or 9 incorrect password, and 38 or 46 fails to find database requested by user. These can help you troubleshoot the authentication issues.

To check for the State number in 18456 error, you can use SSMS (if accessible) or the Windows Event Viewer. If you fail to connect to server instance using SA account, try using an alternative administrative account to open SSMS. For example, Windows Authentication.

After connecting to the server instance, under Object Explorer, expand Management section, click SQL Server Log, and then click View.

Search the SQL Server error logs for “18456”. You will find the error details, including the state number and reason, listed on the line directly above it.

If you cannot access the SSMS, follow this path to open the SQL Error log:

Alternatively, open the Windows Event Viewer to check the error logs. To open Event Viewer, type eventvwr in Run window and click OK.

In Event Viewer window, expand Windows Logs and select Application.

Go to the right pane. Under Actions, select Filter Current Log…

In the Filter Current Log window, select MSSQLSERVER (or your specific instance name) as the Event source, enter 18456 in the Event IDs field, and click OK.

Troubleshooting SQL Server Login Failed Error 18456

You can follow the methods mentioned below to fix the authentication failure error 18456 in SQL Server.

Method 1- Enable SQL Server Logins

The error 18456 Login failed for user may occur when you try to log in with a SQL password, but the server is set to allow "Windows Authentication Only." In this case, you can enable the SQL logins. To do this, you can either use an Integrated Authentication or follow the below steps:

• In SQL Server Management Studio, right-click on the SQL Server name in Object Explorer and select Properties.
• Under Server Properties window, click Security.

• Select the SQL Server and Windows Authentication mode. Select OK.

• Restart SQL Server services for the changes to take effect.
• Once you’ve enabled SQL logins, you must also enable the login for System Administrator account (sa). In Object Explorer, expand Security > Logins, right-click sa, and select Properties.

• Under Select a page, click Status.

• In Status window, under Settings, select Enabled for the Login option and click OK to save the applied changes.

• If the sa account still shows a red cross, right-click the account and select Refresh to update its status.

Once refreshed, the red icon will disappear, confirming the account is enabled.

If you still get the error, check your password again.

Method 2 - Check and Change the User’s Default Database

You may encounter the login error 18456, when the SQL Server fails to connect to the default database. It occurs right after authentication, if the database is unavailable due to being dropped, renamed, or offline. You can check and reset the user’s default database to master by using the following command:

Alternatively, you can change default database to master by following these steps:

• In SSMS, under Object Explorer, select Security > New > Login…

• In the Login-New window, under the General section, change the default database to Master. Click OK.

This method of resetting the database to master only works if the application relies on the server's default settings. However, if the application is specifically looking for a missing explicitly specified database, this command will not fix the error. You can change the application's connection settings instead.

Method 3 - Verify Username and Password Credentials

The SQL Server Error 18456 'Login failed for user' can also occur if you provide an incorrect username or password, while using SQL Server Authentication. To confirm this, you can check the error log for the reason “Password did not match that for the login provided.”

To fix the issue, try to use the correct password in your application. In case you forgot the password, take the help of a SQL Admin or use Stellar Password Recovery for MS SQL to reset the System Administrator account (SA) and user passwords.

You can also verify the credentials outside the application by creating a UDL file. According to Microsoft documentation, the UDL file helps to test the connection by using the SQL Server OLE DB Driver.

Method 4 - Check and Grant System Admin Permissions

You may encounter the error 18456 when you attempt to log into SQL Server using Windows Authentication through an AD group. This indicates that the server instance has rejected your connection request. This connection failure usually occurs if the AD group has not been added as a server login, if cross-domain trust restrictions prevent visibility, or if SQL Server fails to verify the group membership token. To resolve this, you can check the existing login configuration and grant the sysadmin server role directly to that specific Active Directory group. Here’s how:

Note: Only the authorized admin like security admin can grant server-level roles and permissions.

• Open your SQL Server Management Studio (SSMS), expand the Logins folder, right-click on NewSA, and select Properties.

• Select the Server Roles and then check the option sysadmin. Click OK.

Check SQL Server Database for Corruption

It is good practice to check if all the databases are healthy. You can use DBCC CHECKDB command to detect corruption. This command displays the integrity issues and helps to resolve database corruption. However, it may cause data loss while repairing severely corrupt databases.

To prevent data loss, you can use Stellar Repair for MS SQL Technician. It can repair corrupt SQL database including master .mdf files and restore the data, without requiring backup.It allows you to repair a SQL database when corruption is severe.

If you forgot the SA account credentials, then use the tool’s utility - Stellar Password Recovery for MS SQL. It resets SA and user passwords, as well as the MDF file's password, ensuring you regain control of your SQL Server environment quickly and securely.

Conclusion

The error 18456 “Login failed for user” in Microsoft SQL Server can occur due to various reasons, like incorrect credentials, incorrect server authentication mode, and others. It can lead to downtime if administrator credentials are lost or if database metadata corruption locks users out. You can follow the methods discussed above to check and resolve the error. If your SQL database is corrupted, then you can use Stellar Repair for MS SQL Technician.