July 15, 2013 at 4:53 am
Hi,
I am looking for a little bit of advice on how to setup an extended event in SQL 2008 R2 which will audit each and every connection to the SQL Server.
I have managed to do so in SQL 2012, but so far the same code will not work in SQL 2008 R2 as the event sqlserver.login does not exist.
This is the event I had defined:
CREATE EVENT SESSION [RT_Test3] ON SERVER
ADD EVENT sqlserver.login(SET collect_database_name=(1),collect_options_text=(0)
ACTION(package0.collect_system_time,sqlserver.client_hostname,sqlserver.nt_username,sqlserver.username)
WHERE ([package0].[equal_boolean]([is_cached],(0))))
ADD TARGET package0.ring_buffer(SET max_events_limit=(0),max_memory=(204800))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=ON)
GO
Basically I want to capture the following for each and every connection made to the server:
1. Time
2. Hostname
3. NT User (if applicable)
4. SQL Login (If Applicable)
I may add further fields in the future as at the moment this is merely testing.
I have tested using SQL Audit and a server side trace but both generate very large files on disk due to the volume of connections being made, I want to use extended events to utilise the buffer storage as I will summaries the data stored in the buffer and store the results to seperate database. I understand that the buffers are written to in an Asynchronous mannner so can be slightly behind current activity and also that they get overwritten as the buffer fills to its set capacity.
Any help would be appreciated.
MCITP SQL 2005, MCSA SQL 2012
July 22, 2014 at 8:29 pm
Hi,
Did you ever find an answer to your question, I am having the same issue?
Thanks
Rory
July 23, 2014 at 11:10 am
I think you are out of luck as far as Xevents go. You have 2 options:
1. Configure the instance to log failed and successful logins which sends them to the event log where you get a message like this with event id 18453:
Date7/23/2014 1:08:00 PM
LogWindows NT (Application)
SourceMSSQLSERVER
Category(4)
Event18453
Useruser name
Computerserver name
Message
Login succeeded for user 'user name'. Connection made using Windows authentication. [CLIENT: <local machine>]
2. Server-side trace and just keep the trace files small and have a job process the trace files. You'd have to experiment with the frequency of the processing based on how big and how many trace files you allow.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 23, 2014 at 11:29 am
Despite the oldness of this thread, you only have those options in 2008R2.
Just another driving force for the upgrade to 2012. 😉
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 23, 2014 at 11:34 am
SQLRNNR (7/23/2014)
Despite the oldness of this thread, you only have those options in 2008R2.Just another driving force for the upgrade to 2012. 😉
Are you stalking me? You seem to be commenting on everything I post on today 😀
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 23, 2014 at 12:10 pm
Jack Corbett (7/23/2014)
SQLRNNR (7/23/2014)
Despite the oldness of this thread, you only have those options in 2008R2.Just another driving force for the upgrade to 2012. 😉
Are you stalking me? You seem to be commenting on everything I post on today 😀
Yeah go figure. You just seem to be getting to the interesting topics before me.:-D
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply