I’m going to start the blogging back up with a topic that is near and dear to my heart, and something that has bugged me for years.

Every time I work with SQL auditing, especially in environments governed by DISA STIG requirements, I’m reminded how misunderstood, misconfigured, and frankly incomplete everyone leaves their auditing process.

Audits matter. A lot. They are one of the few artifacts that let you trace what actually happened on a SQL Server instance: the who, what, when, where, and how. When something breaks, or something suspicious happens, the audit is where you go to reconstruct the truth, or at least I’d like to say that.

But who actually digs into them? They are a huge pain to deal with.

Audit are normally just a firehose of noise. Unfiltered audits capture everything, including internal system operations SQL Server does behind the scenes thousands of times a minute. If you’ve ever tried to load a 20GB audit file in SSMS and waited long enough to rethink your life choices, you understand. First of all, letting a single audit file get that huge is a huge mistake, but there are so many reasons it might happen.

Filters: Important, Necessary… and Rarely Understood

If you run a DISA STIG compliant audit, you are capturing over 30 audit action items which translates to hundreds of gigabytes of audit data in a day, easily. The irony is that while filters are essential to reducing audit volume to something manageable, almost no one has tried to add a filter, and even fewer actually understand how to create them. And honestly, I can’t blame them. Audit filters are confusing and poorly documented.

To make it even worse, a lot of organizations think “filters = less auditing = bad,” so they leave everything on. All that does is ensure no one ever reviews the audit, because no one has time to wade through millions of rows of system chatter. Combine that with the dreaded action item SCHEMA_OBJECT_ACCESS_GROUP and you are looking at more data than you can shake a stick at.

Which leads me to the part that drives me crazy.

The STIG Problem: Required to Log It… but Never Required to Review It

If you work with DISA SQL Server STIGs, you already know how much of the checklist focuses on required audit actions. Historically dozens of STIGIDs cover dozens of audit action items.

But not a single STIGID actually requires proof of review.

• We enforce that the audit must exist.
• We enforce that certain actions must be captured.
• We enforce retention (kind of), location, configuration, offloading…

…but nowhere do we enforce that the organization has to actually look at the audit on a scheduled basis. We do at least enforce testing backups.

I’ve seen organizations that never look at their audits. They just lock them away and hope the files don’t eat up too much storage space. Sometimes I was successful in teaching them how important those audit files could be, sometimes I wasn’t.

I’d love to see a STIGID that requires scheduled, documented review of the audit logs. What’s the point of collecting all this data if it only gets looked at after an incident? The problem is, I was the main person advising DISA on STIG changes and improvements for the last few years. There are other colleagues still at Microsoft who could champion this cause, but I don’t think they have the time any more.

SQL Audits Are Slow — Painfully Slow

Even if someone wanted to review the audit regularly, SQL Server doesn’t make it easy. Audits grow large quickly, and the built-in functions for reading audit files are notoriously slow.

You can easily hit scenarios where:

• A single audit file takes minutes to open
• A month of audit history takes hours to load
• “Daily review” becomes impossible simply due to the cost of reading the files

This is another reason filters are so important.

The 2016 Filter Mistake (and Why It Still Haunts Us)

One of the biggest audit-filtering issues came from the SQL Server 2016 STIG. The wording was wrong on the STIGID itself. It told administrators not to filter “administrative permissions.” But that was not what the underlying SRG says.

The SRG actually says you cannot filter out “direct database access”.

I corrected this mistake in the 2022 SQL STIG, so newer environments are finally getting the right guidance in the Check Content. The 2016 STIG still has the incorrect language, and now that I’m no longer at Microsoft, I don’t know if it will ever be fixed. Since the 2016 STIG should be sunset soon, it may not be a problem much longer, but it still haunts me.

The worst part is that even the SQL Server product team didn’t want to touch the filter question. The phrase “administrative activities” was so vague that no one would explicitly approve or deny a filter as STIG-compliant when I asked. I couldn’t get a straight answer internally so that I could provide a STIG compliant audit filter in the Fix Text of the audit creation STIGIDs.

The filter was long and complicated. I’ll spare you the full wall of code in this blog, but imagine dozens of NOT clauses filtering sys tables that you probably have never heard of or want to hear of.

Even with explanations, few felt comfortable deploying the audit filter. It was too long and too confusing to read.

Where Do We Go From Here?

There’s no perfect answer here. A unified STIG for SQL Server, not broken out by version, is my dream. This would keep changes more up to date and easier to maintain. A magically faster audit reading solution would also be ideal. I’ve helped build and maintain an automated Audit Data Warehouse in the past, but it was never widely adopted because even trying to summarize the audit data for regular review took massive amounts of storage space. What you can do right now though?

• Use audit filters, but test them thoroughly and document their intent
• Separate system noise from user-driven events if you can
• Keep audit files small enough to review regularly, many small files are easier to read than few huge files
• Define a documented review schedule (even if the STIG doesn’t require it), use those audits for what they are intended
• Push for clearer guidance in future STIG cycles. You can request changes from DISA too, they do listen to customers, they just vet those changes through the vendor and their own SMEs before a change cycle (which is about 6 months).

I plan to continue working with the SQL STIGs even from outside Microsoft. Security is important to me, and having that be consistent and actionable is paramount.