| Author |
Topic |
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
 Posted - 2007-09-26 : 14:51:04
|
| I have been given a requirement to audit access to particular databases. In other words, we need to log anyone making a connection to this database.Does anyone know how to do this in SQL Server 2000, with or without a third-part tool?Does anyone know how to do this in SQL Server 2005?CODO ERGO SUM |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-09-26 : 14:52:35
|
| SQL Server 2005 provides login auditing. The info would be in the SQL Server Error Log and therefore the Application Log in Event Viewer. I believe in 2000 though that you have to do something that has C2 in it. I just can't remember the details.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2007-09-26 : 14:55:39
|
| You say anyone. What if there is an app account that access the db and is used by someone? Do you just need to know the app account or the actual user? I wouldn't know how to tell the actual user. You can set you auditing to full or whatever and all connections (success or failure) will be caught in the error log. |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2007-09-26 : 14:59:29
|
| FYI, I've been asked in the past about tracking everyone for every database..who they are, what changes they made, what time, ect...adn even though the app uses an app account, they want to know the specific user doing the data change. They want this to be handled at the database level...no extra code or app modification to be written. And if it was a Prod DBA that made the change, they want to also know how to automatically tie that data change back to a work order request (and none of the data tables have any type of a column to track a work order that may have been the reason for the data update). Management thinks you can just flip a switch and make all that happen. |
|
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-09-26 : 15:25:26
|
| Has anyone heard of a third-party tool that can do any of this?Are there any third-party tools that can read the C2 audit logs, and extract useful information?Has anyone ever implemented C2 auditing? It sounds like you would be drinking audit info from a firehose.CODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-26 : 15:27:06
|
"and none of the data tables have any type of a column to track a work order that may have been the reason for the data update"ROFTL. That figures ...  We have triggers [on all tables] that store Update User and Update Date, and on most tables store the "deleted" data into a parallel Archive table. I appreciate that this is pretty Hum-Drum, but it might be a start in the direction you a re being asked to go, and can be set up relatively easily.Good luck with the Work Order tracking if there is No Field available, Nothing in the application to capture it and Its one-more-thing-for-the-operator-to-have-to-kowtow-to  Kristen |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2007-09-26 : 15:36:21
|
| When I said at the database level, I think I may have said the wrong thing. I meant they wanted some third party tool that you could buy or write seperate from the application to do it all. The only thing I was coming up with was to have profiler traces running 24/7 capturing certain event types for certain types of info required. It would have taken about 15 or so traces runing 24/7 and populating sql tables on another server. That's the closest I could come up with but still didn't have everything they wanted. I had talked to some other dba's back then and they mentioned products like Lumigent Audit DB and Idera Compliance Manager but I never researched either of those. They wanted to know...ok, who modified this field in this table and at what time did it happen? There were modbylogin and moddate fields, but it's possible that it got passed by if a prod dba did the update...or the app/SP wasn't coded to update those fields. So they wanted some outside way to track everything possible. |
|
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-09-26 : 15:39:42
|
The article I linked to mentions using Server Side Tracing in order to obtain a more granular audit. Is there any reason why you couldn't implement that? You are all far more knowledgeable than I, so feel free to slap me if I am way off base =)
Future guru in the making. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-09-26 : 15:46:56
|
quote:
Originally posted by Zoroaster
The article I linked to mentions using Server Side Tracing in order to obtain a more granular audit. Is there any reason why you couldn't implement that? You are all far more knowledgeable than I, so feel free to slap me if I am way off base =)
Future guru in the making.
I'm sure anything can be done, but I am really looking for a solution that has been done by someone and worked without being a lot of trouble. I'm not anxious to be the first one to have done something like this, and would really like to bypass those first-time mistakes. CODO ERGO SUM |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-09-26 : 22:26:46
|
| For third party tools, check Lumigent's Audit DB. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-09-27 : 11:56:34
|
quote:
Originally posted by rmiao
For third party tools, check Lumigent's Audit DB.
Does anyone have any experience using Lumigent Audit DB?CODO ERGO SUM |
|
|
|
|
Log Database Access