SQL Server Auditing and Compliance

Joey — Wed, 23 Apr 2008 07:01:00 GMT

A hot topic for the IT Pro is compliance. Government and other regulation compliance challenges exist all over the IT infrastructure. I had a chance to sit down with JC Cannon in the SQL product group to discuss how SQL Server 2008 addresses compliance and auditing in SQL Server 2008, as well as how new tools such as Policy Based Management can help in the IT Pro's mission to achieve compliance.

Windows Server 2008 - Active Directory Auditing Enhancements

extreme — Thu, 29 Nov 2007 01:00:00 GMT

I hope this post will act as a good reference point to be able to quickly understand the good and bad about new AD auditing enhancements and then enable you to dive deeper at will using the links in this article.

There’s nothing more exciting than auditing right? Well, check this out and hopefully it will spark some interest.

In Windows Server 2003 R2 and prior, the auditing of active directory certainly has not been a strong point. You would enable or disable global AD auditing for success or failures, set a SACL on the objects you wanted to monitor, and then typically one or both of the following would happen:

Your security event log fills up with way more security events than you’d ever hoped for, possibly wrapping or ballooning the size of the security log.
Auditing doesn’t actually provide enough information for you to make any use of the events which are recorded in the security event log. i.e. it only says who was successful at modifying the object, but nothing on the details of the value(s) which were changed.

In Server 2008, we are on a good path to fix this pain. Some of the key improvements to AD auditing are as follows:

You can limit the number of attributes which are audited for object types. For instance, you only want to know if the Employee’s Pay Level attribute is modified for all user accounts and nothing else.
Auditing is now broken into four categories: Access (same as 2000/2003), Changes, Replication, and Detailed Replication. The most interesting come from the new changes category:
- AD DS logs the previous and current values of the attribute. If the attribute has more than one value, only the values that change as a result of the modify operation are logged.
- If a new object is created, values of the attributes that are populated at the time of creation are logged.
- If an object is moved, the previous and new location (distinguished name) is logged for moves within the domain. When an object is moved to a different domain, a create event is generated on the domain controller in the target domain.
- If an object is undeleted, the location where the object is moved to is logged.

What are the downfalls?

You have to modify the schema in order to limit the number of attributes which are audited per object type. This isn’t really difficult, but it would be nice if there were some friendlier type way to do it.
You cannot view or modify the audit policy subcategories with the Local Group Policy Editor (GPedit.msc). You can only do this with the command-line tool Auditpol.exe.
As far as I can tell, you can’t limit auditing to different specific attributes for a subset of the same type of object. For instance, you would like to audit attributes X, Y, Z for all admin user accounts, but only attribute X for all regular user accounts. Of course you have some control over this with your SACLs…

Get Started:

A screencast on How to enable granular AD auditing in WS08 (coming in the future from me)
Windows Server 2008 Auditing AD DS Changes Step-by-Step Guide
TechNet - AD DS: Auditing
Windows Networking Site AD enhancements overview
MS Directory Services Team Blog Post on WS08 Auditing Enhancements

Entries tagged with auditing - TechNet Edge

SQL Server Auditing and Compliance

Windows Server 2008 - Active Directory Auditing Enhancements