Quick Tip: Error: 18456, Severity: 14, State: 38.

When trying to investigate the SQL error, “Error: 18456, Severity: 14, State: 38.” it can be frustrating when not a single log on the system will tell you what the actual problem is. In the case of state 38 (16 or 27 prior to SQL 2008) this error means the database specified in the client connection does not exist, or is offline. In a large shop with many various clients it can be hard to find out the source of the connection, so you’re option is to use the tools at your disposal.

The SQL Error Log does not help much at all. Here’s what’s in the SQL Error log:
2011-05-06 09:06:17.28 Logon Error: 18456, Severity: 14, State: 38.
2011-05-06 09:06:17.28 Logon Login failed for user ‘DOMAIN\ssrs.service’. Reason: Failed to open the explicitly specified database. [CLIENT: 192.168.0.147]

The Windows Application log is not much help either.
Login failed for user ‘DOMAIN\ssrs.service’. Reason: Failed to open the explicitly specified database. [CLIENT: 192.168.0.147]

In other words, useless for troubleshooting. The way to obtain the database name the client is trying to connect to is to use SQL Server Profiler.

To set up SQL Server Profiler, connect to the SQL instance where the error is occuring and then track the following events:
Errors and Warnings: User Error Message
Security Audit: Audit Login Failed

For these two events, make sure you capture at least the following columns: ApplicationName, HostName, LoginName, SPID (required), StartTime, TextData (where the message text will be), Severity, State, ClientProcessID, and Error.

SQL Profiler Setup Screenshot

Then run the trace. You’ll see the following data under the “User Error Message” event when the login failure occurs:
Cannot open database “DatabaseName” requested by the login. The login failed.
For the “Audit Login Failed” event you’ll see the following data:
Login failed for user ‘BLUENE\ssrs.service’. Reason: Failed to open the explicitly specified database. [CLIENT: 192.168.0.147]

Using this information (and the rest of the info in the other columns you selected), you should be able to go to the source and identify which process is trying to connect to the specified database and take appropriate action.

If you’re interested in the other states that error 18456 can generate, please visit fellow MVP Aaron Bertrand’s page on this topic for a very nice listing.

Attached is the SQL Profiler Trace Template for SQL 2008 instances if you want to import it instead of manually configuring SQL Profiler. To import the template, in SQL Server Profiler, go to File->Templates and select Import…

SQL_Login_Failures_Profiler_Template.tdf (860 bytes)

17 thoughts on “Quick Tip: Error: 18456, Severity: 14, State: 38.

  • salsish

    Thanx man, this one made my day!

  • Chris Savage

    Thanks for this. It is EXACTLY what I needed!

  • AleckIV

    Thank you master.
    You realy save me a lot of time.

  • Edward Beckett

    One of the most useful posts I’ve seen on the topic …

    Thanks a million … My case was the a connection attempt to the Report Server which I would have never found without your help ….

    Thanks again …

    🙂

  • Anoop

    Thanks for the tip. Helped save a lot of time.

  • Anne Mette Evert

    I agree!
    Nice input!

  • Vidya

    Thank you soo… much..
    This resolved my issue in just 2 minutes…

  • anji

    ALTER LOGIN sa ENABLE ;
    GO
    ALTER LOGIN sa WITH PASSWORD = ” ;
    GO

  • Carrowkeale

    Nice post, however where can you find the database name if the database has been removed. The trace defaults to reporting the database as Master which is incorrect.

    • Phil Brammer

      I assume this is on SQL 2008? If so, see VSTS bug 295750 listed towards the bottom of the KB article for service pack 3:
      http://support.microsoft.com/kb/2546951

      If you’re on 2008, ensuring that SP3 is installed should take care of the problem.

  • Carrowkeale

    Thanks Phil

    Applying SP3 worked 🙂

  • Bill Smith

    I have just got this error on my site which contains system which I regret to admit I have no backup. I used the SQL repair utility :

    ALTER DATABASE feedostylepro SET SINGLE_USER;
    GO
    DBCC CHECKDB feedostylepro REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
    GO

    The database had errored and was marked as suspect – sql server couldn’t bring it online so I repaired it as above.

  • S. Kusen

    Thank you very much for this article. we see these from time-to-time when we move databases from one server to another or decommission databases, and I hadn’t really dug into trying to find out what DB’s were attempted to be connected to from the DB side. Usually we just asked the application owners to check the client IP’s for the necessary information.

    I saved this off as a profiler template. I really appreciated the effort for this tip.

    Cheers,
    Steve

  • Tim

    Regarding your comment on March 27, 2012. I am also running a trace and the DatabaseName is always coming back as master. I am currently running version 10.50.1734.0 which I think has service pack 3 installed. I’m not sure how to verify though.
    When I went to install SP3, it said that there are no instances of SQL Server to apply the update to. Any advice on how to proceed?

  • Jim

    I’m also getting login failures pointing to my master db. The requests are coming from the CRM Async process and the IIS process. I have given both the machine account and the user account dbowner access to the master db and still getting errors. So frustrating, and I believe these failed requests are causing my SQL to crash sporadically.

  • Nathan

    Thanks, this saved me allot of time

  • Iain Elder

    Before I read your article, I was being mislead by the output of the “Audit Login Failed” class. In that event, The DatbaseName column contains “master”.

    Thanks for documenting the “User Error Message” class as the solution!

Comments are closed.