Profiler Trace Not Capturing Logon Events

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

DECLARE @ON BIT
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
SELECT @TraceID AS TraceID
GOTO finish

error:
SELECT @rc AS ErrorCode

finish:
GO
LVL 11
SwindleAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Marten RuneSQL Expert/Infrastructure ArchitectCommented:
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
0
SwindleAuthor Commented:
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...
0
SwindleAuthor Commented:
And before anyone asks, there are no filters applied to the trace.  I made sure of that as well.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Marten RuneSQL Expert/Infrastructure ArchitectCommented:
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
0
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
It looks as if your trace is not complete.
lookat example in:
http://social.msdn.microsoft.com/Forums/eu/sqldatabaseengine/thread/ca52b3b4-3cfb-4370-aae6-21716aeb6134

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
0
SwindleAuthor Commented:
http://msdn.microsoft.com/en-us/library/ms186265.aspx

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.
0
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
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:
http://technet.microsoft.com/en-us/library/dd277388.aspx#XSLTsection123121120120
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SwindleAuthor Commented:
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.
0
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.