Best way to monitor or audit SQL

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Security » Best way to monitor or audit SQL

Best way to monitor or audit SQL

Rate Topic

Display Mode

Topic Options

Author

Message

shusta

Posted Monday, January 28, 2013 12:44 PM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, February 27, 2013 6:08 AM
Points: 24, Visits: 143

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.

Post #1412612

opc.three

Posted Monday, January 28, 2013 2:30 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 8:26 AM
Points: 6,737, Visits: 11,791

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1412661

shusta

Posted Tuesday, January 29, 2013 6:40 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, February 27, 2013 6:08 AM
Points: 24, Visits: 143

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.

Post #1413010

opc.three

Posted Tuesday, January 29, 2013 7:03 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 8:26 AM
Points: 6,737, Visits: 11,791

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 Wink

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1413029

« Prev Topic | Next Topic »

Permissions