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 !
