Login Failed for user The user associated with a trusted SQL Server connection ( Microsoft SQL Server Errors : 18452)

 

Issues: SP servers are recovering fallowing error “Login Failed for user The user associated with a trusted SQL Server connection ( Microsoft SQL Server Errors : 18452)”

Postmortem:

The SPN record in DNS for the Share point service account had issues, and for some reason the SP server could not get the SPN record for authentication thus below errors were generated.

 

Possible Solution: there might be multiple different scenarios why above generic event occurs the tools used in this example ( list is below) important to capture any authentication issues maybe causing problem.

Find out which DC is not honoring the mentioned SPN record. Run SSPIClient.exe and check the output see below

You may have to fire up ADSIedit and check the SPN entry for the SP service account , if you do remove the SPN entrees the authentications should fall back to NTLM and things should work fine. If so add the SPN value back and check to make sure the Domain controller SP is complaining about does have valid Kerberos and CName record, under DNS _msdtc . Remember these records are dynamically created records when DC reboots it registers these records into DNS database, so that DC can offer such services to its clients. Check TPC/IP configuration on the DC to make sure which DNS server the DC is configured to talk too. If DC is configured to point itself and running AD integrated DNS, try to point it to another DC on different  AD Site and FlushDNS and register via fallowing commands…….

• IPconfig /FlushDNS
• IPConfig /RegisterDNS
• Also re-start Netlogon service on the domain controller and try to authenticate once more.

Tools used:
SSPIClient.exe:  Security Support Provider Interface.  Tracks the authentication process and generates a log.

Kerblist.exe: CLI tool.  Reset the Kerberos cache (purge) and list the cached Kerberos (tickets).   Reboot of any server effectively execute the purge on all servers.

Adsiedit.msc: Standard AD tool.  Used to edit the SPN record of svc-intra.sql account.

Eventlogs Windows standard tool for log analysis.
Network Monitor or NetMon: Capturing network packets on Windows platform (not used).

Respectfully,
Oz Casey, Dedeal

MVP Exchange Server
MCITP (EMA), MCITP (SA)
MCSE 2003, M+, S+, MCDST
Security+, Project +, Server +
http://smtp25.blogspot.com/ (Blog)
http://telnet25.wordpress.com/(Blog)