Recent Updates
Sphere of Care
Syndication
Reading/Listening
  • The Host: A Novel
    The Host: A Novel
    by Stephenie Meyer
  • Another Day on Earth
    Another Day on Earth
    by Brian Eno
  • Oracular Spectacular
    Oracular Spectacular
    by MGMT
Archives
Xbox360
Xbox360 Avatar
« The Apple iPhone Legitimized the Blackberry | Main | The Choose Function »
Wednesday
28Mar

Error: 18456, Severity: 14, State: 16

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\user

Description:  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)

AH HA!


Gnat.
.
April 4, 2007 | Unregistered CommenterGnat
I am having this same issue, but what I do not know is, if I run aspnet_regsql and remove ASP.NET application from the database will that destory the abilitiy to use the Microsoft SharePoint databases that are in use?
July 27, 2007 | Unregistered CommenterJustin Loving
Thanx CorkChop!!

That solved my case. :)
April 2, 2009 | Unregistered CommenterStandal
Hello - I can not seem to identify which database this is happening to....
how can I find that out?

Thanks
April 16, 2009 | Unregistered CommenterNathan Cartwright
Nathan,

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.
April 17, 2009 | Unregistered CommenterDavid Claxton

PostPost a New Comment

Enter your information below to add a new comment.

My response is on my own website »
Author Email (optional):
Author URL (optional):
Post:
 
All HTML will be escaped. Hyperlinks will be created for URLs automatically.