SQL server:opening errorlog:Failed to retrieve data for this request

After an in place upgrade of SQL server 2005 to SQL server 2008 R2 , I tried to open SQL errorlog from Management> SQL server logs but I got the following error

Refreshing the node will pop the error up again.

Maybe we try T-SQL!

EXEC xp_readerrorlog


Msg 22004, Level 16, State 1, Line 0
Failed to open loopback connection. Please see event log for more information.
Msg 22004, Level 16, State 1, Line 0
error log location not found


As far as I know , SSMS calls a system stored procedure xp_enumerrorlogs to return the list of error logs and associated last change date.

The location of the SQL server errorlog ,and master database files, is stored in registry under following key

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.instance_name\MSSQLServer\Parameters

Instance_name is SQL server instance name currently being accessed ; it was default in my case so it was MSSQLSERVER

This is the value of SQL sever errorlog entry

-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG

I then verified that the folder exists and SQL server service account has enough permissions to read/write the file.

Looked at Event viewer , Found following three events

Event Type: Failure Audit
Event Source: MSSQLSERVER
Description:
Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: local machine]

Event Type: Error
Event Source: MSSQLSERVER
Description:
The description for Event ID ( 17052 ) in Source ( MSSQLSERVER ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: Severity: 16 Error:18452, OS: 18452 [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed. The login is from an untrusted domain and cannot be used with Windows authentication..

Event Type: Error
Event Source: MSSQLSERVER
Description:
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. [CLIENT: local machine].

What’s causing these login failures? the connection is certainly local so “untrusted domain” error looks misleading.

Xp_readerrorlog calls a DLL ,xpstar.dll, but there’s not much info how xpstar.dll works. However,the original error carried some guidance represented in “Failed to open loopback connection” part.

To me, the word loopback used to mean local communication on same server when addressing IP address 127.0.0.1 or Localhost computer name (I see it has more uses now).

ALL I did was to open Windows® hosts file C:\WINDOWS\system32\drivers\etc\hosts

and that was it , I didn’t find a map for LOCALHOST nor servername; all I did was to add that map

127.0.0.1 localhost server_name

After that , I could open the errorlog from SSMS and Xp_readerrorlog came back with results.

Hope this helps someone , It can be really annoying.

Cheers,
M