Recently I received below error while executing XP_ReadErrorlog command from SSMS.
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 error log location not found
As per the above error message I have checked event viewer log and got the below information from event viewer log.
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:18456, OS: 18456 [Microsoft][SQL Native Client][SQL Server]Login failed for user ‘domain\service-sql’..
I have opened error log file from log directory and observed the number of login service-sql with the state 11.
2009-09-30 22:45:31.69 Logon Login failed for user ‘domain\service-sql’. [CLIENT: 10.10.10.10]
2009-09-30 22:45:34.26 Logon Error: 18456, Severity: 14, State: 11.
Service-SQL account has sysAdmin rights on SQL Server and it is added through SQL Server Service – Production AD group. I can see this AD group has SysAdmin rights on SQL Server.
Resolution: Remove account from local administrator group and from SQL Server Logins. Add account again with the same privileges in local administrator group and SQL Server logins.