After we finished install SQL Server for clustering (install was excellent nothing failed, all greens, logs good); SQL Server refuses to start up. We keep getting following types of error messages in our ERRORLOG file; this was same for RTM and SP2:
2009-05-09 12:53:16.94 Logon Error: 18456, Severity: 14, State: 11.
2009-05-09 12:53:16.94 Logon Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’. [CLIENT: xxx.xxx.xxx.xxx]
2009-05-09 12:53:39.53 spid7s SQL Server is terminating in response to a ‘stop’ request from Service Control Manager. This is an informational message only. No user action is required.
I could not figure out the issue with this; so while talking to Microsoft I found two interesting facts:
- I was able connect to SQL Server using \\.\pipe\SQLLocal\NamedInstanceName\ (so using Named Pipes) using Windows Authentication and SQL Server authentication.
- I was able to log into ServerName\NamedInstanceName using SQL Server authentication only.
So Microsoft engineer thought it was an issue with NTLM so he got me to create a new value in the following registry location:
Key: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Lsa\
Value Name: DisableLoopbackCheck
Value Type: DWORD
Value: 1
Microsoft Engineer referenced KB887993 as to this fix; this resolved the authentication issue with SQL Server.
But today I had issues with other servers where users not being able to authenticate, in a recent Windows Update, KB957097 (addressing MS08-068 Security Bulletin, link). After reading up KB957097, I found out disabling that setting actually is not advised way to fix this issue; I sent an email to Microsoft Engineer asking his opinion on this …
Ref Links:
SQL Server Protocols: Using Kerberos with SQL Server, Link.
SQL Canada (SQLCAN) 