March 3, 2009 at 3:28 pm
Hi,
I'm trying to set up Windows Authentication between two machines running Windows XP Pro and SQL Server Express 2005 SP3.
Login fails when I try to connect using SSMS. ERRORLOG:
2009-03-03 22:04:42.57 Logon Error: 17806, Severity: 20, State: 2.
2009-03-03 22:04:42.57 Logon SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: 192.168.1.12]
2009-03-03 22:04:42.57 Logon Error: 18452, Severity: 14, State: 1.
2009-03-03 22:04:42.57 Logon Login failed for user ''. The user is not associated with a trusted SQL Server connection. [CLIENT: 192.168.1.12]
Here is my configuration:
Computers are on the same LAN, static IP and no gateway.
Network is running, ping works nice and Windows firewall is OFF.
Computer names are "Client1" and "Client2" with Workgroup "MSHOME".
SQL Server is running on Client2 with service account "LocalSystem".
Mixed auth mode and remote connections using TCP/IP are enabled.
A matching Windows user named "Test" exists on both machines with same password.
A mapped SQL Login for "Test" with sysadmin role exists.
Testing:
I'm running SSMS logged in as Windows user "Test" on Client1.
Login fails (above message) when I try to connect with the following input:
Server Name: Client2
Authentication: Windows Authentication
User name: Client1\Test (auto obtained)
However, SQL Authentication with user "sa" works.
Windows Authentication on localhost also works.
Please help me,
Thank you!
March 3, 2009 at 6:57 pm
Omg, I just solved the case after 10 hours of googling!
Client and server are not joined to a domain/AD (seems overkill for just two machines).
Hence they were unable to use Kerberos to establish a trusted connection.
However, it turned out that NTLM fallback failed, which caused the SSPI error.
(Useful details on Kerberos, NTLM and SQL here)
But why was NTLM failing? The answer is buried inside Windows security policy:
http://technet.microsoft.com/en-us/library/cc786449.aspx
Network access: Sharing and security model for local accounts
This security setting determines how network logons using local accounts are authenticated. If this setting is set to Classic, network logons that use local account credentials authenticate by using those credentials. The Classic model allows fine control over access to resources. By using the Classic model, you can grant different types of access to different users for the same resource.
If this setting is set to Guest only, network logons that use local accounts are automatically mapped to the Guest account. By using the Guest model, you can have all users treated equally. All users authenticate as Guest, and they all receive the same level of access to a given resource, which can be either Read-only or Modify.
...
Default: Guest only on Windows XP Professional. Classic on the Windows Server 2003 family and Windows XP Professional computers joined to a domain.
This security policy caused any Windows Authentication on the server to be mapped to the Guest account.
Furthermore, the Guest account was disabled on the machine.
This caused SSPI to return user "null" (none at all).
I ran gpedit.msc and changed the security setting to Classic mode:
Computer Configuration\Windows Settings\Security Settings\Local Policies\Security Options
After that, Windows Authentication started working!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply