Monday, October 5, 2009

Login Issue

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.

No comments:

Post a Comment