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

Oct 25, 2011 / By Mohammed Mawla

Tags: , ,

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

errorlog

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_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL10_50.instance_nameMSSQLServerParameters

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 FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGERRORLOG

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:WINDOWSsystem32driversetchosts

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

4 Responses to “SQL server:opening errorlog:Failed to retrieve data for this request”

  • Kamlesh says:

    Hi Mohammed Mawla,

    Thanks for this article!
    I have a standalone SQL server 2008 for testing/studying purpose and facing the same problem. I tried the above solution provided by you but it didn’t help.
    Would really appreciate if you could help out. It’s really irritating me.

    Thanks,
    Kamlesh

  • Mohammed Mawla says:

    Kamlesh,

    Not sure about your exact situation but I assume you have the latest Service pack installed ?

  • Gerardo says:

    Were you able to solve this issue?

  • Paolo says:

    For me the SQL Server Agent does not start
    In the event log find:
    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: ].

    Thank to this illuminating post we find in the file host an additional extra lines (putted by someone for unknown reason)

    –before
    127.0.0.1 localhost
    ::1 localhost
    10.27.32.200 mysql.domain.local

    –after
    127.0.0.1 localhost
    ::1 localhost
    ##10.27.32.200 mysql.domain.local

    Now it’s working very fine
    Thank againg

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>