Solved

Profiler Trace Not Capturing Logon Events

Posted on 2012-03-11
9
476 Views
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

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
0
Comment
Question by:Swindle
[X]
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
9 Comments
 
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
0
 
LVL 11

Author Comment

by:Swindle
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...
0
 
LVL 11

Author Comment

by:Swindle
ID: 37711989
And before anyone asks, there are no filters applied to the trace.  I made sure of that as well.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

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

Expert Comment

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

Author Comment

by:Swindle
ID: 37722639
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
 
LVL 20

Accepted Solution

by:
Marten Rune earned 500 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:
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
 
LVL 11

Author Comment

by:Swindle
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.
0
 
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
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help needed in sql query 4 26
SQL Server Sum Over Multiple Tables 20 32
Upgrading to SQL Server 2015 Express 2 29
Applying Roles in Common Scenarios 3 16
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

735 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