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
6 Comments. Leave new
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
Kamlesh,
Not sure about your exact situation but I assume you have the latest Service pack installed ?
Were you able to solve this issue?
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
Hi Mawla,
Thanks for providing a solution to this annoying issue. In my case, i need to read the error logs of hundreds of servers and place it a network folder. I cannot able to read error log in these servers as mentioned earlier. Is there any other way to read the errorlog from these stores? Also i’m checking other options like copying errorlog file into the network folder, but it may be difficult for me to filter out the errors from the errorlog file. Hope you understand my situation.
Thanks,
Sarat
I was facing same error and your solution worked like a charm for me. Thanks a lot sir.
FYI, in my environment, the change that happened was with IP addresses; they had been changed for Cluster and all Nodes. Cluster was working fine after change but I was not able to read error logs. After updating the host file, it is now working fine. Also, in my case, the localhost IP was already mentioned in host file which you highlighted. Instead of that, the Old-SQL-Cluster-IP was mentioned. As soon as I updated the old-SQL-Cluster-IP with New-SQL-Cluster-IP; then it started working fine. Thanks again.