I noticed some Failure Audit errors, in the Windows Application event log of the server that my SQL Server runs on, stating:

Event ID: 18456  
Login failed for user 'DOMAIN\username'.  
Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]

The user mentioned in the errors was in the sysadmin server role, and I checked that the user had a default database associated with it. The errors recurred every five minutes. Does anyone have any insight into this issue?

The SQL Server service itself and SQL Server Agent run as that account.

  • it seems that some service has been started with an incorrect password, or the password of this user has been changed. – McNets Jan 20 '17 at 22:17

I checked whether I had any SQL Agent jobs on the server that ran every five minutes, and found one that was failing. When I checked the job steps, one of them had a blank Database drop down. I selected a database from the drop down, and that corrected the issue. Now, after finding the root cause, the error makes perfect sense: that user definitely couldn't open an undefined database.

  • It's possible that this was a database that had been dropped or renamed. The database name might have still been present in the metadata in msdb, but it wouldn't be displayed correctly in the UI, because that dropdown lists the valid names currently in sys.databases. – Aaron Bertrand Jan 20 '17 at 19:45
  • @AaronBertrand I think that's exactly what happened. The database was renamed in the past, and evidently the job wasn't updated to reflect that. – arcain Jan 20 '17 at 21:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.