Login Failed for user 'xxxx\xxxxx' Reason: Failed to open the explicitly specified database. [CLIENT: xx.x.xx.xxx]
You have accounts trying to login to the SQL Server to a specific database, but that database doesn't exist. You get this error in your error log
Error: 18456, Severity: 14, State: 38
Login failed for user <account name>. Reason: Failed to open the explicitly specified database. [CLIENT: <IP address trying to connect>]
Well, this error is kind of helpful but it doesn't tell you which database its trying to connect to. First, check to see if any databases are offline or in a recovering state. If all databases are up and running then the client IP is trying to connect to a database that does not exist. Use NSLOOKUP to find the name of the machine trying to connect. Next step is to find that database. We can do this by using SQL Profiler. The database name is not captured in the default trace. Here is a great article in setting up the trace.
http://www.mssqltips.com/sqlservertip/2581/sql-server-error-18456-finding-the-missing-databases/
Use a blank trace and make selections from these two categories
Select theses events
You will only need these columns
Filter the trace on the NTUSER column for the name of the account trying to connect. NTUSER is user name without the domain info.
To find the database that the user is trying to connect to will be in the User Error Message event. Look in the TextData column
Error: 18456, Severity: 14, State: 38
Login failed for user <account name>. Reason: Failed to open the explicitly specified database. [CLIENT: <IP address trying to connect>]
Well, this error is kind of helpful but it doesn't tell you which database its trying to connect to. First, check to see if any databases are offline or in a recovering state. If all databases are up and running then the client IP is trying to connect to a database that does not exist. Use NSLOOKUP to find the name of the machine trying to connect. Next step is to find that database. We can do this by using SQL Profiler. The database name is not captured in the default trace. Here is a great article in setting up the trace.
http://www.mssqltips.com/sqlservertip/2581/sql-server-error-18456-finding-the-missing-databases/
Use a blank trace and make selections from these two categories
- Security Audit
- Error and Warnings
Select theses events
- User Error Message
- Error Log
- Audit Log Failed
You will only need these columns
- TextData
- ApplicationName
- NTUser
- LoginName
- SPID
Filter the trace on the NTUSER column for the name of the account trying to connect. NTUSER is user name without the domain info.
To find the database that the user is trying to connect to will be in the User Error Message event. Look in the TextData column