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


Best way to monitor or audit SQL


Best way to monitor or audit SQL

Author
Message
shusta
shusta
SSC Veteran
SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)

Group: General Forum Members
Points: 242 Visits: 185
Hello,

I'm looking for any thoughts on a good monitoring tool for SQL server 2005. Although the users are safely locked out and using the front end application as they should be the auditors are still asking what I'm doing to monitor the back-end SQL accounts such as "sa" even though this account is not handed out to anyone.

Without breaking the bank what can you suggest that might meet my needs to make the auditors happy?

Thank you for the help.
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39458 Visits: 14411
On SQL 2005 Trace should work just fine. Most third-party tools will likely leverage Trace anyway. You can filter on SessionLoginName to capture all SQL text issued to the instance by any member of the sysadmin Role, but that needs to be defined when the Trace is started. Of course there are ways to circumvent that Trace, namely creating a new login, adding it to the sysadmin Role, then logging in as that login to carry out an attack. The creation of the login will be logged though, however server/service reboots could afford someone a chance to get in unnoticed if they can prevent the Trace from starting. The bottom line is that a skilled person that can enter using a login in the sysadmin Role will know how to circumvent all of this type of auditing but it will catch the lesser skilled ones and make the more skilled ones time a little harder if they want to avoid detection.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
shusta
shusta
SSC Veteran
SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)

Group: General Forum Members
Points: 242 Visits: 185
OK.. So to satisfy auditors and myself would you have a recommendation for a 3rd party app that I could use to monitor this? I was hoping to find a server based app with clients on the SQL server(s) so I'm able to catch the type of activity you're referring to. We already have our system locked down for the average user but how or what would you recommend to monitor the gate keeper (me).

In the end that's what the auditors are asking for, a report on the gate keeper and the fact that he/she has used their powers for good and not evil.
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39458 Visits: 14411
Trace is going to be your best option on 2005. You could also look into C2 or Common Criteria auditing (which both use Trace by the way) as those are already setup for you and are enabled with a simple server config. As I said, on 2005 your options are limited. I am not familiar with any third-party apps that can give you something to satisfy your auditors because it depends on what they want to see out of the audit. It's also worth mentioning that if you are one of the people the auditors need to account for with a custom auditing solution then you probably shouldn't be the only one involved in designing it ;-)

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
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