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 DoD STIG for SQL Server is a great, solid starting point for establishing your security practices with SQL Server. In fact, it’s also a good template for your own STIG in your organization. So, you may want to create a custom checklist that makes sense from the perspective of your SQL Server environment.

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.

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?

So in this post, I’ll walk through how I’m building the checklist, based on the DoD STIG as the baseline (and I can then organize 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 and then print it to a pdf file).

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:

Category	What you’re checking
Authentication	How users authenticate
Authorization	Who has access to what
Accounts & Logins	SQL logins, Windows accounts, disabled accounts
Privileges	sysadmin, server roles, database roles
Auditing	Login and security-related auditing
Encryption	TDE, TLS, encryption at rest
Network Security	SQL ports, protocols, exposure
Configuration	SQL Server security-related configuration
Database Security	Database-level permissions and ownership
Service Accounts	SQL Server service identities
Agent Security	SQL Agent jobs, proxies, credentials
Sensitive Data	PII and other protected information
Monitoring	Security events and changes
Documentation	Exceptions, evidence and remediation

That and among other things that may be applicable to whichever environment I happen to be working on.

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. Involve everyone and, needless to say, document.

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!