Login Failure with Pass-Through Authentication
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. ------------------------------ ADDITIONAL INFORMATION: 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 –
E:\Tools\Err>Err.EXE 0x8009030c # for hex 0x8009030c / decimal -2146893044 : SEC_E_LOGON_DENIED winerror.h # 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 Source: Microsoft-Windows-Security-Auditing Event ID: 4625 Task Category: Logon Level: Information Keywords: Audit Failure User: N/A Computer: Win2K12Node1.icicibank.com Description: An account failed to log on. Subject: 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 Information: Failure Reason: Unknown user name or bad password. Status: 0xC000006D 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.
Learnings
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.
Comments
Anonymous
August 31, 2015
Nice Dev, beauty is not the issue , but its the way you have explained troubleshootingAnonymous
August 31, 2015
Thanks Akash!