Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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
  • 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.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

564 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