Chris Bell of Water Ox Consulting has recently released sp_WOXCompliant, a tool which helps you check your instances for compliance. His first goal is to get STIG tests in place.
I ran it against a local instance I use in a VM. I haven’t put much effort into securing this instance for various reasons, so I wasn’t surprised that I ended up with 111 results.
These tests cover a range of scenarios. In the picture above, you can see that my data files are located on the C drive, that I have AdventureWorks installed, that I have not disabled the VIEW ANY DATABASE role, and that I still have an sa account, although it is disabled.
Other checks that I failed include:
- Databases are not encrypted using TDE.
- I am auditing failed logins only, and not successful logins.
- Attribute names are shared across entities but the data types are not the same (for example, two tables have columns named Name, but one is a varchar(30) and the other a varchar(50)).
- No asymmetric keys are available for encryption.
- SSIS, VSS, the SQL Server Browser, etc. are installed but may not be required.
- Certain SQL trace flags for auditing are missing, and I’m not using the equivalent Extended Events.
- My SQL Server installation is out of date; there are new updates.
All in all, this is a very interesting tool to run against environments, and as updates come in, the tool will get more valuable. Some of the findings are more theoretical (like services which are running) because the procedure has no way of telling if your documentation is up to date or if you really should be running those services, so you’ll never have 0 entries in the set. What we do get, however, is a solid checklist for things to look at, and over time, I expect this procedure to be one of my go-to installation tools on instances I manage.