Home Dashboard Directory Help
Search

Audit trace does not capture clientprocessid column that is available in SQL Profiler by Ajmer Dhariwal


Status: 

Closed
 as Won't Fix Help for as Won't Fix


3
0
Sign in
to vote
Type: Bug
ID: 790274
Opened: 6/16/2013 1:22:45 PM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

As SQL Profiler is now deprecated, the alternative for capturing the details of a login failure is to use Server Audits via the FAILED_LOGIN_GROUP.
The additional_information column in the audit file contains an xml trace with information like the address, error state etc (pretty much what would get dumped to the error log), but not the PID of the client process generating the failure.

If I were to trace the login failure as per normal via Profiler, I'd select the login failure event and clientprocessid column (amongst other things), and 90% of the time it would be the clientprocessid column which helps me track down which one of the many processes on the server that is generating the login failure is the one causing the problem.
Without Profiler, the only information I would have via the Server Audit event tracing is to either run a network trace or just work through all the processes on that server which might be making a connection to the database.
Details
Sign in to post a comment.
Posted by Ajmer Dhariwal on 6/18/2013 at 12:53 PM
Thanks for looking into it Walter, although I am disappointed because this is something of a regression as it is now impossible to establish the PID of a connecting process generating a login failure without using a deprecated product.
It's strange that a newer version of SQL is offering less functionality than an older version.
Posted by Microsoft on 6/17/2013 at 11:23 AM
Hello Ajmer. Thanks for the feedback. We triaged this issue and at this time do not plan to address this in a future version of SQL Server. -Walter A Jokiel, Program Manager, SQL Server (wajokiel@microsoft.com)
Sign in to post a workaround.