Troubleshooting Error: 18452 Login failed for user


Error: 18452 Login failed for user ‘null‘, …


          “Null” or ‘’ means that client windows token is not trusted


          Kerberos authentication is not available, fallback to NTLM (not in a domain, SPN not registered)


          Solution: Create the same account, password on the SQL Server


          Delegation may be required (at least one hop exists)


          Solution: use SQL Authentication or enable delegation


          SQL Server service account cannot query domain


 


Error: 18456 Login failed for user ‘name‘


          ‘Name’ means that principal is recognized, but not authorized to connect


          “Login Failed for user ‘NT Authority\ANONYMOUS’ LOGON“


          Reason: client is running under LocalSystem connects using NTLM


          Solution: Create a dedicated account on both machines


          “Login failed for user ‘<domain>\<machinename>$’ “


          Reason: client is LocalSystem or NetworkService


          Solution: create login in SQL Server for client machine


          “Login failed for user ‘machinename\User’“


          Reason: client is local user and has no access


          Solution: create login in SQL Server for ‘machinename\User’


          “Login failed for user ‘user’“ (no “\”)


          Reason: SQL Login cannot connect


          Solution: Enable mixed mode


          Note that ‘user’ can be ‘null’ if client windows machine is not trusted, i.e. even SQL authentication requires cross-NTbox windows authentication


          SQL authentication can often be used as universal or the last resort solution to all these problems


 


Error: 18456, Severity: 14, State: XX.


          Client is recognized, but not authenticated


          State: XX is not reported back to the client


          Written into ERRORLOG


 






























































State


Reason


1


Generic error


2


Login for remote login doesn’t exist in the server


3,4


Failed to decrypt encrypted in-memory password


5


SQL login doesn’t exist in the server


6


Connecting SQL login has mismatched login type catalog


7


Login disabled


8


Password does not match


9


Password is invalid (when changing password at logon)


10


Account policy validation failed


11


Windows login is not authorized to connect


12


Sql login is not authorized to connect


13


Server has paused, no logins are allowed


14


Login cannot be used with this type of connection interface


15


Database specified in the connection string is invalid


16


Default database is invalid


17


Default language is invalid


18


Password cannot be changed for this type of login


19


Failed to decrypt encrypted in-memory new password


 


see also blogs.msdn.com/sql_protocols/archive/2006/12/02/understanding-kerberos-and-ntlm-authentication-in-sql-server-connections.aspx


 

Comments (3)

  1. Dave says:

    I had this issue with merge replication and did a search of the web to find the problem.

    To no avail.  

    The fix was to change the subscriber agent connection properties

    right click on replication – off context menu select – ‘configure publishers, subcribers…’

    on the subscribers tab, click on the ellipsis alonside the subriber with the problem – on the agent connect ion to the subscriber.  Change to SQL authenctication and add appropraite account details.

    Hope this helps

  2. anji says:

    ALTER LOGIN sa ENABLE ;

       GO

       ALTER LOGIN sa WITH PASSWORD = '<enterStrongPasswordHere>' ;

       GO