Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

SQL Server Audit Expand / Collapse
Author
Message
Posted Monday, September 10, 2012 6:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:01 PM
Points: 12,744, Visits: 31,074
raj_melvin (9/10/2012)
we are fine to log into a flat file

but I am unable to capture the required informations like " Hostname" ,"local ip" and "Application Name" ect.

this is for a enterprise application only I am testing this in development and Audit it running with the missing informations.


Thanks


yeah, wrong tool for the right job, I'm thinking;
I'm reading up a bit deeper on SQL Audit now, but it looks like some of that information is not currently available to the audit process, but is available form the connection information that you can use in a trigger..


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1356678
Posted Tuesday, September 11, 2012 9:00 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 18, 2012 2:07 PM
Points: 11, Visits: 21
Could any one please help.thanks in advance.
Post #1357771
Posted Wednesday, September 12, 2012 6:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:01 PM
Points: 12,744, Visits: 31,074
raj_melvin (9/11/2012)
Could any one please help.thanks in advance.


help with what? you already determined SQL Audit doesn't do what you are asking, right? and I gave you two working examples, one of a trigger, and the other of a trace, which does capture what you are asking.

what are you looking for if not those three items?


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1357937
Posted Wednesday, September 12, 2012 8:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 18, 2012 2:07 PM
Points: 11, Visits: 21
Thanks for your reply.

Trigger - yes it's working solution But the company doen't wanted to use the trigger
SQL Profiler - unable to trace it in a table - Getting error "Failed to Save table <tablename> cursor operation conflict"
SQL Audit - How to get the Hostname, program name etc...

Post #1358029
Posted Wednesday, September 12, 2012 9:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:01 PM
Points: 12,744, Visits: 31,074
raj_melvin (9/12/2012)
Thanks for your reply.

Trigger - yes it's working solution But the company doen't wanted to use the trigger
SQL Profiler - unable to trace it in a table - Getting error "Failed to Save table <tablename> cursor operation conflict"
SQL Audit - How to get the Hostname, program name etc...



No idea about the error in profiler; my suggestion was a server side trace.
take a look at that and see if it you can get your arms around that instead.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1358060
Posted Wednesday, September 12, 2012 9:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 18, 2012 2:07 PM
Points: 11, Visits: 21
Thanks .

Could you please help me with server side trace,Can we run the trace 24x7? Any sample will be helpfull

Post #1358069
Posted Wednesday, September 12, 2012 11:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:01 PM
Points: 12,744, Visits: 31,074

If you need someone with more experience to actually set this up for you, consdier grabbing a consultant or a DBA you know and trust. Believe me, I know anything new can be scary, and it's easier to ignore than to try something new.

Like i mentioned in my post quoted here, I have this running on my servers, with a rollover 24-7; I also have a DML trace, capturing every command executed as well; the procedure sp_procoption (linky to Books Online) is what you use to set a procedure to be executed every time the server restarts.

You need to do that, because except for the C2 Audit and the default DDL trace, all traces stop when the server restarts.

server side traces have minimal impact, unlike a profiler session.

If you really need it, myself or any of the frequent posters here could be hired as a consultant to get you set up ; or you could do a little bit of investigation and get yourself up to speed.

Lowell (9/10/2012)


here's a link to a Login trace I like to use for an example; you would need to make the procedure a startup procedure so it restarts when the server restarts. it also creates a view, so i can select from the trace anytime i want.
sp_AddMyLogonTrace.txt


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1358143
Posted Wednesday, September 12, 2012 11:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 18, 2012 2:07 PM
Points: 11, Visits: 21
Thanks much for the guidance This is really helpfull

I will explore more in this .

Thanks
Post #1358146
Posted Tuesday, September 25, 2012 5:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 08, 2012 11:35 AM
Points: 4, Visits: 9
Take a look at this logon event generated by SQL 2008 Audit and then refined by LOGbinder (www.logbinder.com). Does that give you what you need? http://www.ultimatewindowssecurity.com/securitylog/encyclopedia/event.aspx?eventid=24001
Post #1364367
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse