Aug 20, 2013

Trace SQL login error

Solution from: http://www.eraofdata.com/sql-18456-login-failures/

In SQL 2008 R2, I encountered this type of message:
Login failed for user 'sa'. Reason: Failed to open the explicitly specified database.

That’s all, no more information. If I look into Windows Event log, I only can see:Login failed for user 'sa'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]

That’s not clear enough. So just a trace from SQL Profiler including these 3 types of message can help to give more details:

  • Audit Login Failed (under Security Audit)
  • User Error Messages (under Errors & Warnings)
  • Errorlog (under Errors & Warnings)

Thanks SQL Profiler !