SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Roy Ernest / What, When and who? Auditing 101 / Latest Posts

RE: What, When and who? Auditing 101

Jeff Moden — Mon, 05 Dec 2011 19:56:40 GMT

I'll definitely wait for the other articles from Roy but, right now, I'm thinking I'll still with some good ol' fashioned, high performance audit triggers.

RE: What, When and who? Auditing 101

codebyo — Mon, 05 Dec 2011 07:54:15 GMT

You can have WHO and WHEN available to you using CT with the CHANGE_TRACKING_CONTEXT method posted in one of my last posts. Storing WHAT changed would be limited by the size of the VARBINARY(128) column so it wouldn't be a good solution.CT is a lightweight solution available to all editions and it can be quite useful in situations where too much detail isn't needed.Best regards,

RE: What, When and who? Auditing 101

Roy Ernest — Mon, 05 Dec 2011 05:23:51 GMT

That is true. From what I have seen, we have to use at least two of the new Auditing technology together to get the "Audit" to work. SQL Audit actually does audit Who and when but not what. :-D

RE: What, When and who? Auditing 101

Jeff Moden — Mon, 05 Dec 2011 05:16:10 GMT

Kind of, Roy. It's not actually negatives with CT... I'd be more likely to call it a lack of positives. CT doesn't appear to actually audit, in my feeble ol' mind. If I understand the article correctly, it only keeps one historical row back.Like I said... good article. The fact that CT doesn't appear to do what I think of as "auditing" has no bearing on the fact that this is an excellent article about what CT does and doesn't do. Thanks for writing it. :-)

RE: What, When and who? Auditing 101

Roy Ernest — Mon, 05 Dec 2011 05:09:08 GMT

Hey Jeff,Thanks Jeff. The second part will come out hopefully soon. Are there too many negatives on CT for your company to use CT? :-D

RE: What, When and who? Auditing 101

Jeff Moden — Fri, 02 Dec 2011 21:09:22 GMT

[quote][b]Roy Ernest (11/29/2011)[/b][hr]Thanks David for the compliment. To agree to the definition of an Audit is difficult. Everyone has their own take on it. Your definition is easier to understand as well. Thanks for sharing it. :-)[/quote]Heh... I've found that a lot of people define "Audit" as "Keeping historical data so we know who to blame when something goes wrong." :-DNicely done on the article. Good order of revelation with nice, simple examples. Well done, Roy.As a side bar, I'm going to use your article to justify why we don't actually want to use CT when we finally get to 2008 at the new company I'm working at. :-) Of course, if they wait a bit longer, we can jump to 2k12.

RE: What, When and who? Auditing 101

Jeff Moden — Fri, 02 Dec 2011 21:06:57 GMT

[quote][b]tymberwyld (11/28/2011)[/b][hr]When SQL 2005 came out, I cringed at the way SQL Notification services worked and basically threw it out the window as an option for change tracking. However, I re-tooled the same concept using SQL Service Broker, and using a simple, generic Trigger, was able to audit ANY table's changes (real-time, with both previous and new values) asynchronously and we were able to audit all databases into one or many audit databases if we wished. Views were created against the meta-data to make "pseudo" tables which resembled the originals, so comparisons could be made. Anyway, it's worked for years and was completely scalable. If we wanted to move the auditing database(s) to another server, it was as easy as "changing the channel" on the service broker, no messy Trigger or Proc changes.[/quote]That would make one heck of a nice article. Any chance of you taking that on?

RE: What, When and who? Auditing 101

Roy Ernest — Tue, 29 Nov 2011 08:55:39 GMT

Thanks Zero one. :-)Auditing in SQL Server is still in infancy state. It will take couple of more releases to get all the required information for auditing I guess.

RE: What, When and who? Auditing 101

Zero One — Tue, 29 Nov 2011 08:42:30 GMT

Good article, thanks for the summary.The biggest issues I've encountered, save for a server side trace, is catching the originating hostname for the incoming connection. To me this is a critical aspect of "who" and without it, it can be difficult in tracking down an individual in the situation where a common SQL login is utilized by multiple individuals (or even a service account implemented on several servers). In this, SQL Server Audit is woefully lacking. Sure we *wish* we could always avoid folks using a SQL login but life is rarely perfect especially when you've inherited someone else's mess to straighten out. To me this is the whole point of auditing is catching non-standard activity but without capturing *correct* hostname you can't easily complete the picture of *who* (all hostname captured information in SQL Audit is the actual host SQL server or blank and not the host of the originating connection).This of course is Microsoft's issue to resolve, not the author's.

RE: What, When and who? Auditing 101

Roy Ernest — Tue, 29 Nov 2011 06:11:07 GMT

[quote][b]Evil Kraig F (11/28/2011)[/b][hr]Roy, an excellent walkthrough on the mechanics of something I've basically avoided. You highlighted its difficulties and its uses quite well.Thanks for the article.[/quote]Thanks Craig. When I look back, I realized that I could have added a bit more in depth information.

RE: What, When and who? Auditing 101

Roy Ernest — Tue, 29 Nov 2011 06:09:48 GMT

Thanks David for the compliment. To agree to the definition of an Audit is difficult. Everyone has their own take on it. Your definition is easier to understand as well. Thanks for sharing it. :-)

RE: What, When and who? Auditing 101

Roy Ernest — Tue, 29 Nov 2011 06:02:55 GMT

[quote][b]codebyo (11/28/2011)[/b][hr]Just one small correction: ALLOW_SNAPSHOT_ISOLATION is not needed for Change Tracking but it's the probably the safer way to use it with.Best regards,[/quote]Andre, what you stated here is true. You do NOT need SNAPSHOT ISOLATION on but if you do not, you will need to do some extra work to make sure that all requirements are met. You can read [url=http://msdn.microsoft.com/en-us/library/cc280358.aspx]here[/url] to get more details regarding how it should be done without snapshot isolation.EDIT : Regarding CHANGE_TRACKING_CONTEXT, it will work fine if all data changes are done only using applications. Then you have control of it and you get more context of the data change. It is a good info to pass to everyone. Thanks. I missed that in the article.

RE: What, When and who? Auditing 101

tymberwyld — Mon, 28 Nov 2011 18:58:29 GMT

When SQL 2005 came out, I cringed at the way SQL Notification services worked and basically threw it out the window as an option for change tracking. However, I re-tooled the same concept using SQL Service Broker, and using a simple, generic Trigger, was able to audit ANY table's changes (real-time, with both previous and new values) asynchronously and we were able to audit all databases into one or many audit databases if we wished. Views were created against the meta-data to make "pseudo" tables which resembled the originals, so comparisons could be made. Anyway, it's worked for years and was completely scalable. If we wanted to move the auditing database(s) to another server, it was as easy as "changing the channel" on the service broker, no messy Trigger or Proc changes.

RE: What, When and who? Auditing 101

codebyo — Mon, 28 Nov 2011 16:08:10 GMT

Just one small correction: ALLOW_SNAPSHOT_ISOLATION is not needed for Change Tracking but it's the probably the safer way to use it with.Best regards,

RE: What, When and who? Auditing 101

David In BC — Mon, 28 Nov 2011 15:45:31 GMT

I am not sure I agree with,"Data auditing is the process of doing a profile check and assessing the quality of data, to find how accurate it is. This can be achieved by keeping track of all the data changes."A couple of points:- you can't always know that data is correct. If the data is based on mathematical formula, and you have the inputs, you can. But you can't tell that my mobile telephone number is correct, or my date of birth. At least not without external validation. Quality is abstract.- auditing goes beyond changing data. Tracking the viewing of data, or the printing of data, is very important. Examples can include celebrities who are hospitalized - you want to know if 300 hospital workers are viewing the patients chart (and shouldn't be).I like this definition better: "[a] systematic examination of information use, resources and flows, with a verification by reference to both people and existing documents, in order to establish the extent to which they are contributing to an organisation’s objectives"But interesting article.David

RE: What, When and who? Auditing 101

codebyo — Mon, 28 Nov 2011 14:54:08 GMT

Excelent article, Roy.I'm thinking that one of the ideal business solutions to use CT is for database synchronization from mobile devices or off-site databases.To help CT become more "auditable", maybe we could use the syntax:[code="sql"]WITH CHANGE_TRACKING_CONTEXT(@context)--<INSERT or UPDATE command here>[/code]and send some information about the username or changes inside the @context VARBINARY(128) column. Is there a limitation for using the CT context and would it also become a major pain to maintain like described in the article?Change Tracking comes with all SQL Server editions so it's a great little feature to have at our disposal. :-)Best regards,

RE: What, When and who? Auditing 101

Evil Kraig F — Mon, 28 Nov 2011 12:43:11 GMT

Roy, an excellent walkthrough on the mechanics of something I've basically avoided. You highlighted its difficulties and its uses quite well.Thanks for the article.

RE: What, When and who? Auditing 101

Roy Ernest — Mon, 28 Nov 2011 11:01:22 GMT

Thx everyone. It was fun writing this series. I need to finish the SQL Audit in the coming week so that I can send it to you guys for review before I submit. I have already submitted CDC and I am waiting for it to be reviewed.Andre, What I saw in CT is that it is using the old Tran replication technology to get the job done. For instance, if you add a column you have to do so many things to get the CT to track the changes just like replication of SQL 2000.

RE: What, When and who? Auditing 101

bitbucket-25253 — Mon, 28 Nov 2011 10:29:19 GMT

Nice article Roy ... now waiting for the follow up article ..

RE: What, When and who? Auditing 101

ALZDBA — Mon, 28 Nov 2011 10:18:04 GMT

Great job, Roy.It's been my pleasure to help out.:smooooth:

RE: What, When and who? Auditing 101

andre.quitta — Mon, 28 Nov 2011 09:57:36 GMT

The article was thorough and straighforward.I find the problem with ChangeTracking and ChangeDataCapture is that there is so much hardcoding going on. That means whenever you add or delete a column, it is another area that needs to be touched. Plus the queries are pretty unwieldly and verbose.There is a need in my group to send out an email when anything changes among multiple tables. That would be a very long query, and pretty unwieldy.I have a lot going on with dynamic SQL to do that, but there is a performance hit.

RE: What, When and who? Auditing 101

SQLRNNR — Mon, 28 Nov 2011 09:40:39 GMT

Nice job Roy.

What, When and who? Auditing 101

Roy Ernest — Sun, 27 Nov 2011 01:40:27 GMT

Comments posted to this topic are about the item [B]What, When and who? Auditing 101[/B]