Native Audits

Question

Native Audits

Steve Jones - SSC Editor

SSC Guru

Points: 736255
More actions
May 21, 2015 at 10:02 pm

#398492

Comments posted to this topic are about the item Native Audits

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Gary Varga SSC Guru Points: 82166 More actions · Answer 1

I am vaguely aware of the feature but have never implemented it nor have I seen it used at any client. Most clients either do not audit or roll out an existing solution already in use.

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

dbaninja Old Hand Points: 378 More actions · Answer 2

I have successfully used SQL's native auditing features. The only set-back I found was the location for the data (you can only choose "File", Windows "Security Log" or Windows "Application Log"). I would have preferred the data being stored in a table in a database on the instance. We opted for the "File" destination but this can be a bit tricky to maintain (and query) especially if you manage 100+ SQL instances.

"My gosh! It's full of [SELECT] stars!"

phegedusich SSCommitted Points: 1552 More actions · Answer 3

One problem with SQL Audit: it's exposed to the sysadmin. This doesn't fly with our Audit department, so we use Guardium instead.

Gary Varga SSC Guru Points: 82166 More actions · Answer 4

phegedusich (5/22/2015)
One problem with SQL Audit: it's exposed to the sysadmin. This doesn't fly with our Audit department, so we use Guardium instead.

Definitely a potential for abuse. We all know that nearly every person with access to a sysadmin account wouldn't abuse the situation but unfortunately I believe I was correct when I felt the need to use the word nearly :'-(

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 5

phegedusich (5/22/2015)
One problem with SQL Audit: it's exposed to the sysadmin. This doesn't fly with our Audit department, so we use Guardium instead.

I agree with this. I think auditing needs to be built in to the product, but from a separate interface that can be controlled and managed by a non-DBA.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 6

phegedusich (5/22/2015)
One problem with SQL Audit: it's exposed to the sysadmin. This doesn't fly with our Audit department, so we use Guardium instead.

Same problem with us too. We would need better separation of duties.

SQLGrinder SSC Journeyman Points: 95 More actions · Answer 7

We recently had the requirement to audit some of our sensitive servers. I looked at SQL Server Audit and another draw back is that is only runs on Enterprise. And yes, it should definitely provide the ability to log to a database and not a file or windows log. This is probably why the tool goes un-noticed and un-used.

We eventually found a few third party tools and settled on Idera Compliance Manager. It runs a small trace and uses the trace API to filter and log data into a SQL repository. I believe future versions will be more extended events based. Since we are tracking DML, so far I have found it challenging trying to sift through the volume of data it logs to get a useful report. I don't see any real way to get a pass/fail or exceptions type of audit report. It seems to still require a someone analyzing all the data and making a determination if it is good or bad. You can setup Event filters to filter out data to that gets logged but that could mean you are potentially missing something you may need later and you can setup Alerts based on "suspicious" activities.

Needless to say there is a big learning curve and perhaps some new processes to develop.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

Steve Jones - SSC Editor (5/22/2015)
phegedusich (5/22/2015)
One problem with SQL Audit: it's exposed to the sysadmin. This doesn't fly with our Audit department, so we use Guardium instead.
I agree with this. I think auditing needs to be built in to the product, but from a separate interface that can be controlled and managed by a non-DBA.

Uh huh... and who's going to audit that non-DBA's actions? While I agree with auditing functions, it's becoming like a grease stain. The more you rub it, the bigger the stain gets the more you need to rub it, ad infinitum. Eventually, it'll be the audit version of Cohn's Law.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 9

The non-DBA's actions certainly could be an issue, but I'd hope that the person doing the auditing isn't a privileged user that can do something dangerous on the system. If the auditing data is available for application actions, then this doesn't provide any protection.

phegedusich SSCommitted Points: 1552 More actions · Answer 10

Jeff, Guardium captures the activity and provides very granular reporting capabilities (who did what when). It's read-only as far as interaction with SQL Server. A Guardium user can't do anything to the data, config, etc. It lives inside the SQL process space, so it can't be turned off without stopping the SQL instance.

I know audit is painful, but it's an absolute in my industry.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 11

phegedusich (6/8/2015)
Jeff, Guardium captures the activity and provides very granular reporting capabilities (who did what when). It's read-only as far as interaction with SQL Server. A Guardium user can't do anything to the data, config, etc. It lives inside the SQL process space, so it can't be turned off without stopping the SQL instance.
I know audit is painful, but it's an absolute in my industry.

Yep... Sounds good, especially the part about it being read-only as far as the interaction with SQL Server goes. But even there, someone has the keys to it and there's just no getting around that. Yes, I realize that there should be a practical limit to who's guarding who but the auditors don't think so. Some of the rules are becoming totally ridiculous but still have little impact. Even the IRS was recently broken into recently. Did they pass their audit? Did they even have an audit?

So far as audits go, yes, they are painful and they're an absolute necessity in my industry as well. My point is that they are becoming more and more painful unnecessarily. Companies want audits from us and we certainly embrace the reasons why they are asking but answering the same questions with slightly different wording in 20 different sections of a ridiculously thick document is a waste of everyone's time. They also ask for stupid things like a list of people "who have access" and they want screen shots and all sorts of proof. What's wrong with that? They could easily all be faked because there's no oversight or supervision of the people doing the screen shots or other proofs, etc. It's just stupid but they keep making the grease spot of audits larger and larger and with no real guarantee that the information is even accurate. It's especially frustrating when the people who want the audit done don't have a clue as to what the results are trying to tell them. It's like someone in HR trying to do a technical interview for a position that only a true SQL Server god could fill when all they really need is someone that knows T-SQL real well and they don't know what to ask the candidates.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

corsair1 Old Hand Points: 308 More actions · Answer 12

Guardium from IBM and SecureSphere from Imperva are designed to monitor and alert all risk trying to attack your data. They both have essentially SQL proxy's which can prevent SQL injection and record all incoming SQL requests for analysis on their own hardware. They also monitor where historically the biggest risk comes from....internally. I believe one or both advertise the fact that they watch the admins. In the interest of protecting my company's data I would love to get either one anywhere I work. On the other hand SQL Audit and SQL Policies are for DBA's to monitor their systems and increase security without relying on external tools. To answer Steve's original question one of the businesses I'm doing work for is using over 100 SQL policies combined with several SQL Audits per server to examine several hundred servers for compliance on a daily basis. We expect the number of both Policies & Audits to increase quickly as we gain more experience with them.

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 13

Jeff Moden (6/8/2015)
...
It's just stupid but they keep making the grease spot of audits larger and larger and with no real guarantee that the information is even accurate. It's especially frustrating when the people who want the audit done don't have a clue as to what the results are trying to tell them. It's like someone in HR trying to do a technical interview for a position that only a true SQL Server god could fill when all they really need is someone that knows T-SQL real well and they don't know what to ask the candidates.

I agree. Hence my thoughts we should be building auditing into the system at a low level, with hooks to let us ensure there are more reliable methods of tracking activity.