April 23, 2010 at 1:16 pm
My client has an absolute requirement to audit SQL Server logins (and specific data within that type of event) 24x7x365. If SQL is on, it must be audited.
Seems the SQL Error log contains some login info, but not really, and not what they need. And the Windows Event log doesn't seem to have real SQL login info.
So it seems the real way to do it, and to get the specific events they need logged is via SQL Server Profiler. That has a bajillion types of data to audit... just what I need... lookie all them badboys!!
But if I understand correctly, the actual Profiler GUI we use is intended to be for ad-hoc/interactive monitoring -- not full time, permanent logging.
This seems like a good article:
http://www.mssqltips.com/tip.asp?tip=1715
Basically it says the way to automate Profiler traces -- and therefore have them running permanently -- is to use system stored procedures. Oh hey! This doesn't look trivial at all!
This looks like another very good article:
http://www.microsoft.com/technet/abouttn/flash/tips/tips_020205.mspx
Makes it clear why you do NOT want to use the GUI version of Profiler very long:
...this option starts two separate traces that capture the same set of events. SQL Server sends one event stream to the Profiler GUI and the other event stream to a local file...
Just wondering if anyone else has permanent logging of important SQL info, and if they use Profiler (or the associated stored procedures).
Could I use SQLDiag instead of all the above? Hmmm... I hope to hear from you, and I will also dig, develop and report back!
April 23, 2010 at 1:42 pm
If you need to log info from a trace, use a server side trace instead of the GUI. A server side trace will run in the background and output the trace information to a file.
Another option would be to implement a login trigger - and capture the information you need in the trigger. Be careful with this, cause if you mess it up - you can't get back in 🙂
Also, I think with the login trigger - you wouldn't get failed logins. The server side trace is probably going to be the best option. Using this method, you have to decide how much data to keep - and where to keep it. You can easily import the data into a database and just let the files cycle.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
April 28, 2010 at 11:54 am
Thanks, that helped a lot!
I have set up a number of server-side traces.
I have included a little bit of the code below to help out the next person.
I need to audit specific events, and for those events I need every data point (every column). So this might look like a lot of code, but its almost all just repetitive...
/*
SQL Auditing - Server Side Trace.
Inspiration: http://www.mssqltips.com/tip.asp?tip=1035
SQL 2005 sp_trace_create http://msdn.microsoft.com/en-us/library/ms190362%28SQL.90%29.aspx
SQL 2005 sp_trace_setevent http://msdn.microsoft.com/en-us/library/ms186265%28SQL.90%29.aspx
*/
-- Declare variables
DECLARE @ReturnCode INT
DECLARE @traceid INT--This will be the ID of the new trace, assigned by SQL Server, generally it will be 2.
DECLARE @options INT
DECLARE @tracefile NVARCHAR(245)
DECLARE @maxfilesize bigint
DECLARE @stoptime DATETIME
DECLARE @filecount INT
DECLARE @on BIT
-- Change these on each server as needed, but normally only change @tracefile.
SET @options = 2 -- 2 = Rollover = When @maxfilesize is reached, the current file closed, new file created, sequence integer appended.
SET @tracefile = N'C:\traces\MyAuditTrace' --This is the whole path PLUS the file name, but *without* the .trc extension, and *without* any _n sequencing number!
SET @maxfilesize = 5 -- in megabytes (MB), default is 5.
SET @stoptime = NULL -- NULL = never stop
SET @filecount = 10 -- How many files to keep before old ones are deleted.
SET @on = 1 -- need to use this variable, not a 1.
-- Create New Trace.
EXEC @ReturnCode = sp_trace_create @TraceID OUTPUT, @options, @tracefile, @maxfilesize, @stoptime, @filecount
-- If error, end:
IF (@ReturnCode != 0) GOTO error
-- *************************************************************************
-- Set all the Events and Columns to collect.
-- Below are a few specific Events, but ALL Columns for those events!
-- *************************************************************************
-- 109 = Audit Add DB User Event:
EXEC sp_trace_setevent @TraceID,109,1,@on
EXEC sp_trace_setevent @TraceID,109,2,@on
EXEC sp_trace_setevent @TraceID,109,3,@on
EXEC sp_trace_setevent @TraceID,109,4,@on
EXEC sp_trace_setevent @TraceID,109,5,@on
EXEC sp_trace_setevent @TraceID,109,6,@on
EXEC sp_trace_setevent @TraceID,109,7,@on
EXEC sp_trace_setevent @TraceID,109,8,@on
EXEC sp_trace_setevent @TraceID,109,9,@on
EXEC sp_trace_setevent @TraceID,109,10,@on
EXEC sp_trace_setevent @TraceID,109,11,@on
EXEC sp_trace_setevent @TraceID,109,12,@on
EXEC sp_trace_setevent @TraceID,109,13,@on
EXEC sp_trace_setevent @TraceID,109,14,@on
EXEC sp_trace_setevent @TraceID,109,15,@on
EXEC sp_trace_setevent @TraceID,109,16,@on
EXEC sp_trace_setevent @TraceID,109,17,@on
EXEC sp_trace_setevent @TraceID,109,18,@on
EXEC sp_trace_setevent @TraceID,109,19,@on
EXEC sp_trace_setevent @TraceID,109,20,@on
EXEC sp_trace_setevent @TraceID,109,21,@on
EXEC sp_trace_setevent @TraceID,109,22,@on
EXEC sp_trace_setevent @TraceID,109,23,@on
EXEC sp_trace_setevent @TraceID,109,24,@on
EXEC sp_trace_setevent @TraceID,109,25,@on
EXEC sp_trace_setevent @TraceID,109,26,@on
EXEC sp_trace_setevent @TraceID,109,27,@on
EXEC sp_trace_setevent @TraceID,109,28,@on
EXEC sp_trace_setevent @TraceID,109,29,@on
EXEC sp_trace_setevent @TraceID,109,30,@on
EXEC sp_trace_setevent @TraceID,109,31,@on
EXEC sp_trace_setevent @TraceID,109,32,@on
EXEC sp_trace_setevent @TraceID,109,33,@on
EXEC sp_trace_setevent @TraceID,109,34,@on
EXEC sp_trace_setevent @TraceID,109,35,@on
EXEC sp_trace_setevent @TraceID,109,36,@on
EXEC sp_trace_setevent @TraceID,109,37,@on
EXEC sp_trace_setevent @TraceID,109,38,@on
EXEC sp_trace_setevent @TraceID,109,39,@on
EXEC sp_trace_setevent @TraceID,109,40,@on
EXEC sp_trace_setevent @TraceID,109,41,@on
EXEC sp_trace_setevent @TraceID,109,42,@on
EXEC sp_trace_setevent @TraceID,109,43,@on
EXEC sp_trace_setevent @TraceID,109,44,@on
EXEC sp_trace_setevent @TraceID,109,45,@on
EXEC sp_trace_setevent @TraceID,109,46,@on
EXEC sp_trace_setevent @TraceID,109,47,@on
EXEC sp_trace_setevent @TraceID,109,48,@on
EXEC sp_trace_setevent @TraceID,109,49,@on
EXEC sp_trace_setevent @TraceID,109,50,@on
EXEC sp_trace_setevent @TraceID,109,51,@on
EXEC sp_trace_setevent @TraceID,109,52,@on
EXEC sp_trace_setevent @TraceID,109,53,@on
EXEC sp_trace_setevent @TraceID,109,54,@on
EXEC sp_trace_setevent @TraceID,109,55,@on
EXEC sp_trace_setevent @TraceID,109,56,@on
EXEC sp_trace_setevent @TraceID,109,57,@on
EXEC sp_trace_setevent @TraceID,109,58,@on
EXEC sp_trace_setevent @TraceID,109,59,@on
EXEC sp_trace_setevent @TraceID,109,60,@on
EXEC sp_trace_setevent @TraceID,109,61,@on
EXEC sp_trace_setevent @TraceID,109,62,@on
EXEC sp_trace_setevent @TraceID,109,63,@on
EXEC sp_trace_setevent @TraceID,109,64,@on
--Audit Add Login to Server Role Event
EXEC sp_trace_setevent @TraceID,108,1,@on
EXEC sp_trace_setevent @TraceID,108,2,@on
EXEC sp_trace_setevent @TraceID,108,3,@on
EXEC sp_trace_setevent @TraceID,108,4,@on
EXEC sp_trace_setevent @TraceID,108,5,@on
EXEC sp_trace_setevent @TraceID,108,6,@on
EXEC sp_trace_setevent @TraceID,108,7,@on
EXEC sp_trace_setevent @TraceID,108,8,@on
EXEC sp_trace_setevent @TraceID,108,9,@on
EXEC sp_trace_setevent @TraceID,108,10,@on
EXEC sp_trace_setevent @TraceID,108,11,@on
EXEC sp_trace_setevent @TraceID,108,12,@on
EXEC sp_trace_setevent @TraceID,108,13,@on
EXEC sp_trace_setevent @TraceID,108,14,@on
EXEC sp_trace_setevent @TraceID,108,15,@on
EXEC sp_trace_setevent @TraceID,108,16,@on
EXEC sp_trace_setevent @TraceID,108,17,@on
EXEC sp_trace_setevent @TraceID,108,18,@on
EXEC sp_trace_setevent @TraceID,108,19,@on
EXEC sp_trace_setevent @TraceID,108,20,@on
EXEC sp_trace_setevent @TraceID,108,21,@on
EXEC sp_trace_setevent @TraceID,108,22,@on
EXEC sp_trace_setevent @TraceID,108,23,@on
EXEC sp_trace_setevent @TraceID,108,24,@on
EXEC sp_trace_setevent @TraceID,108,25,@on
EXEC sp_trace_setevent @TraceID,108,26,@on
EXEC sp_trace_setevent @TraceID,108,27,@on
EXEC sp_trace_setevent @TraceID,108,28,@on
EXEC sp_trace_setevent @TraceID,108,29,@on
EXEC sp_trace_setevent @TraceID,108,30,@on
EXEC sp_trace_setevent @TraceID,108,31,@on
EXEC sp_trace_setevent @TraceID,108,32,@on
EXEC sp_trace_setevent @TraceID,108,33,@on
EXEC sp_trace_setevent @TraceID,108,34,@on
EXEC sp_trace_setevent @TraceID,108,35,@on
EXEC sp_trace_setevent @TraceID,108,36,@on
EXEC sp_trace_setevent @TraceID,108,37,@on
EXEC sp_trace_setevent @TraceID,108,38,@on
EXEC sp_trace_setevent @TraceID,108,39,@on
EXEC sp_trace_setevent @TraceID,108,40,@on
EXEC sp_trace_setevent @TraceID,108,41,@on
EXEC sp_trace_setevent @TraceID,108,42,@on
EXEC sp_trace_setevent @TraceID,108,43,@on
EXEC sp_trace_setevent @TraceID,108,44,@on
EXEC sp_trace_setevent @TraceID,108,45,@on
EXEC sp_trace_setevent @TraceID,108,46,@on
EXEC sp_trace_setevent @TraceID,108,47,@on
EXEC sp_trace_setevent @TraceID,108,48,@on
EXEC sp_trace_setevent @TraceID,108,49,@on
EXEC sp_trace_setevent @TraceID,108,50,@on
EXEC sp_trace_setevent @TraceID,108,51,@on
EXEC sp_trace_setevent @TraceID,108,52,@on
EXEC sp_trace_setevent @TraceID,108,53,@on
EXEC sp_trace_setevent @TraceID,108,54,@on
EXEC sp_trace_setevent @TraceID,108,55,@on
EXEC sp_trace_setevent @TraceID,108,56,@on
EXEC sp_trace_setevent @TraceID,108,57,@on
EXEC sp_trace_setevent @TraceID,108,58,@on
EXEC sp_trace_setevent @TraceID,108,59,@on
EXEC sp_trace_setevent @TraceID,108,60,@on
EXEC sp_trace_setevent @TraceID,108,61,@on
EXEC sp_trace_setevent @TraceID,108,62,@on
EXEC sp_trace_setevent @TraceID,108,63,@on
EXEC sp_trace_setevent @TraceID,108,64,@on
-- *************************************************************************
-- Set any Filters for this trace.
-- http://msdn.microsoft.com/en-us/library/ms174404%28v=SQL.90%29.aspx
-- *************************************************************************
-- Exclude SQL Profiler from this trace.
-- @TraceID feeds in the trace we just created, 10 is the ApplicationName column under sp_trace_setevent,
-- 0 is the AND logical operator, 7 is the EQUAL comparison operator:
EXEC sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'
-- Start the trace
EXEC sp_trace_setstatus @TraceID, 1
-- Display trace id for future references. Generall it will be 2.
SELECT 'You just created a new Trace with ID = ' + CAST( @TraceID AS VARCHAR(100) )
GOTO finish
-- error trap
error:
SELECT 'An error has occured! Return Code = ' + CAST( @ReturnCode AS VARCHAR(100) )
-- exit
finish:
GO
-- ***********************
-- End creating new trace.
-- ************************************
/***************************************
List all traces running on server:
fn_trace_getinfo
NOTE: There is (almost) always traceid 1, the Default Trace,
so the trace we create will normally be traceid 2.
1 = Trace options. Generally just 2 = TRACE_FILE_ROLLOVER
2 = File name
3 = Max size
4 = Stop time
5 = Current trace status
****************************************/
SELECT * FROM :: fn_trace_getinfo(default)
--WHERE traceid <> 1 -- use this if you don't want to see the Default Trace http://msdn.microsoft.com/en-us/library/ms175513%28SQL.90%29.aspx
ORDER BY traceid, property
--http://msdn.microsoft.com/en-us/library/ms176034%28v=SQL.90%29.aspx
--sp_trace_setstatus trace_id, status
sp_trace_setstatus 2, 0 --Stop a trace
sp_trace_setstatus 2, 1 --Start a trace
/***************************************
--Delete a trace (usually Trace #2)
--http://msdn.microsoft.com/en-us/library/ms176034%28v=SQL.90%29.aspx
--sp_trace_setstatus trace_id, status
***************************************/
sp_trace_setstatus 2, 0 --first Stop
GO
sp_trace_setstatus 2, 2 --then DELETE
GO
April 28, 2010 at 12:00 pm
Another question, if I may: if you look at that code I posted above, there is a simple command to list all the traces running on a server: SELECT * FROM :: fn_trace_getinfo(default)
But there seems to be no command whatsoever to see the actual definition of a trace. Again, if you look at the code I posted above, and add in a bunch more events, then create those traces on 50 servers, and THEN you realize oops! I forgot to add one thing, so you have to go back and delete, then recreate all those traces, and THEN you get confused -- which ones have I changed to the new version and which are the old?? Good luck with that, with no way to see the definition of those traces!!
Is there any way to see the definition of a trace, once you've created it??
April 28, 2010 at 12:39 pm
You can replace the function fn_trace_getinfo with:
SELECT * FROM sys.traces;
To get more information about the traces running, use the following functions:
fn_trace_getfilterinfo
fn_trace_geteventinfo
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
April 28, 2010 at 12:42 pm
Oh - one more thing, use the catalog views:
sys.trace_columns
sys.trace_events
sys.trace_categories
sys.trace_event_bindings
sys.trace_subclass_values
With the above, you can convert the internal column/event id's to their external values.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
April 28, 2010 at 12:55 pm
jpSQLDude (4/28/2010)
Another question, if I may: if you look at that code I posted above, there is a simple command to list all the traces running on a server: SELECT * FROM :: fn_trace_getinfo(default)But there seems to be no command whatsoever to see the actual definition of a trace. Again, if you look at the code I posted above, and add in a bunch more events, then create those traces on 50 servers, and THEN you realize oops! I forgot to add one thing, so you have to go back and delete, then recreate all those traces, and THEN you get confused -- which ones have I changed to the new version and which are the old?? Good luck with that, with no way to see the definition of those traces!!
Is there any way to see the definition of a trace, once you've created it??
I build the trace in a stored procedure, so the definition is in there. It appends the date to the end of the trace name, and I schedule it to start whenever SQL restarts. That way it's always running.
SELECT @path = 'E:\MSSQL.1\MSSQL\Log_Events\'
SET @path = @path + N'MyDMLtrace_SQL_x' + convert(varchar(25),getdate(),12)--system appends .trc automatically for the filename
May 10, 2010 at 8:27 am
It seems like, finally, something has dawned on me:
All user-defined server side traces are DELETED every time SQL Server is restarted (or even if you just restart the SQL Server Agent).
You use sp_trace_create to create a trace. You can use sp_trace_setstatus to start, stop and DELETE a trace.
So you might actually think that if you create a trace, and start it, and leave it running, you could reboot the server, and OK fine so you have to RESTART the trace when the server comes back up.
You would be wrong.
And by you I mean me. :w00t:
If you want a server side trace to be running ALL the time (such as for Auditing), then every time SQL Server or Agent comes back online, you must RECREATE the entire trace definition, and also turn it back on again.
Oh.
Here is another good article:
http://www.virtualobjectives.com.au/sqlserver/server_side_tracing_2005.htm#auto
I will mergeify all this and report back........
May 10, 2010 at 8:33 am
jpSQLDude (5/10/2010)
All user-defined server side traces are DELETED every time SQL Server is restarted (or even if you just restart the SQL Server Agent).
SQL Server, yes, SQL Agent, no. The traces run within SQL Server engine.
You can set up a startup procedure to start the traces. You can also enable c2 or common compliance auditing, which auto-starts on SQL startup (and will stop SQL if it can't audit) and includes logins as well as other events. I suggest Common Compliance rather than c2. C2 is rather harsh on SQL 2005.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 11, 2010 at 10:41 am
I would suggest you also consider exactly how to meet the implicit requirement of "if the traces fail, SQL Server logins need to fail"; i.e. if your logging is down, the database must cease allowing new logins (or just go down as well).
If you only accept unencrypted connections, you might be able to do something at a stateful firewall/network analysis/packet sniffer level as well.
May 11, 2010 at 11:03 am
Just wondering what requirements do you need for an audit? Capturing a serverside trace is fine, however, if the data table needs to be audited you may need to look at other options.
There a bunch of good 3rd party auditing software out there. It all depends on what you need.
May 12, 2010 at 8:58 pm
If you're using SQL 2005+ you can also setup an Event Notification for all the Audit_Login events. I'm implementing this now. It's easier to off load the service broker queue with a stored procedure than load a server side trace file and it is not transactional like a login trigger.
May 13, 2010 at 8:04 am
I was looking at a server side trace to replace an interactive trace that I run from Profiler. I don't see an option to Save to a Table for sp_trace_create like you can do with Profiler. Is this not an available?
I know that there is a potential of continuosly filling a table that needs to be addressed, but I would think this would be an option.
I am currently running SS 2000 but am in the process of moving to SS 2008 and have the tools installed. I didn't see any reference to Save to a Table for sp_trace_create in either 2000 or 2008 BOL.
Steve
May 13, 2010 at 8:26 am
steve block (5/13/2010)
I was looking at a server side trace to replace an interactive trace that I run from Profiler. I don't see an option to Save to a Table for sp_trace_create like you can do with Profiler. Is this not an available?
It's not, fortunately, because it's an incredibly bad idea. Running a profiler trace to a table is the most intensive way to run profiler, with the highest impact on the server that is been traced.
Save the trace to a fast, local drive, make sure that rollover is in place, that the drive has sufficient space. You can always import and analyse later.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 13, 2010 at 8:56 am
Gail,
Thank you for the information. fn_trace_gettable looks like what I need to get the trace information into a table. I will try it out.
Steve
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply