SQL Server Audit

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » SQL Server Audit

19 posts, Page 2 of 2

««1 2

SQL Server Audit

Rate Topic

Display Mode

Topic Options

Author

Message

Lowell

Posted Monday, September 10, 2012 6:04 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 4:59 PM
Points: 11,792, Visits: 28,078

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

raj_melvin

Posted Tuesday, September 11, 2012 9:00 PM

Grasshopper

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

Lowell

Posted Wednesday, September 12, 2012 6:32 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 4:59 PM
Points: 11,792, Visits: 28,078

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

raj_melvin

Posted Wednesday, September 12, 2012 8:54 AM

Grasshopper

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

Lowell

Posted Wednesday, September 12, 2012 9:30 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 4:59 PM
Points: 11,792, Visits: 28,078

raj_melvin (9/12/2012)

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

raj_melvin

Posted Wednesday, September 12, 2012 9:42 AM

Grasshopper

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

Lowell

Posted Wednesday, September 12, 2012 11:28 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 4:59 PM
Points: 11,792, Visits: 28,078

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

raj_melvin

Posted Wednesday, September 12, 2012 11:54 AM

Grasshopper

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

Randall F Smith

Posted Tuesday, September 25, 2012 5:26 PM

Forum 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 »

19 posts, Page 2 of 2

««1 2

Permissions