SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server Audit


SQL Server Audit

Author
Message
Lowell
Lowell
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69734 Visits: 40917
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
raj_melvin
raj_melvin
SSC-Enthusiastic
SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)

Group: General Forum Members
Points: 125 Visits: 21
Could any one please help.thanks in advance.
Lowell
Lowell
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69734 Visits: 40917
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
raj_melvin
raj_melvin
SSC-Enthusiastic
SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)

Group: General Forum Members
Points: 125 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...
Lowell
Lowell
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69734 Visits: 40917
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
raj_melvin
raj_melvin
SSC-Enthusiastic
SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)

Group: General Forum Members
Points: 125 Visits: 21
Thanks .

Could you please help me with server side trace,Can we run the trace 24x7? Any sample will be helpfull
Lowell
Lowell
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69734 Visits: 40917
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
raj_melvin
raj_melvin
SSC-Enthusiastic
SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)

Group: General Forum Members
Points: 125 Visits: 21
Thanks much for the guidance This is really helpfull

I will explore more in this .

Thanks
Randall F Smith
Randall F Smith
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search