SQL Server Error 18456: Finding the Missing Databases

By:   |   Comments (31)   |   Related: > Error Logs


Problem

SQL Server Error Logs often show a message related to error 18456. Although it generally means a login attempt from a client connection has failed, different State numbers associated with the error can mean different reasons for the failure. One of the error States is 38, which was added with SQL Server 2008, means the database being accessed cannot be found or does not exist. Unfortunately the Error Log entry does not show what database it is, so in this tip we walk through how you can determine which database is causing the error message. 

Solution

A few days ago I was looking through the Error Log of a database server and noticed a large number of the same error messages that looked like the following:

2011-12-15 11:22:08.76 Logon      Error: 18456, Severity: 14, State: 38.
2011-12-15 11:22:08.76 Logon      Login failed for user '<domain_name>\<account_name>'. Reason: Failed to open the explicitly specified database. [CLIENT: XXX.XX.XX.XXX]

Error 18456 generally means a failed login attempt. Like other error messages, it also has a State number and there are different State numbers associated with it, each having a different meaning for the failure. As I found out later, States 38 and 40 (in SQL 2008 and later) and States 16 and 27 (before version 2008) mean that there is an issue with the database being connected to. The database could be offline, shutdown, deleted, dropped, renamed, auto-closed, or inaccessible for some other reason. It could also be the default database for the login where explicit permission was not given.

The server was running an instance of SQL Server 2008 and although it was a test instance, I decided to spend some time to find out what database was being accessed by this account. The reason I wanted to dig deeper was because the error was happening many times, almost every ten seconds, and in my view it had these potential problems:

  • If this was a production server, SQL would be spending I/O time writing the same message to the Error Log. In other words, disk access would be almost continuous.
  • The potentially large Error Log would take longer and longer to load. Even with this instance where the Error Log was cycled every night, it was still taking time to load thousands of rows of log entries. Things would be even worse for instances where the log was not cycled regularly.
  • With the same error message repeated over and over, it would be difficult to sift through the log and a DBA could miss other errors or warnings.
  • And of course there is the question of security - you should want to know why database access is being refused. 

From the frequency of the messages, I knew the request was probably coming from an application or web server, so I ran the following command against the IP addresses to confirm this.  nslookup is a command that can take an IP address as a parameter and tell you the name of the machine: it's like the reverse of the ping command.

nslookup command

I looked at the SQL Server logins and saw that the account in question was a member of the sysadmin role, meaning it had unrestricted access to all the databases in the server. In other words, it was trying to access a database that was not in the system anymore and I had to find that missing database. The problem would be simple if there were a few databases and if I knew the application well enough to identify each of them. This was a test environment reflecting a production system and there were 104 databases hosted by the instance. The Windows Application or Security Event Log did not yield much information either except showing the same messages.

So I decided to start Profiler and put a trace on the server. I started with the default trace template (since I was going to modify the events anyway) and ensured the trace data was being saved somewhere:

Setting up Profiler Trace
 

Next came the question of choosing events. Since the problem was not associated with a query, I could get rid of the default TSQL and Stored Procedures events listed. Instead, I chose two different categories of events:

  • Security Audit
  • Errors and Warnings

From the Security Audit category (which is automatically listed in the default trace), I only kept the "Audit Login Failed" event. It may be tempting to keep the "Audit Login" event as well, but if you think about it this would cause the trace to grow really big very quickly. Each login attempt would be recorded and the trace would have unnecessary entries that you would not need.

From the Errors and Warnings category, only two events were chosen. First, the "ErrorLog" event - this would trap every instance the Error Log is accessed. The second and the most important event was the "User Error Message". This is the event that would capture the error returned to the client (in this case the web server the connection request was being made from) and the message would contain the name of the database being accessed. This would also mean other messages sent to the client would also be captured, such as "database context changed to..." etc.

For the columns, only five were kept: TextData, ApplicationName, NTUserName, LoginName and SPID. Again, you may think the DatabaseID or DatabaseName columns would yield the required information, but in reality it only shows the context of the database from which the connection was requested. In this case, it was the account's default database: master. HostName was also not required because I knew the name of the requesting server already.

Selecting Trace Events

Selecting Trace Events

Finally, all that remained was to define a filter. I chose to filter the NTUserName field because I was able to determine the account name from the Error Log.  (Make sure you replace the value with the account you are trying to trace.)

Defining Filter for the Trace

Within a few seconds of starting the trace, I had something like the following:

Data Captured by Profiler Trace

As you can see from the repeated entries, a user error message is sent to the client, then an Error Log entry is made and then the login failure audit is captured. Clicking on a User Error Message entry right before the Error Log entry gave me the name of the database that was causing the error.

The message was something like the following:

"Cannot open database <database name> requested by the login. The login failed."

As I queried the sys.databases table, obviously there was no entry for that name, which proved my initial premise that the database had been dropped.

One thing you need to be aware of, particularly where large number of databases are associated, is that the account could be failing to connect to not one, but multiple databases. In my case as I found out, a number of databases had been dropped by the developers and the login attempt against each database was failing.

To cater for this, it's best to run Profiler for a reasonable amount of time so all database access attempts are captured. After you have stopped the trace, you can open the file and filter the TextData field for the error message, as shown below:

Defining Filter on the Saved Trace File

This allowed me to find seven databases that were missing. The next step was to contact the relevant application team and notify them of the missing databases.

Next Steps
  • Learn more about SQL Server Profiler and how to set up trace
  • Learn about SQL Server Error Log and how you can access the file in a text editor, outside of SSMS
  • There is an excellent article on Error 18456, written by Aaron Bertrand. You can read it here.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Sadequl Hussain Sadequl Hussain has been working with SQL Server since version 6.5 and his life as a DBA has seen him managing mission critical systems.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Friday, July 6, 2018 - 2:54:22 PM - George Back To Top (76555)

Another option would be to find the "cannot open database" message in the sys.messages system table and alter it to log when it occurs.  This way you don't have to turn on a profile trace to capture the error.

In my case the message Id was 4060 so I executed the following: 

EXEC sp_altermessage
 @message_id = 4060
,@parameter = 'WITH_LOG'
,@parameter_value = 'true'

The next occurance showed up in the SQL Server error log. 

 


Thursday, May 10, 2018 - 5:43:25 PM - Sadequl Hussain Back To Top (75916)

Hi Megna,

 

I believe this is happening because your app is trying to connect to the secondary database in the mirroring setup, instead of the primary one. When it tries to do so, it cannot access the database because the DB is unavailable when acting as a secondary. If possible, you can do a failover and check if the problem persists.


Thursday, May 10, 2018 - 4:15:11 PM - Megna Back To Top (75915)

I am getting same Error “Login failed for user ‘XXX’. Reason: Failed to open the explicitly specified database ‘XXX’. [CLIENT: XXX.XXX.XXX.XXX]”,
Error: 18456, Severity: 14, State: 38

Database is Configured as Mirroring in (Restoring State), Login is SysAdmin on the Server but still i am getting this Error tried to find why this Error is occuring but could not find anything, if you can help that would be great.


Monday, March 6, 2017 - 11:25:16 AM - Craig Silvis Back To Top (47395)

Thanks so much for the tip, I have an application that is attempting to connect to a database every 15 minutes and this will help figure out what database it is trying to get to. 

 


Friday, December 9, 2016 - 1:15:33 PM - Sam Back To Top (44934)

 Thanks for the great article. In my case it was due to deleting reporting services database, but not stopping the service. Thus the service was pinging the database server to login as the account, but the database was dropped! I was wandering what would be the issue, I initally granted sysadmin access to the account, but that did not solve it. After some search, I found your great article, and that solved my problem. Thanks. 

 


Monday, October 31, 2016 - 4:58:37 PM - Lee Back To Top (43663)

 Excellent article. Do you know if this can be done with Extended Events? Thanksa again.

 


Friday, July 29, 2016 - 4:16:02 AM - Ning Back To Top (43001)

Thank you so much for this guide, it helped me so much!


Wednesday, June 22, 2016 - 12:26:23 PM - Amy Morgan Back To Top (41739)

 This was exactly the issue we were having and this tip solved the problem. Your insturctions were precise and very thorough. Thank you so much for sharing your knowledge with the rest of us. You are appreciated!

 


Monday, August 10, 2015 - 10:39:00 AM - Sourav M Back To Top (38412)

Nice Article.

 

In my environment, I found that one of the login accounts had sysadmin permission. So natually it grabs admin rights to all databases. However, it still used to throw the error.

 

I realized the issue and granted DB_admin rights to the designated databases it generally uses and the issue is resolved.

 

I wanted to know if anyone can suggest what made wrong that inspite of having syadmin rights, still it used to throw the error and upon giving a DBO permission it worked.

 

Any advise plz?

 

Login failed for user ''. Reason: Failed to open the explicitly specified database. 


Thursday, May 7, 2015 - 4:06:01 PM - Anup Shah Back To Top (37128)

Hi Hussain,

Thanks for the great article. I found the same issue in my SharePoint DB Production Serevr.I provided Db owner access to Sharepoint Application Service account to perform deployment. After the 1 week of deployment, permission got revoked somehow and received bunch of same errors in SQl logs. The db was available on Db server and from the Application side it was also available. How can I diffrentiate and find root cause that why access got revoked?  can you please provide your inputs.?

Thanks In advance.

-Anup


Saturday, March 28, 2015 - 1:39:23 PM - Annett Back To Top (36739)

This was very helpful.  I am trying to copy a database instance using the Microsoft SQL 2014 Copy Database Wizard and it fails because it is trying to move a database which was dropped over a year ago.  Do you have any way of finding what in the database instance is still pointing to the dropped database?  I tried recreating the database with the same name and it still failed.

 

I am stumped.


Tuesday, October 21, 2014 - 11:03:39 AM - Pavel Chowdhury Back To Top (35028)

This is helpful.. Thank you..


Monday, October 20, 2014 - 8:23:25 AM - Varinder Sandhu Back To Top (35018)

Really useful information. thanks for sharing.

 

Varinder Sandhu
www.VarinderSandhu.in

 


Wednesday, September 24, 2014 - 6:21:33 PM - Steve Armistead Back To Top (34706)

Thank you for sharing, it is appreciated.


Sunday, July 20, 2014 - 11:52:45 AM - Sagnik Back To Top (32795)

My database is in the same server. Hence, it is local. if my local database is missing, then how to re-get it? Any pointers will help. Very new to Windows. 


Thursday, May 8, 2014 - 2:36:39 AM - Manus Cornelius Back To Top (30662)

Thank you very much. This was extremely helpful.


Monday, February 24, 2014 - 2:29:52 PM - Shari Back To Top (29555)

Thank you so much for the article. It helped a lot to debug my problem!

 


Tuesday, December 10, 2013 - 10:08:26 AM - tobefruit Back To Top (27750)

Crystal Clear! Helped a lot ! Thanks so much!!

:-D


Thursday, September 12, 2013 - 10:33:34 AM - Neal Back To Top (26768)

Thanks Sadequl.  Makes sense.  I'll work this issue from the app side.


Thursday, September 12, 2013 - 3:29:17 AM - Sadequl Hussain Back To Top (26756)

Hi CC, Neal,

Thanks for the feedback. I believe the connection information for the database should really be an application specific thing.

For Reporting Services, I would probably be checking the RS configuration tool and ensure it is pointing to the right database (newly created ones). 

Also, From the SQL Server error log entries I would want to ensure the client IP address is definitely corresponding to the RS system.

Similarly for other apps, I would want to check the IP address first and find the corresponding server. If I can find the server (presumably an app server), I would want to check weherever any connection information could be stored. This can be in an ODBC connection or stored in any app-specific config file etc.

Another thing I would probably do is to query the sys.databases table and see if my non-existent database is still listed there for some strange reason. If so, I would probably try to do a DROP DATABASE against it and restart the SQL Service.

Hope this helps.


Tuesday, September 10, 2013 - 2:34:47 PM - Neal Back To Top (26714)

Great article but I agree with CC, the last comment posted.  I want to eliminate the failed login error messages.  How do we remove the connection information to the deleted\removed databases?


Thursday, July 18, 2013 - 9:40:12 AM - CC Back To Top (25894)

This article helped me find the missing databases, but what I don't understand is how do I remove the connection to those databases that no longer exist?  They were reporting services databases setup with SSRS to work with SharePoint.  The server has been rebuilt and new databases with new names created.  i'm not sure where the connection is stored and how to remove it from those old databases.


Thursday, June 6, 2013 - 2:16:46 PM - Chris Bankovic Back To Top (25325)

Really great article!  Huge thanks!


Monday, May 20, 2013 - 6:02:20 PM - Brien Malone Back To Top (24049)

Thanks for posting this. I'm a developer with some basic SQL server admin knowledge. I went from never having heard of the profiler to being comfortable with the tool thanks to this tutorial.

 

The only thing I would add (for us newbies) is that the profiler is installed as part of the SQL server installation and is found under [start]-->[All Programs]-->[Microsoft SQL server 2008]-->[Performance Tools]-->[SQL Server Profiler]


Monday, May 6, 2013 - 1:29:39 PM - Russell Back To Top (23740)

This was very helpful to me too. Thank you for such a clear troubleshooting explaination.


Wednesday, April 3, 2013 - 4:04:28 AM - Daniel Back To Top (23129)

Thanks, this was really helpful.


Thursday, February 7, 2013 - 4:07:44 AM - Mushtaq Back To Top (21963)

Jazakallah, for sharing your experience :)

 


Friday, June 29, 2012 - 11:55:47 AM - Mark Back To Top (18268)

Is there any way to fetch the database name from any logs via T-SQL?  


Wednesday, March 14, 2012 - 1:52:55 AM - manu Back To Top (16369)

Thanks for sharing your experience. It definitely helps as I have seen application developerss leaving behind the databases even after the application is decommisioned. 


Thursday, January 12, 2012 - 4:55:01 PM - Sadequl Hussain Back To Top (15630)

Thanks for your input, Jason. Yes the DB sever in question was a SharePoint development server and a large number of developers had worked on it on different projects for quite some time. The DBs in question were associated with application that were no longer needed - in this case, the DBs were dropped by developers but I guess the application side of things were not properly wrapped up.


Thursday, January 12, 2012 - 11:24:32 AM - Jason Back To Top (15627)

I am glad you wrote up your experiences. I am surprised that your application team has not noticed the 7 applications do not work for some time.

This particular is also common when someone type (or copy paste) incorrect database name (with trailing control character) into connect-string.

Another common error is the login account no longer has a default database asociated with it. They get a different error. If database browser service is on, the user front-end can re-choose default database; if not, DBA has to reset that login's default database.















get free sql tips
agree to terms