18456

If you want to trace a SQL Server login failure (18456 error) you’re going to have to get to grips with SQL Server Audit from now on. This deprecation of SQL Profiler provided a suitable opportunity to update my original post on isolating login failures in order to show how to trace login failures using SQL Server Audit, but as it would have bloated that post way too much I’ve created it as a separate post.

With a little help from SQL Server’s tracing tools you can easily get to the bottom of almost all login failures (18456 errors). If the login failure is against SQL Server 2005 or above and the default trace is running just go to my SQL Server default trace post. If you’d prefer to use extended events (as SQL Profiler is now deprecated), go to Capturing login failures via SQL Server Audit.