Login Failed for SQL Server Authentication: Causes and Solutions

SQL Server Authentication allows users to connect to a SQL Server instance by providing a login name and password. However, users may sometimes encounter the error “Login failed for user” while connecting through SQL Server Management Studio (SSMS), an application, or another database client.

This issue commonly appears with SQL Server Error 18456 and indicates that SQL Server has rejected the login attempt. Incorrect credentials, disabled logins, authentication mode settings, expired passwords, and database access restrictions can all cause this problem.

In this guide, we will explain the common reasons behind SQL Server Authentication failures and explore practical ways to resolve them.

What Is the SQL Server Authentication Login Failed Error?

The error usually appears in the following format:

It occurs when SQL Server cannot authenticate the supplied login credentials or validate the user's access to the server. The error may appear when:

Connecting to SQL Server using a SQL login.
Logging in through SSMS with a username and password.
Connecting an application to a SQL Server database.
Using an outdated or incorrect connection string.
Accessing a SQL Server instance after changing its authentication settings.

The exact reason for the failure is often recorded in the SQL Server Error Log. Checking the associated error state can help identify the underlying issue.

Common Causes of Login Failed for SQL Server Authentication

Before applying a fix, identify what is preventing SQL Server from accepting the login. Some common causes include:

Incorrect username or password: The credentials entered in SSMS or the application do not match the SQL Server login.
Windows Authentication Only mode: The SQL Server instance does not allow SQL Server Authentication.
Disabled SQL login: The account exists but has been disabled by an administrator.
Expired password: The login password has expired or requires a change.
Missing login account: The specified SQL login does not exist on the target instance.
Incorrect default database: SQL Server cannot open the database assigned as the login's default database.
Insufficient permissions: The login exists but does not have the required server or database access.
Incorrect connection details: The application uses an incorrect server name, instance name, username, or database name.

Methods to Fix Login Failed for SQL Server Authentication

Follow the methods below to identify and resolve the authentication issue.

Method 1: Verify the SQL Server Username and Password

Incorrect credentials are among the most common reasons for Error 18456. Check whether the username and password entered in SSMS match the SQL Server login.

Follow these steps:

Open SQL Server Management Studio.
Select Database Engine as the server type.
Choose SQL Server Authentication.
Enter the correct login name and password.
Verify the server and instance name.
Click Connect.

If you are unsure about the password, check with the database administrator or use an authorized password reset method. Repeatedly trying incorrect passwords may also trigger account lockout policies where configured.

Method 2: Enable SQL Server and Windows Authentication Mode

SQL Server supports Windows Authentication and SQL Server Authentication. If the instance is configured for Windows Authentication only, SQL logins will not work.

To enable Mixed Mode Authentication:

Open SSMS and connect using an account with sufficient administrative permissions.
Right-click the SQL Server instance in Object Explorer.
Select Properties.
Open the Security page.
Under Server Authentication, select SQL Server and Windows Authentication Mode.
Click OK.
Restart the SQL Server service for the change to take effect.

After restarting the service, try connecting again using SQL Server Authentication.

Method 3: Check Whether the SQL Server Login Is Disabled

An administrator may disable a SQL Server login, preventing the account from connecting even when the correct password is supplied.

To check the login status:

Connect to SQL Server using an administrator account.
Expand the Security folder in Object Explorer.
Open the Logins folder.
Right-click the affected login and select Properties.
Open the Status page.
Under Login, check whether the login is enabled.
If it is disabled, select Enabled and click OK.

You can also enable a login using T-SQL:

ALTER LOGIN [LoginName] ENABLE;

Replace LoginName with the actual SQL Server login name. Run this command only with the required administrative permissions.

Method 4: Check the SQL Server Error Log and Error State

SQL Server records additional information about failed login attempts in its Error Log. This information can help you determine whether the problem is related to an incorrect password, disabled account, or authentication configuration.

To check the error log:

Open SSMS and connect to the SQL Server instance.
Expand Management in Object Explorer.
Expand SQL Server Logs.
Open the current log.
Search for the message Login failed for user.
Review the associated error state and reason.

Some commonly encountered error states include:

State 2 and 5: The login ID is invalid or does not exist.
State 7: The login is disabled and the password may also be incorrect.
State 8: The supplied password is incorrect.
State 18: The password must be changed.
State 38: SQL Server could not open the requested database.
State 58: SQL Server is configured for Windows Authentication only, but a SQL login was used.

The error state provides useful diagnostic information, although other states may also occur. Refer to Microsoft's documentation for the complete explanation of Error 18456.

Method 5: Verify the Default Database and Login Permissions

Sometimes SQL Server accepts the login credentials but cannot open the database assigned to the login. This can happen when the default database is offline, unavailable, or inaccessible to the user.

To resolve this issue:

Connect to the SQL Server instance using another account with administrative permissions.
Expand Security > Logins.
Right-click the affected login and select Properties.
Open the General page.
Check the assigned default database.
If the database is unavailable, select another accessible database, such as master.
Click OK and try connecting again.

Also verify that the login has the required database user mapping and permissions. If the login is not mapped to a database user, an administrator can create the appropriate mapping and grant the necessary access.

Method 6: Verify the Application Connection String

If the login failure occurs while connecting through an application, the issue may be caused by incorrect connection string details.

Check the following parameters:

Server: Confirm the SQL Server hostname and instance name.
Database: Verify that the requested database exists and is accessible.
User ID: Ensure that the SQL Server login name is correct.
Password: Check whether the application is using the latest password.
Authentication: Confirm that the connection string uses the intended authentication method.

If the SQL Server password was recently changed, update the stored credentials in the application's configuration file or connection settings.

Reset a Forgotten SQL Server Login Password Using Professional Tool

If the login failure occurs because you have forgotten the SQL Server password, verifying the connection settings will not resolve the issue. You need to regain access to the affected SQL login by resetting its password.

For such situations, SysTools SQL Password Recovery Tool provides an alternative to manually managing the password through SQL Server. It works with the SQL Server master.mdf file and allows users to reset the password of individual SQL logins, including the SA account.

The tool can be useful when the SQL Server password is unavailable and you cannot authenticate through SSMS to perform a regular password change. It also supports password reset from an offline Master MDF file without requiring a SQL Server installation.

Steps to Reset a SQL Server Password:

Install and launch the the advanced tool.
Browse and add the SQL Server master.mdf file.
Allow the software to scan the file and display the available SQL login accounts.
Select the login account whose password needs to be reset.
Choose the password reset option.
Enter a new password and apply the changes.
Use the updated credentials to connect to SQL Server through SSMS.

Important: Stop the SQL Server services before loading the Master MDF file. Always create a backup copy of the original file before performing the reset. The software works with a healthy Master MDF file and does not repair corrupted SQL Server databases.

Best Practices to Avoid SQL Server Authentication Failures

Follow these practices to reduce recurring login issues:

Maintain a secure record of SQL Server login credentials.
Use strong passwords and follow your organization's password policies.
Avoid disabling or deleting SQL logins without checking their dependencies.
Verify authentication mode before configuring application connections.
Keep application connection strings updated after password changes.
Regularly review SQL Server Error Logs for authentication failures.
Assign only the permissions required by each SQL login.
Maintain regular backups of SQL Server system databases, including the Master database.

Conclusion

The Login failed for SQL Server Authentication error can occur due to incorrect credentials, disabled accounts, authentication mode restrictions, or database permission issues. SQL Server Error 18456 provides a starting point for diagnosing the problem, while its associated error state can help identify the specific cause.

If you have forgotten the SQL Server login password and cannot access the account, a professional utility offers a way to reset the password using the Master MDF file. Once the password is reset, you can use the new credentials to connect through SSMS and regain access to the SQL Server login.

blog