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.
please post more scenarios on security ,High availability ,backups, and other concept also