Error 18456 Severity 14 State 38

Here we can see “Error 18456 Severity 14 State 38”

SQL Server Error 18456: Finding the Missing Databases

Problem

SQL Server Error Logs often show a message associated with error 18456. Although it generally means a login attempt from a client connection has failed, different State numbers related to the error can mean different reasons for the failure. one among the error States is 38, which was added with SQL Server 2008, which means the database being accessed can’t be found or doesn’t exist. Unfortunately, the Error Log entry doesn’t show what database it’s, so during this trip, we rehearse how you’ll determine which database is causing the error message.

Solution

A few days ago, I used to be rummaging through the Error Log of a database server and noticed an outsized number of equivalent error messages that seemed 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 features a State number, and there are different State numbers related to it, each having a special meaning for the failure. As I acknowledged later, States 38 and 40 (in SQL 2008 and later) and States 16 and 27 (before version 2008) mean a problem with the database being connected to. The database might be offline, shutdown, deleted, dropped, renamed, auto-closed, or inaccessible for a few other reasons. It could even be the default database for the login where explicit permission wasn’t given.

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

• If this were a production server, SQL would be spending I/O time writing an equivalent message to the Error Log. In other words, 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 nightly, it took time to load thousands of rows of log entries. Things would be even worse for instances where the log wasn’t cycled regularly.
• With an equivalent error message repeated repeatedly, it might be difficult to sift through the log, and a DBA could miss other errors or warnings.
• And in fact, there’s the question of security – you ought to want to understand 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 subsequent command against the IP addresses to verify this. nslookup may be a command that will take an IP address as a parameter and tell you the machine’s name: it’s just like the reverse of the ping command.

I checked out 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 or any of the databases within the server. In other words, it had been trying to access a database that wasn’t within the system anymore, and that I had to seek out that missing database. the matter would be simple if there have been a couple of databases and if I knew the appliance tolerably to spot each of them. This was a test environment reflecting a production system, and the instance has hosted 104 databases. The Windows Application or Security Event Log didn’t yield much information either except showing an equivalent message.

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

Next came the question of selecting events. Since the matter wasn’t related to a question, I could prevent the default TSQL and Stored Procedures events. Instead, I chose two different categories of events:

• Security Audit
• Errors and Warnings

I only kept the “Audit Login Failed” event from the safety Audit category (which is automatically listed within the default trace). I only kept the “Audit Login Failed” event. It’s going to be tempting to stay at the “Audit Login” event also, but if you think that about it, this can cause the trace to grow big very quickly. Each login attempt would be recorded, and therefore, the trace would have unnecessary entries that you wouldn’t need.

From the Errors and Warnings category, only two events were chosen. First, the “ErrorLog” event – this can trap every instance the Error Log is accessed. The second and, therefore, the most vital event was the “User Error Message“. this is often the event that might capture the error returned to the client (in this case, the online server the connection request was being made). Therefore the message would contain the name of the database being accessed. This can also mean other messages sent to the client would be captured, like “database context changed to…” etc.

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

Finally, all that remained was to define a filter. I chose to filter the NTUserName field because I used to be ready to determine the account name from the Error Log. (Make sure you replace the worth with the account you’re trying to trace.)

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

As you’ll see from the repeated entries, a user error message is shipped to the client. Then a mistake Log entry is formed 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 causing the error.

The message was something just like the following:

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 would like to remember of, particularly where a sizable number of databases are associated, is that the account might fail to attach to not one but multiple databases. In my case, as I acknowledged, a variety of databases had been dropped by the developers, and therefore the login attempt against each database was failing.

To cater to this, it is best to run Profiler for an inexpensive amount of your time, so all database access attempts are captured. After you’ve got stopped the trace, you’ll open the file and filter the TextData field for the error message, as shown below:

This allowed me to seek out seven databases that were missing. the subsequent step was to contact the relevant application team and notify them of the missing databases.