The mssql_secured module is the Puppet implementation of the Center for Internet Security (CIS) benchmark for for MS SQL databases. This module will help you:

  • Sleep better at night knowing your databases are more secure.
  • Feel confident that you’re doing everything possible to protect your databases.
  • Save time on MS SQL database security management.
  • Automate the application of security best practices.
  • Easily upgrade to new versions and stay up to date with the latest security recommendations.

We now support the current versions:

See here for a way to get started with the mssql_secured module.

Design goals

The design goals for the mssql_secured module where:

  • Easy to apply
  • Easy to skip controls that you don’t want or need
  • Easy to change values that are variable within the CIS benchmark or STIG document
  • Easy to exclude some objects from the control
  • Correlate between the changes Puppet applies and the CIS document or STIG document.
  • Easy to upgrade to a new CIS or STIG version
  • Easy to upgrade to a new Microsoft SQL Server version

Let’s go over these design goals.

Easy to apply

Securing your database with the mssql_secured module is as easy as adding one line of puppet code to your manifest. In its most basic form:

mssql_secured::ensure_cis {'MYDB':}

is enough.

Easy to skip controls

The CIS benchmark and STIG documents are very extensive. Applying ALL controls can make your database too secure for your application. The mssql_secured module allows you to specify what controls you want to skip. You can, for examle use the parameter skip_list

mssql_secured::ensure_cis {'MYDB':
  skip_list   => [

You can also use hiera to skip certain controls. Here is an example of that:

mssql_secured::controls::log_file_destination_directory_is_set_correctly::mydb: skip
mssql_secured::controls::filename_pattern_for_log_files_is_set_correctly::mydb: skip

Easy to customize values

For specific controls, the CIS benchmark allows you to specify a value. To be compiant with the CIS benchmark, the specified value must be within a specific range. The mssql_secured module supports this. Let’s look at an example. The control failed_login_attempts_is_less_than_or_equal_to_5 guards that the number of failed login attempts is 5 or less (as the name states). The default value the mssql_secured module enforces is 5. But you can make it less. You can use the value 3.

mssql_secured::controls::failed_login_attempts_is_less_than_or_equal_to_5::preferred_value:	3

Is a way to do this. To ensure you stay compliant, the mssql_secured module enforces that the values stay within the bounds of CIS range. When you specify a value that is outside of the range, Puppet will not accept it. Here is an example when we specify 6:

Error: Evaluation Error: Error while evaluating a Resource Statement, mssql_secured::Controls::Failed_login_attempts_is_less_than_or_equal_to_5[TEST]: parameter 'preferred_value' expects an Integer[0, 5] value, got Integer[6, 6] (file: /root/examples/apply_one_control.pp, line: 5) on node mssql_secured

Easy to exclude some objects

Many CIS controls layout settings that must apply to all objects in the database. Some organizations battle with this. Most of the time, there are just a few objects that have to deviate from a control to keep the application working. The mssql_secured module supports this. You can specify exclusion rules.

TODO: Add an example

Correlate Puppet changes to CIS

Although it is excellent that Puppet guards the compliance of your database, it is good to know that when Puppet changes something, WHY it changed something. What was the control that caused this? And preferably, what paragraph in what version of the CIS benchmark states this.

The mssql_secured module helps you with this. Here is some example output:

Notice: Making sure database MSSQLSERVER is secured.
Notice: Apply mssql_secured CIS controls from mssql2019 V1.2.0 on MSSQLSERVER.  
Notice: /Stage[main]/Profile::Base::Vagrant/Exec[disable windows defender]/returns: executed successfully
Notice: /Stage[main]/Mssql_secured::Mssql2019::V1_2_0::P5_1::Mssqlserver/Mssql_secured::Controls::Maximum_number_of_error_log_files_is_set_to_greater_than_or_equal_to_12[MSSQLSERVER]/Registry_value[maximum_number_of_error_log_files_is_set_to_greater_than_or_equal_to_12 on MSSQLSERVER]/data: data changed '500' to '100' 
Notice: /Stage[main]/Mssql_secured::Mssql2019::V1_2_0::P5_2::Mssqlserver/Mssql_secured::Controls::Default_trace_enabled_server_configuration_option_is_set_to_1[MSSQLSERVER]/Sqlserver_tsql[default_trace_enabled_server_configuration_option_is_set_to_1 on MSSQLSERVER]/returns: executed successfully

As you can see all of the messages contain: The database version of the CIS benchmark (e.g. /Mssql2019) The document version of the CIS benchmark (e.g. ::V1_2_0) The paragraph in the CIS benchmark (e.g. ::P5_1) The database that is changed (e.g. ``::Mssqlserver) The name of the control (e.g. maximum_number_of_error_log_files_is_set_to_greater_than_or_equal_to_12`) This way, you can always see what the reason is for a change.

Upgrade to a new CIS or Microsoft SQL Server version

Because the exclusion lists, preferred values, and skip lists, are bound to the name of the control, your customizations will most of the times be compatible with newer versions of a CIS benchmark. So when a newer version comes. You only have to change the doc_version property. Let’s see an example. Let’s say a V1.1.0 for the mssql2019 is available.

mssql_secured::ensure_cis {'MYDB':
  product_version => 'mssql2019',
  doc_version     => 'V1.1.0'

This is enough to start using the new CIS version. Sometimes CIS. Of course you will still have to look if new controls are available that you want to skip or customize. Also, sometimes the value of a configuration item changes. This will cause a new control in the mssql_secured module. Let’s look at this in a contrived example.

Let’s say that in the CIS document, the setting for the number of failed login attempts has been changed from a value of 12 or less to a value of 20 or more. The original control was named: maximum_number_of_error_log_files_is_set_to_greater_than_or_equal_to_12. It will still be available. But a new control is also available. It is now called failed_login_attempts_is_less_than_or_equal_to_20. Just change these values in your skip_lists, excludes and preferred value settings is enough.