Error: 18456, Severity: 14, State: 16
Wednesday, March 28, 2007 at 06:08PM Microsoft SQL Server 2005 has been reporting an error for about three weeks. I would have never noticed it but this afternoon Reporting Services failed and brought our production system to a halt. Of course now I realize that this was not the source of the problem but if you are like me, any reported failures in the event log should be resolved, at least, explained.
The Application Event Log reported this error:
Source: MSSQLSERVER
Category: (4)
Event ID: 18456
Type: Failure Audit
User: domain\userDescription: Login failed for user 'domain\user'. [CLIENT: <local machine>]
0000: 18 48 00 00 0e 00 00 00 .H......
0008: 07 00 00 00 42 00 4c 00 ....B.L.
0010: 41 00 44 00 45 00 32 00 A.D.E.2.
0018: 00 00 07 00 00 00 6d 00 ......m.
0020: 61 00 73 00 74 00 65 00 a.s.t.e.
0028: 72 00 00 00 r...
The data portion of the event properties reveals that the user account was attempting to use the master database. Using the Log File Viewer for SQL Server, I noticed the following entry:
Date 3/28/2007 5:28:00 PM
Log SQL Server (Current - 3/28/2007 6:01:00 PM)Source Logon
Message
Error: 18456, Severity: 14, State: 16.
I was stumped because 'domain\user', which is a domain service account has the sysadmin role for SQL Server. I searched on Google and could not find a solution. By accident, I clicked on the SQL Agent Log and found two entries:
[298] SQLServer Error: 18456, Login failed for user 'domain\user'. [SQLSTATE 28000]
[298] SQLServer Error: 4060, Cannot open database "ASPState" requested by the login. The login failed. [SQLSTATE 42000]
Then it occurred to me that when I recently moved my ASP.net out-of-process state server from this server to a high-availability cluster server, I took the database offline to make sure that all references to it had been changed. Apparently SQL Agent is responsible for ASPState cleanup and the entries to perform the clean up reside in the master database. The Agent could not gain access to the database because it was offline so it reported the error.
For those of you that have installed an ASP.net application or state server in SQL Server, you will have to use the aspnet_regsql tool to remove it. Manually deleting the databases or taking them offline will produce these errors.
Reader Comments (5)
Gnat.
.
That solved my case. :)
how can I find that out?
Thanks
Look in the SQL Agent LOG - it should should which database it is trying to gain access to.
I figured out I had to login errors to test DB's I had created and deleted. I created two DB's with the same names and the errors went away.
I am trying the aspnet_sql tool now.