Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Profiler Trace Not Capturing Logon Events

Posted on 2012-03-11
Medium Priority
Last Modified: 2012-03-16
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

Question by:Swindle
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
LVL 20

Expert Comment

by:Marten Rune
ID: 37708709
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
LVL 11

Author Comment

ID: 37711987
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...
LVL 11

Author Comment

ID: 37711989
And before anyone asks, there are no filters applied to the trace.  I made sure of that as well.
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

LVL 20

Expert Comment

by:Marten Rune
ID: 37712184
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
LVL 20

Expert Comment

by:Marten Rune
ID: 37719281
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
LVL 11

Author Comment

ID: 37722639

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.
LVL 20

Accepted Solution

Marten Rune earned 2000 total points
ID: 37724275
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
LVL 11

Author Comment

ID: 37729913
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.
LVL 20

Expert Comment

by:Marten Rune
ID: 37729956
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

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question