We help IT Professionals succeed at work.

Profiler Trace Not Capturing Logon Events

Swindle asked
I'm trying to decommission an old SQL 2000 server, but I would like to audit the logins on it for a couple of weeks just to make sure I haven't overlooked anyone that is connecting to it.  I created a profiler trace using the script below, but it never captures anything.  The file never gets a single entry.  I checked fn_trace_getinfo and verified that the trace is indeed running/active.  I also checked fn_trace_geteventinfo to make sure it had the right events to capture.  I'm baffled...  When I run profiler from the GUI everything works great, but I need it to run server side.  Please help!

-- Create a Queue
DECLARE @rc             INT
DECLARE @TraceID        INT
DECLARE @maxfilesize    BIGINT

SET @maxfilesize = 500

EXEC @rc = sp_trace_create @traceid=@TraceID OUTPUT, @options=2, @tracefile=N'F:\LogonTrace\Trace', @maxfilesize=@maxfilesize, @stoptime=NULL
IF (@rc != 0) GOTO error

SET @ON = 1
--EventID =14: Audit Login, @ColumnID=6: NT UserName
EXEC sp_trace_setevent @TraceID, 14, 6, @ON
--EventID =14: Audit Login, @ColumnID=10: Application Name
EXEC sp_trace_setevent @TraceID, 14, 10, @ON
--EventID =14: Audit Login, @ColumnID=11: Login Name
EXEC sp_trace_setevent @TraceID, 14, 11, @ON
--EventID =14: Audit Login, @ColumnID=14: Start Time
EXEC sp_trace_setevent @TraceID, 14, 14, @ON
--EventID =14: Audit Login, @ColumnID=35: Database Name
EXEC sp_trace_setevent @TraceID, 14, 35, @ON

-- Set the trace status to start
EXEC sp_trace_setstatus @TraceID, 1

-- display trace id for future references
GOTO finish

SELECT @rc AS ErrorCode

Watch Question

Marten RuneSQL Expert/Infrastructure Architect

Check permission on folder F:\LogonTrace\
You can put everone modify to simplify.

If this doesn't work create some trace that captures more and use the same folder and a serverside trace again.

It runs with the account for sql server service.

Regards Marten


Thanks for the response and at least taking a stab at it.  It couldn't be related to permissions because it was able to create the file without a problem.  I went back today and checked and there are some logon events added now.  I've been logging on and off of that server and it isn't getting written to the trace file though, so it seems random as to what is and isn't getting written.  It sounds to me like we need to get this thing turned off even faster than I anticipated maybe...


And before anyone asks, there are no filters applied to the trace.  I made sure of that as well.
Marten RuneSQL Expert/Infrastructure Architect

try this on a sql2005. I may be mistaken but I belive it wont work on sql 2000. Its late at night in Sweden now, will check further tomorrow
Marten RuneSQL Expert/Infrastructure Architect

It looks as if your trace is not complete.
lookat example in:

Youre missing
@TraceID, 10, xxx
@TraceID, 12, xxx
@TraceID, 17, xxx
You can borrow the code for "exec sp_trace_setevent ..." from the link provided. It should work on a SQL2000, though I dont have any systems to try it out on. Since they have all been migrated since long gone.

Regards Marten



10 - RPC:Completed
12 - SQL:BatchCompleted
17 - ExistingConnection

Those aren't related to auditing logins, I went ahead and added them anyways just for the sake of argument.  It's still not working.  I can connect to the server and even run queries without it getting logged to the trace.
SQL Expert/Infrastructure Architect
Seems as I see it as if youre running profiler against the wrong server, or with the wrong credentials, or it's simply broken wich I have never seen (fulltime DBA sinse >5yrs, and DBA as part time worktask >15 yrs).

May I suggest a simpler solution, log all logins in the log, see example in:
Quote: '... to audit login activity, including failed login attempts, to the Windows Application log. To configure this auditing, launch Enterprise Manager, select a database server, right-click Properties, go to the Security tab, and set your desired level of auditing'.

Now all logins are logged in the windows event log. This should be sufficient to see wether it's being used or not!

Regards Marten


Thanks for the help Marten.  Like you, I've never seen anything like this and I've been a full time DBA for almost 12 years now.  The great thing is that it gets even wierder!  For the last 24 hours it started logging everything correctly without any more intervention (well...as far as I know.  At least it's logging me connecting now where it wasn't before).  I'm going to go ahead and call it good for now and just use what I got over the last 24 hours.  

My next step was going to be the C2 auditing, so I'll go ahead and give you the points since that would have given me the info too.
Marten RuneSQL Expert/Infrastructure Architect

Glad to hear it worked out for you. Though you don't need C2 auditing to lag all logins (successful and failed). Just a clarification.

SQL trace is designed to not fail the SQL service if the strain on the machine is high. Its not one of those were the CPU slows down when the machine is idle?

Just a thought

Regards Marten

Explore More ContentExplore courses, solutions, and other research materials related to this topic.