Using dbachecks for CIS Security Checks

Well back at the end of 2019 I finished writing most of the checks related to the CIS Center for Internet Security requirements.  I have yet to write a blog post on how to use them.  So, well here is how to go about using them, it’s mostly code so should be pretty simple to implement.  I’ve mentioned this several times over the past year in presenting on dbatools.

So first you need to have dbachecks.  So, let’s start with the basics just in case you haven’t heard of dbachecks.  dbachecks is PowerShell module that checks the configuration of your SQL Server against various test have been predefined.  By default, it exports the data to JSON, and we will be opening PowerBI to display the data because why that is pretty.  So, go download you a copy of Power BI from the Microsoft website and let’s install dbachecks first.

So, first we need to install Pester the testing framework PowerShell module. It’s very important that you get install this first and get the 4.10.1 version as the newest versions of Pester 5+ do not work with the current version of dbachecks and will be installed by default if you install dbachecks first.  Then you can install the dbachecks module.  Behind the scenes it also installs dbatools because it uses those commands to checks most of the items on SQL Server.

Install-Module Pester -Force -SkipPublisherCheck -RequiredVersion '4.10.1’
Install-Module dbachecks

Next, let’s talk about some particulars of the dbachecks commands.  First, I like to save my old configuration of dbachecks before I start messing around with setting particular security settings as these settings tend to be different than what you typically set.

# Save your configuration for pre dbachecks just in case you need to get it back 
$FolderPath = "C:Temp'  
Export-DbcConfig -Path "$($FolderPath)oldCIS.json"

Then I reset my config back to the default settings so I know I’m starting with the defaults.

# Then we are going to reset the config just in case, we should always do this when setting up something new
Reset-DbcConfig

Now every CIS item has the ability to decide rather you will adhere to it or not because of example CIS says Database Mail XPs should be turned off and I don’t know about you, but I like to get emails from my SQL Servers about what is going on.  So, you can set the following flip the following values to which every way you need to test them.  So, if you wanted Database Mail to be allowed you would change policy.security.databasemailenabled to $true.

Set-DbcConfig -Name skip.security.nonstandardport -Value $false 
Set-DbcConfig -Name policy.dacallowed -Value $false 
Set-DbcConfig -Name policy.errorlog.logcount -Value 12 
Set-DbcConfig -Name policy.security.oleautomationproceduresdisabled -Value $false 
Set-DbcConfig -Name policy.oleautomation -Value 0 
Set-DbcConfig -Name policy.security.adhocdistributedqueriesenabled -Value $false 
Set-DbcConfig -Name policy.security.clrenabled -Value $false 
Set-DbcConfig -Name policy.security.databasemailenabled -Value $false 
Set-DbcConfig -Name policy.security.xpcmdshelldisabled -Value $false 
Set-DbcConfig -Name skip.instance.defaulttrace -Value $false 
Set-DbcConfig -Name policy.security.latestbuild -Value $true 
Set-DbcConfig -Name skip.instance.oleautomationproceduresdisabled -Value $false 
Set-DbcConfig -Name policy.security.remoteaccessdisabled -Value $false 
Set-DbcConfig -Name policy.security.scanforstartupproceduresdisabled -Value $false 
Set-DbcConfig -Name skip.security.agentserviceadmin -Value $false 
Set-DbcConfig -Name skip.security.asymmetrickeysize -Value $false 
Set-DbcConfig -Name skip.security.builtinadmin -Value $false 
Set-DbcConfig -Name skip.security.clrassembliessafe -Value $false 
Set-DbcConfig -Name policy.security.containedbautoclose -Value $false S
Set-DbcConfig -Name skip.security.containedbautoclose -Value $false 
Set-DbcConfig -Name policy.security.databasemailenabled -Value $false 
Set-DbcConfig -Name policy.security.clrenabled -Value $false 
Set-DbcConfig -Name policy.security.crossdbownershipchaining -Value $false 
Set-DbcConfig -Name policy.security.databasemailenabled -Value $false 
Set-DbcConfig -Name policy.security.adhocdistributedqueriesenabled -Value $false 
Set-DbcConfig -Name policy.security.xpcmdshelldisabled -Value $false 
Set-DbcConfig -Name skip.security.ContainedDBSQLAuth -Value $false 
Set-DbcConfig -Name skip.security.engineserviceadmin -Value $false 
Set-DbcConfig -Name skip.security.fulltextserviceadmin -Value $false 
Set-DbcConfig -Name skip.security.guestuserconnect -Value $false 
Set-DbcConfig -Name skip.security.hideinstance -Value $false 
Set-DbcConfig -Name skip.security.localwindowsgroup -Value $false 
Set-DbcConfig -Name skip.security.loginauditlevelfailed -Value $false 
Set-DbcConfig -Name skip.security.loginauditlevelsuccessful -Value $false 
Set-DbcConfig -Name skip.security.LoginCheckPolicy -Value $false 
Set-DbcConfig -Name skip.security.LoginPasswordExpiration -Value $false 
Set-DbcConfig -Name skip.security.LoginMustChange -Value $false 
Set-DbcConfig -Name skip.security.sadisabled -Value $false 
Set-DbcConfig -Name skip.security.saexist -Value $false 
Set-DbcConfig -Name skip.security.sqlagentproxiesnopublicrole -Value $false 
Set-DbcConfig -Name skip.security.symmetrickeyencryptionlevel -Value $false 
Set-DbcConfig -Name skip.security.publicrolepermission -Value $false 
Set-DbcConfig -Name skip.security.serverprotocol -Value $false 
Set-DbcConfig -Name skip.security.SQLMailXPsDisabled -Value $false

Finally, we will save the config, run the checks, and start up Power BI.  After Power BI, loads hit the “Refresh” at the top to load your data.

# Save your configuration for dbachecks 
Export-DbcConfig -Path "$($FolderPath)CIS.json"
# Run CIS Checks Then Display Power BI Report 
$SQLInstance = @("dbatools2")
Invoke-DbcCheck -SqlInstance $SQLInstance –ComputerName $SQLInstance -Tag CIS -Show Summary -PassThru | Update-DbcPowerBiDataSource
Start-DbcPowerBi

Example report:

Instead of setting config values you can opt to exclude tests all together this will make your scans go faster but will make them not show at all on your reports.  To get a list the ones you can exclude you can run the following command:

Get-DbcCheck -Tag CIS

For example, you can exclude the Database Mail test by running the exact same scan as above by running the below code and including the ExcludeChecks parameter.  For multiple, test to be excluded just separate them by commas.

$SQLInstance = @("dbatools2") Invoke-DbcCheck -SqlInstance $SQLInstance –ComputerName $SQLInstance -Tag CIS -ExcludeChecks DatabaseMailEnabled -Show Summary -PassThru | Update-DbcPowerBiDataSource Start-DbcPowerBi

Complete code for setting this up is below minus installing the modules.

Import-Module dbachecks
# Save your configuration for pre dbachecks just in case you need to get it back
$FolderPath = "C:temp" 
Export-DbcConfig -Path "$($FolderPath)oldCIS.json"
# Then we are going to reset the config just in case, we should always do this when setting up something new
Reset-DbcConfig
# The tricky part, populate the sqlintance and computername with the values you need, if you use named instances remember the computer names will be different.  This can be an array so you can read from a text file or a table, there are a number of ways to do this, so I can't quite give examples for this one.
$SQLInstance = @("sql1", "sql2")
Set-DbcConfig -Name app.sqlinstance -Value $SqlInstance
Set-DbcConfig -Name skip.security.nonstandardport -Value $false
Set-DbcConfig -Name policy.dacallowed -Value $false
Set-DbcConfig -Name policy.errorlog.logcount -Value 12
Set-DbcConfig -Name policy.security.oleautomationproceduresdisabled -Value $false
Set-DbcConfig -Name policy.oleautomation -Value 0
Set-DbcConfig -Name policy.security.adhocdistributedqueriesenabled -Value $false
Set-DbcConfig -Name policy.security.clrenabled -Value $false
Set-DbcConfig -Name policy.security.databasemailenabled -Value $false
Set-DbcConfig -Name policy.security.xpcmdshelldisabled -Value $false
Set-DbcConfig -Name skip.instance.defaulttrace -Value $false
Set-DbcConfig -Name policy.security.latestbuild -Value $true
Set-DbcConfig -Name skip.instance.oleautomationproceduresdisabled -Value $false
Set-DbcConfig -Name policy.security.remoteaccessdisabled -Value $false
Set-DbcConfig -Name policy.security.scanforstartupproceduresdisabled -Value $false
Set-DbcConfig -Name skip.security.agentserviceadmin -Value $false
Set-DbcConfig -Name skip.security.asymmetrickeysize -Value $false
Set-DbcConfig -Name skip.security.builtinadmin -Value $false
Set-DbcConfig -Name skip.security.clrassembliessafe -Value $false
Set-DbcConfig -Name policy.security.containedbautoclose -Value $false
Set-DbcConfig -Name skip.security.containedbautoclose -Value $false
Set-DbcConfig -Name policy.security.databasemailenabled -Value $false
Set-DbcConfig -Name policy.security.clrenabled -Value $false
Set-DbcConfig -Name policy.security.crossdbownershipchaining -Value $false
Set-DbcConfig -Name policy.security.databasemailenabled -Value $false
Set-DbcConfig -Name policy.security.adhocdistributedqueriesenabled -Value $false
Set-DbcConfig -Name policy.security.xpcmdshelldisabled -Value $false
Set-DbcConfig -Name skip.security.ContainedDBSQLAuth -Value $false
Set-DbcConfig -Name skip.security.engineserviceadmin -Value $false
Set-DbcConfig -Name skip.security.fulltextserviceadmin -Value $false
Set-DbcConfig -Name skip.security.guestuserconnect -Value $false
Set-DbcConfig -Name skip.security.hideinstance -Value $false
Set-DbcConfig -Name skip.security.localwindowsgroup -Value $false
Set-DbcConfig -Name skip.security.loginauditlevelfailed -Value $false
Set-DbcConfig -Name skip.security.loginauditlevelsuccessful -Value $false
Set-DbcConfig -Name skip.security.LoginCheckPolicy -Value $false
Set-DbcConfig -Name skip.security.LoginPasswordExpiration -Value $false
Set-DbcConfig -Name skip.security.LoginMustChange -Value $false
Set-DbcConfig -Name skip.security.sadisabled -Value $false
Set-DbcConfig -Name skip.security.saexist -Value $false
Set-DbcConfig -Name skip.security.sqlagentproxiesnopublicrole -Value $false
Set-DbcConfig -Name skip.security.symmetrickeyencryptionlevel -Value $false
Set-DbcConfig -Name skip.security.publicrolepermission -Value $false
Set-DbcConfig -Name skip.security.serverprotocol -Value $false
Set-DbcConfig -Name skip.security.SQLMailXPsDisabled -Value $false
# Save your configuration for dbachecks
Export-DbcConfig -Path "$($FolderPath)CIS.json"
# Run CIS Checks Then Display Power BI Report
$SQLInstance = @("dbatools2")
Invoke-DbcCheck -SqlInstance $SQLInstance –ComputerName $SQLInstance -Tag CIS -Show Summary -PassThru | Update-DbcPowerBiDataSource
Start-DbcPowerBi

The post Using dbachecks for CIS Security Checks first appeared on Tracy Boggiano's Blog.