Creating a Custom SQL Server Security Checklist Using the DoD STIG

Here’s a follow up for our US Department of Defense STIG document. In my previous post, SQL Server Security Hardening Guide Using the DoD STIG Checklist, I walked through how I used the DoD STIG checklist as a starting point for reviewing and hardening a SQL Server environment.

After going through the checklist, I started thinking about what I would actually want to use the next time I perform a security review.

The STIG is detailed, which is a good thing, but I found myself wanting something a little more practical for day-to-day DBA work. Something I could open, work through one item at a time, record what I found, and come back to later without having to navigate through the entire STIG document every time.

That led me to the idea of creating my own SQL Server security checklist.

The goal isn’t to replace the STIG or create another security standard. Instead, I want to take what I learned from the STIG and turn it into a checklist that makes sense from a SQL Server DBA’s perspective.

Questions to Ask

I want it to answer some simple questions:

• What am I checking?
• Why am I checking it?
• How can I verify it?
• What does PASS or FAIL look like?
• What evidence should I record?
• Does this requirement actually apply to this environment?
• If I can’t meet a requirement, how do I document the exception?

I’m also going to include some checks that may not come directly from the STIG but are things I would normally want to look at during a SQL Server security review.

So in this post, I’ll walk through how I’m building the checklist, starting with the STIG as the baseline and then organizing it into something I can actually use as a DBA.

The main thing here is to understand what the STIG is asking you to verify.

Once I have the STIG requirements in front of me, the next step is to break each rule down into information that I can actually work with. Rather than simply copying the requirement into my checklist, I want to capture a few key details for each rule.

This gives me enough information to understand what I’m checking, how I can validate it, and what I should record when I’m done.

Again, this STIG is just a starting point. Capture the checklist in a separate document that you can share in your organization.

What to Capture?

For every rule, capture:

• STIG ID
• Severity
• Requirement
• Check
• Fix
• Instance or Database
• Applicable?
• Current status

Let’s Create a Checklist

Let’s open the STIG Viewer, at the Checklist section, click New to create a new checklist.

Let’s select to create a checklist for a SQL Server Instance.

You have to options: Add individual rules or add all the rules in the STIG. Click the search icon to pick the individual rules or the plus sign to add all the rules. For the purpose of this blog post, let’s add few rules.

Select the rule by clicking the Plus sign. Select all that you want added in your checklist.

To save the checklist, navigate to the burger menu in the top left-hand corner and select Save. Give the checklist file an intuitive name.

Once saved, you can close the checklist builder. The STIG Checklist options will then give you a couple of options to load or create another checklist. Let’s open the checklist that we just created.

Review the checklist and check it against a specific instance or database that you want to assess. Here you can note the instance/database information and mark each rule with three statuses:

Green Check Icon: Not a Finding (meaning instance/database pass the rule),

Red Exclamation: Open (for further review), or

Crossed Circle: Not Applicable

Default Grey Circle: Not reviewed

Keep clicking the gray button to cycle through the statuses.

You can also leave a comment and findings details in each of the rules in the designated boxes at the bottom.

You can then export the checklist if you want to keep a separate file for the assessment (html)

Here’s some screenshots of the the example html…

Your Checklist as Your Tool

Instead of reproducing the STIG’s structure, I’d create something a DBA can work through. I’d create a checklist for the following categories:

This is where your checklist becomes your own tool, rather than just another copy of the STIG.

Most Important Thing, Document

This is an important part of the checklist. I don’t want the process to be simply about finding a problem and immediately changing the configuration.

The goal is to first understand the current state, evaluate whether the setting actually needs to be changed, make the appropriate remediation if necessary, and then validate the change. This gives me a more deliberate process instead of treating every finding as something that automatically requires a configuration change.

This process would look as simple as the following:

1. Check – Determine the current state.

2. Evaluate – Decide whether the configuration is appropriate for your environment.

3. Remediate – Make the change if necessary.

4. Validate – Run the check again.

5. Document – Record what changed.

That gives you a repeatable process:

       CHECK
         ?
      EVALUATE
         ?
     REMEDIATE
         ?
      VALIDATE
         ?
     DOCUMENT

And I’d emphasize that STIG compliance doesn’t automatically mean every setting should simply be changed without considering the environment.

If this was a real checklist for a SQL Server environment I am responsible for, this is my starting point. I expect the checklist to change as I use it, find gaps, and learn more about what works in an actual SQL Server environment.

The STIG gives me a solid baseline, but turning those requirements into something I can consistently use as a DBA makes the exercise much more useful for me.

My next step is to take these checks and start turning them into T-SQL scripts where possible, so instead of manually checking everything, I can let SQL Server help me identify what needs attention.

Have fun and enjoy securing your SQL Server!

The post Creating a Custom SQL Server Security Checklist Using the DoD STIG first appeared on SQL, Code, Coffee, Etc..