Management studio error 18456

MSSQLSERVER_18456

[!INCLUDE SQL Server]

Details

Explanation

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.

User action

The following examples are some of the common login failures. Select the exact error that you’re experiencing to troubleshoot the issue:

• Login failed for user ‘<username>’ or login failed for user ‘<domain><username>’

• Login failed for user ‘NT AUTHORITYANONYMOUS’ LOGON

• Login failed for user ’empty’

• Login failed for user ‘(null)’

Login failed for user ‘<username>’ or login failed for user ‘<domain><username>’

If the domain name isn’t specified, the problem is a failing SQL Server login attempt. If the domain name is specified, the problem is a failing Windows user account login. For potential causes and suggested resolutions, see:

Also, check the extensive list of error codes at Troubleshooting Error 18456.

For more troubleshooting help, see Troubleshooting SQL Client / Server Connectivity Issues.

Login failed for user NT AUTHORITYANONYMOUS LOGON

There are at least four scenarios for this issue. In the following table, examine each applicable potential cause, and use the appropriate resolution:
See the note below the table for an explanation of the term double hop.

[!NOTE]
A double-hop typically involves delegation of user credentials across multiple remote computers. For example, assume you have a SQL Server instance named SQL1 where you created a linked server for a remote SQL Server named SQL2. In linked server security configuration, you selected the option Be made using the login’s current security context. When using this configuration, if you execute a linked server query on SQL1 from a remote client computer named Client1, the windows credentials will first have to hop from Client1 to SQL1 and then from SQL1 to SQL2 (hence, it’s called a double-hop). For more information, see Understanding Kerberos Double Hop and Kerberos Constrained Delegation Overview

Login failed for user (empty)

This error occurs when a user tries unsuccessfully to log in. This error might occur if your computer isn’t connected to the network. For example, you may receive an error message that resembles the following one:

Source: NETLOGON
Date: 8/12/2012 8:22:16 PM
Event ID: 5719
Task Category: None
Level: Error
Keywords: Classic
User: N/A
Computer: <computer name>
Description: This computer was not able to set up a secure session with a domain controller in domain due to the following: The remote procedure call was cancelled.
This may lead to authentication problems. Make sure that this computer is connected to the network. If the problem persists, please contact your domain administrator.

An empty string means that SQL Server tried to hand off the credentials to the Local Security Authority Subsystem Service (LSASS) but couldn’t because of some problem. Either LSASS wasn’t available, or the domain controller couldn’t be contacted.

Check the event logs on the client and the server for any network-related or Active Directory-related messages that were logged around the time of the failure. If you find any, work with your domain administrator to fix the issues.

Login failed for user ‘(null)’

An indication of «null» could mean that LSASS can’t decrypt the security token by using the SQL Server service account credentials. The main reason for this condition is that the SPN is associated with the wrong account.

To fix the issue, follow these steps:

1. Use the SQLCheck or Setspn.exe to diagnose and fix SPN-related issues.

2. Use SQLCheck to check whether the SQL Service account is trusted for delegation. If the output indicates that the account isn’t trusted for delegation, work with your Active Directory administrator to enable delegation for the account.

3. Diagnose and fix Domain Name System (DNS) name resolution issues. For example:

   • Ping IP address by using PowerShell scripts:

     • ping -a <your_target_machine> (use -4 for IPv4 and -6 IPv6 specifically)
     • ping -a <your_remote_IPAddress>

   • Use NSLookup to enter your local and remote computer name and IP address multiple times.

4. Look for any discrepancies and mismatches in the returned results. The accuracy of the DNS configuration on the network is important for a successful SQL Server connection. An incorrect DNS entry could cause numerous connectivity issues later.

5. Make sure that firewalls or other network devices don’t block a client from connecting to the domain controller. SPNs are stored in Active Directory. If the clients can’t communicate with the directory, the connection can’t succeed.

Additional error information

To increase security, the error message that is returned to the client deliberately hides the nature of the authentication error. However, in the [!INCLUDEssNoVersion] error log, a corresponding error contains an error state that maps to an authentication failure condition. Compare the error state to the following list to determine the reason for the login failure.

Other error states exist and signify an unexpected internal processing error.

More rare possible cause

The error reason An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. can be returned in the following situations.

• When the server is configured for mixed mode authentication, and an ODBC connection uses the TCP protocol, and the connection doesn’t explicitly specify that the connection should use a trusted connection.

• When SQL server is configured for mixed mode authentication, and an ODBC connection uses named pipes, and the credentials the client used to open the named pipe are used to automatically impersonate the user, and the connection string doesn’t explicitly specify the use of a trusted authentication.

To resolve this issue, include TRUSTED_CONNECTION = TRUE in the connection string.

Examples

In this example, the authentication error state is 8. This indicates that the password is incorrect.

[!NOTE]
When [!INCLUDEssNoVersion] is installed using Windows Authentication mode and is later changed to [!INCLUDEssNoVersion] and Windows Authentication mode, the sa login is initially disabled. This causes the state 7 error: «Login failed for user ‘sa’.» To enable the sa login, see Change Server Authentication Mode.

See also

• 0420 Reasons for Consistent Auth Issues