3

I'm working on a request to start logging some additional data for PCI compliance. I need to log who, when, and what changed on a set of tables. I set up SQL auditing which would work fine except for the fact that the values of what is being changed is coming through as @1, @2 etc instead of the actual data. What are my other options for logging the needed information? CDC does not track who made the change.

This is currently running on SQL Server 2012 but a possible upgrade to 2014 is possible if that would provide a better solution.

Thanks, Tim

Improve this question
2
  • If you put a lastupdatedby and lastupdatedate column (supported by triggers) into the table & then used CDC would that not work?
    – Kenneth Fisher
    Commented Jan 14, 2015 at 22:30
  • 1
    @Kenneth Still won't provide who - so even if you're going to have triggers capture the names/times and CDC capture the changes, it's still going to be difficult to correlate them on a busy system. The trigger may as well log the changes to the data, too - and it can be pretty smart about not logging changes that weren't really changes. CDC wasn't really intended to facilitate auditing.
    – Aaron Bertrand
    Commented Jan 14, 2015 at 22:39

1 Answer 1

Reset to default
5

I would probably go with triggers. You will be warned about performance implications and all that, but truthfully there isn't a "free" way to audit, and any option you try will have some impact on your workload. You will need to be aware that if someone updates the whole table, you will be logging that too, so be prepared for your auditing data growth to potentially outpace your actual data. Might want a separate database on isolated storage for that.

Auditing, CDC and Change Tracking would be great if they were combined, but they're not. As you've discovered, auditing shows parameter placeholders, so it tells you who changed it but not what they changed, and the other two tell you what has changed, but not who changed it or when.

I've asked for the ability to include username and date/time in CDC data, but it was declined:

http://connect.microsoft.com/SQLServer/feedback/details/283707/cdc-options-to-capture-more-data-username-date-time-etc

I also asked for date/time information to be included in Change Tracking, but similarly it was declined:

http://connect.microsoft.com/SQLServer/feedback/details/319049/change-tracking-provide-datetime-column

I wasn't aware of the auditing limitation. If I had known about it, I would have asked for it, too. And it would have been declined like the others.

Improve this answer
1
  • I am not sure that PCI compliance and the raw data stored or displayed as a raw data goes together. This could be considered as a potential treat and I think in case of collecting the raw data, some heavy encryption to protect these data would be required. Everything else written here by Arron states But PCI (unlike SOX) doesn't require collecting the data AFAIK. Even it is the case, we are talking about the static/sensitive data that are not prone to frequent changes. Havingthat in mind, using triggers to audit data changes should not create overhead to your system, at least not significant
    – NikolaD
    Commented Jun 22, 2015 at 8:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.