Although SQL Server login failure issues are pretty common I did not find this one documented anywhere, hence this blog post.
Recently I stumbled upon a Login failure error while connecting to an SQL Instance remotely. The authentication scheme used in this case was Pass-through Authentication.
For those who are not aware of Pass-through authentication, it’s basically a technique in which a local windows user account is created on the source and destination server with the same password. If the user account has the same username and password, Windows authenticates this user and lets him access the resources. This scheme is generally used where there is prohibition of domain account.
So here’s the error which I got while connecting via SSMS –
TITLE: Connect to Server
Cannot connect to WIN2K12NODE1.
Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. (Microsoft SQL Server, Error: 18452)
Looking at the errorlog revealed the below errors –
2015-07-15 21:26:50.06 Logon Error: 17806, Severity: 20, State: 14.
2015-07-15 21:26:50.06 Logon SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. The logon attempt failed [CLIENT: 10.10.10.2]
2015-07-15 21:26:50.08 Logon Error: 18452, Severity: 14, State: 1.
2015-07-15 21:26:50.08 Logon Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: 10.10.10.2]
SSPI Handshake Failed errors happens usually when the user is not authenticated. Let’s see what the specific error code means. For this I generally use the command line utility Err.exe which is one of the must haves for an administrator.
Here is what I got –
# for hex 0x8009030c / decimal -2146893044 :
# The logon attempt failed
# 1 matches found for "0x8009030c"
In this case the output was not very helpful.
Next I looked at the security event logs and found this Audit failure–
Log Name: Security
Event ID: 4625
Task Category: Logon
Keywords: Audit Failure
An account failed to log on.
Security ID: NULL SID
Account Name: -
Account Domain: -
Logon ID: 0x0
Logon Type: 3
Account For Which Logon Failed:
Security ID: NULL SID
Account Name: TestUser
Account Domain: WIN2K12NODE2
Failure Reason: Unknown user name or bad password.
Sub Status: 0xC000006A
In this case, it could not be unknown Username since the account existed in SQL Server. But yeah, it could be a Bad password. And that it was, resetting the password on the machine from where I was connecting to SQL Server did the trick for me.
Password mismatch for the local windows account between the servers. For pass-through authentication to work it’s important that we have the same username and password.
Even though there is a mismatch in the password we do not get 18456 (Login failed due to password mismatch) because the authentication scheme is slightly different in this case. The username and passwords are itself used to match the login in pass-through authentication.
Disclaimer: The information in this weblog is provided "AS IS" with no warranties, and confers no rights. This weblog does not represent the thoughts, intentions, plans or strategies of my employer. It is solely my opinion. Inappropriate comments will be deleted at the author’s discretion. All code samples are provided "AS IS" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular.