mssql_secured

Table of Contents

1. Overview
2. License
3. Description - What the module does and why it is useful
4. Setup

• Requirements
• Installing the mssql_secured module

5. Usage - Configuration options and additional functionality
6. Reference - An under-the-hood peek at what the module is doing and how
7. Limitations - OS compatibility, etc.

Overview

The mssql_secured module is the Puppet implementation of multiple security-related controls. At this point in time we support:

• Center for Internet Security (CIS) benchmarks
• DoD Security Technical Implementation Guides

We now support the current versions:

• CIS MsSQL 2019 V1.2.0

The module is part of our family of Puppet modules to manage and secure Microsoft SQL Server databases with Puppet. Besides the mssql_secured module, this family also contains:

• mssql_profile As an easy customizable profile class to manage your complete progress setup.

License

This is an affordable commercially licensed module. But you can use the module on VirtualBox based development systems for FREE. When used on real systems a license is required.

You can license our modules in multiple ways. Our basic licensing model requires a subscription per node. But contact us for details.

Check the License for details.

Description

Let's first dive into the question: "What configuration settings are needed to get my system secure?". Many people have asked themselves this question. The Center for Internet Security (CIS) is one of the means to get an answer. CIS also has a security baseline for MsSQL Server 2019: CIS Microsoft SQL Server 2019 Benchmark v1.2.0. We have taken this baseline and Puppetized it for you to use.

It is called the mssql_secured and contains an implementation of all rules in the benchmark that describe a configuration setting inside of the database. At this point 40 of the 42 rules are implemented and 2 are not because they rely on settings outside of the database.

On a Puppet run, the module will inspect all settings described in the CIS rules and apply changes to them if they deviate from the standard. (If you have started the Puppet run with a noop, it will do nothing but report all changes that would have been made. ). All changes will be reported to the Puppet master and on the console, you get an overview of the changes. Because the Puppet agent runs every 20 minutes (or different if you set it to a different interval) every 20 minutes your database configuration is checked against the CIS benchmark and you can sleep well and be assured your data is safe.

Check the documentation here

Setup

Requirements

The mssql_secured module requires:

• Puppet module enterprisemodules-easy_type installed.
• Puppet version 4.0 or higher. Can be Puppet Enterprise or Puppet Open Source
• Microsoft SQL Server 2019
• A valid Microsoft SQL Server license
• A valid Enterprise Modules license for usage.
• Runs on most Windows systems.

Installing the mssql_secured module

To install these modules, you can use a Puppetfile

mod 'enterprisemodules/mssql_secured'               ,'3.x.x'

Then use the librarian-puppet or r10K to install the software.

You can also install the software using the puppet module command:

puppet module install enterprisemodules-mssql_secured

Usage

The scope of securing your Microsoft SQL Server database is large. The number of security controls in the CIS benchmark is large. This might make you think that it is difficult to get started, but actually, it is very simple.

Enabling CIS for your database

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:

"`puppet 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`

```puppet
mssql_secured::ensure_cis {'MYDB':
  skip_list   => [
    'sql_server_is_configured_to_use_non_standard_ports',
    'public_role_in_the_msdb_database_is_not_granted_access_to_sql_agent_proxies',

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 maximum_number_of_error_log_files_is_set_to_greater_than_or_equal_to_12 guards that the number of error log is 12 or more (as the name states). The default value the mssql_secured module enforces is 12. But you can make it more. You can use the value 200.

mssql_secured::controls::maximum_number_of_error_log_files_is_set_to_greater_than_or_equal_to_12::preferred_value:  200

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 10:

Error: Evaluation Error: Error while evaluating a Resource Statement, Mssql_secured::Controls::Maximum_number_of_error_log_files_is_set_to_greater_than_or_equal_to_12[MSSQLSERVER]: parameter 'num_error_logs_hiera_value' expects an Integer[12] value, got Integer[10, 10] (line: 2) on node windb1542-secured.home

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.

Reference

Here you can find some more information regarding this puppet module:

• The mssql_secured documentation

Limitations

This module runs on most Windows versions. It requires a puppet version 5 or higher. The module does NOT run on linux systems.