Forge Home

Modules

Puppet

Resources

PuppetEcosystemPuppet CommunityUpdates

sqlserverdsc

Module with DSC resources for deployment and configuration of Microsoft SQL Server.
Project URLRSS FeedReport issues

23,878 downloads

88 latest version

5.0 quality score

We run a couple of automated
scans to help you access a
module's quality. Each module is
given a score based on how well
the author has formatted their
code and documentation and
modules are also checked for
malware using VirusTotal.

Please note, the information below
is for guidance only and neither of
these methods should be considered
an endorsement by Puppet.

Version information

  • 17.0.0-0-0 (latest)
  • 16.6.0-0-1
  • 16.6.0-0-0
  • 16.5.0-0-1
  • 16.5.0-0-0
  • 16.4.0-0-4
  • 16.4.0-0-3
  • 16.4.0-0-2
  • 16.4.0-0-1
  • 16.4.0-0-0
  • 16.0.0-0-4
  • 16.0.0-0-3
  • 16.0.0-0-2
  • 16.0.0-0-1
  • 16.0.0-0-0
  • 15.2.0-0-1
  • 15.2.0-0-0
  • 15.1.1-0-0
  • 15.1.0-0-0
  • 15.0.1-0-0
  • 15.0.0-0-3
  • 15.0.0-0-2
  • 15.0.0-0-1
  • 15.0.0-0-0
  • 14.2.1-0-4
  • 14.2.1-0-3
  • 14.2.1-0-2
  • 14.2.1-0-1
  • 14.2.1-0-0
  • 14.2.0-0-3
  • 14.2.0-0-2
  • 14.2.0-0-1
  • 14.2.0-0-0
  • 14.1.0-0-3
  • 14.1.0-0-2
  • 14.1.0-0-1
  • 14.1.0-0-0
  • 14.0.0-0-3
  • 14.0.0-0-2
  • 14.0.0-0-1
  • 14.0.0-0-0
  • 13.5.0-0-3
  • 13.5.0-0-2
  • 13.5.0-0-1
  • 13.5.0-0-0
  • 13.4.0-0-3
  • 13.4.0-0-2
  • 13.4.0-0-1
  • 13.4.0-0-0
  • 13.3.0-0-3
  • 13.3.0-0-2
  • 13.3.0-0-1
  • 13.3.0-0-0
  • 13.2.0-0-3
  • 13.2.0-0-2
  • 13.2.0-0-1
  • 13.2.0-0-0
  • 13.1.0-0-3
  • 13.1.0-0-2
  • 13.1.0-0-1
  • 13.1.0-0-0
  • 13.0.0-0-3
  • 13.0.0-0-2
  • 13.0.0-0-1
  • 13.0.0-0-0
  • 12.5.0-0-3
  • 12.5.0-0-2
  • 12.5.0-0-1
  • 12.5.0-0-0
  • 12.4.0-0-3
  • 12.4.0-0-2
  • 12.4.0-0-1
  • 12.4.0-0-0
  • 12.3.0-0-3
  • 12.3.0-0-2
  • 12.3.0-0-1
  • 12.3.0-0-0
  • 12.2.0-0-3
  • 12.2.0-0-2
  • 12.2.0-0-1
  • 12.2.0-0-0
  • 12.1.0-0-3
  • 12.1.0-0-2
  • 12.1.0-0-1
  • 12.1.0-0-0
  • 12.0.0-0-3
  • 12.0.0-0-2
  • 12.0.0-0-1
  • 12.0.0-0-0
  • 11.4.0-0-3
  • 11.4.0-0-2
  • 11.4.0-0-1
  • 11.4.0-0-0
  • 11.3.0-0-3
  • 11.3.0-0-2
  • 11.3.0-0-1
  • 11.3.0-0-0
  • 11.2.0-0-3
  • 11.2.0-0-2
  • 11.2.0-0-1
  • 11.2.0-0-0
  • 11.1.0-0-3
  • 11.1.0-0-2
  • 11.1.0-0-1
  • 11.1.0-0-0
  • 11.0.0-0-3
  • 11.0.0-0-2
  • 11.0.0-0-1
  • 11.0.0-0-0
  • 10.0.0-0-3
  • 10.0.0-0-2
  • 10.0.0-0-1
  • 10.0.0-0-0
released Sep 30th 2024
This version is compatible with:
  • Puppet Enterprise 2023.8.x, 2023.7.x, 2023.6.x, 2023.5.x, 2023.4.x, 2023.3.x, 2023.2.x, 2023.1.x, 2023.0.x, 2021.7.x, 2021.6.x, 2021.5.x, 2021.4.x, 2021.3.x, 2021.2.x, 2021.1.x, 2021.0.x
  • Puppet >= 7.0.0 < 9.0.0

Start using this module

  • r10k or Code Manager
  • Bolt
  • Manual installation
  • Direct download

Add this module to your Puppetfile:

mod 'dsc-sqlserverdsc', '17.0.0-0-0'
Learn more about managing modules with a Puppetfile

Add this module to your Bolt project:

bolt module add dsc-sqlserverdsc
Learn more about using this module with an existing project

Manually install this module globally with Puppet module tool:

puppet module install dsc-sqlserverdsc --version 17.0.0-0-0

Direct download is not typically how you would use a Puppet module to manage your infrastructure, but you may want to download the module in order to inspect the code.

Download
Tags: windows, dsc, puppetdsc

Documentation

dsc/sqlserverdsc — version 17.0.0-0-0 Sep 30th 2024

sqlserverdsc

Table of Contents

  1. Description
  2. Requirements
  3. Usage
  4. Troubleshooting

Description

This is an auto-generated module, using the Puppet DSC Builder to vendor and expose the SqlServerDsc PowerShell module's DSC resources as Puppet resources. The functionality of this module comes entirely from the vendored PowerShell resources, which are pinned at v17.0.0. The PowerShell module describes itself like this:

Module with DSC resources for deployment and configuration of Microsoft SQL Server.

For information on troubleshooting to determine whether any encountered problems are with the Puppet wrapper or the DSC resource, see the troubleshooting section below.

Requirements

This module, like all auto-generated Puppetized DSC modules, relies on two important technologies in the Puppet stack: the Puppet Resource API and the puppetlabs/pwshlib Puppet module.

The Resource API provides a simplified option for writing types and providers and is responsible for how this module is structured. The Resource API ships inside of Puppet starting with version 6. While it is technically possible to add the Resource API functionality to Puppet 5.5.x, the DSC functionality has not been tested in this setup. For more information on the Resource API, review the documentation.

The module also depends on the pwshlib module. This Puppet module includes two important things: the ruby-pwsh library for running PowerShell code from ruby and the base provider for DSC resources, which this module leverages.

All of the actual work being done to call the DSC resources vendored with this module is in this file from the pwshlib module. This is important for troubleshooting and bug reporting, but doesn't impact your use of the module except that the end result will be that nothing works, as the dependency is not installed alongside this module!

Long File Path Support

Several PowerShell modules with DSC Resources end up with very long file paths once vendored, many of which exceed the 260 character limit for file paths. Luckily in Windows 10 (build 1607+), Windows Server 2016 (build 1607+), and Windows Server 2019 there is now an option for supporting long file paths transparently!

We strongly recommend enabling long file path support on any machines using this module to avoid path length issues.

You can set this value using the Puppet registry_value resource:

registry_value { 'HKLM\System\CurrentControlSet\Control\FileSystem\LongPathsEnabled':
  ensure   => 'present',
  data     => [1],
  provider => 'registry',
  type     => 'dword',
}

You can also set this value outside of Puppet by following the Microsoft documentation.

Usage

You can specify any of the DSC resources from this module like a normal Puppet resource in your manifests. The examples below use DSC resources from from the PowerShellGet repository, regardless of what module you're looking at here; the syntax, not the specifics, is what's important.

For reference documentation about the DSC resources exposed in this module, see the Reference Forge tab, or the REFERENCE.md file.

# Include a meaningful title for your resource declaration
dsc_psrepository { 'Add team module repo':
    dsc_name               => 'foo',
    dsc_ensure             => present,
    # This location is nonsense, can be any valid folder on your
    # machine or in a share, any location the SourceLocation param
    # for the DSC resource will accept.
    dsc_sourcelocation     => 'C:\Program Files',
    # You must always pass an enum fully lower-cased;
    # Puppet is case sensitive even when PowerShell isn't
    dsc_installationpolicy => untrusted,
}

dsc_psrepository { 'Trust public gallery':
    dsc_name               => 'PSGallery',
    dsc_ensure             => present,
    dsc_installationpolicy => trusted,
}

dsc_psmodule { 'Make Ruby manageable via uru':
  dsc_name   => 'RubyInstaller',
  dsc_ensure => present,
}

Credentials

Credentials are always specified as a hash of the username and password for the account. The password must use the Puppet Sensitive type; this ensures that logs and reports redact the password, displaying it instead as <Sensitive [value redacted]>.

dsc_psrepository { 'PowerShell Gallery':
  dsc_name                 => 'psgAllery',
  dsc_installationpolicy   => 'Trusted',
  dsc_psdscrunascredential => {
    user     => 'apple',
    password => Sensitive('foobar'),
  },
}

Class-Based Resources

Class-based DSC Resources can be used like any other DSC Resource in this module, with one important note: Due to a bug in calling class-based DSC Resources by path instead of module name, each call to Invoke-DscResource needs to temporarily munge the system-level environment variable for PSModulePath; the variable is reset prior to the end of each invocation.

CIM Instances

Because the CIM instances for DSC resources are fully mapped, the types actually explain fairly precisely what the shape of each CIM instance has to be - and, moreover, the type definition means that you get checking at catalog compile time. Puppet parses CIM instances are structured hashes (or arrays of structured hashes) that explicitly declare their keys and the valid types of values for each key.

So, for the dsc_accesscontrolentry property of the dsc_ntfsaccessentry type, which has a MOF type of NTFSAccessControlList[], Puppet defines the CIM instance as:

Array[Struct[{
  accesscontrolentry => Array[Struct[{
    accesscontroltype => Enum['Allow', 'Deny'],
    inheritance => Enum['This folder only', 'This folder subfolders and files', 'This folder and subfolders', 'This folder and files', 'Subfolders and files only', 'Subfolders only', 'Files only'],
    ensure => Enum['Present', 'Absent'],
    cim_instance_type => 'NTFSAccessControlEntry',
    filesystemrights => Array[Enum['AppendData', 'ChangePermissions', 'CreateDirectories', 'CreateFiles', 'Delete', 'DeleteSubdirectoriesAndFiles', 'ExecuteFile', 'FullControl', 'ListDirectory', 'Modify', 'Read', 'ReadAndExecute', 'ReadAttributes', 'ReadData', 'ReadExtendedAttributes', 'ReadPermissions', 'Synchronize', 'TakeOwnership', 'Traverse', 'Write', 'WriteAttributes', 'WriteData', 'WriteExtendedAttributes']]
  }]],
  forceprincipal => Optional[Boolean],
  principal => Optional[String],
}]]

A valid example of that in a puppet manifest looks like this:

dsc_accesscontrollist => [
  {
    accesscontrolentry => [
      {
        accesscontroltype => 'Allow',
        inheritance       => 'This folder only',
        ensure            => 'Present',
        filesystemrights  => 'ChangePermissions',
        cim_instance_type => 'NTFSAccessControlEntry',
      },
    ],
    principal          => 'veryRealUserName',
  },
]

For more information about using a built module, check out our narrative documentation.

Properties

Note that the only properties specified in a resource declaration which are passed to Invoke-Dsc are all prepended with dsc. If a property does _not start with dsc_ it is used to control how Puppet interacts with DSC/other Puppet resources - for example, specifying a unique name for the resource for Puppet to distinguish between declarations or Puppet metaparameters (notifies, before, etc).

Validation Mode

By default, these resources use the property validation mode, which checks whether or not the resource is in the desired state the same way most Puppet resources are validated; by comparing the properties returned from the system with those specified in the manifest. Sometimes, however, this is insufficient; many DSC Resources return data that does not compare properly to the desired state (some are missing properties, others are malformed, some simply cannot be strictly compared). In these cases, you can set the validation mode to resource, which falls back on calling Invoke-DscResource with the Test method and trusts that result.

When using the resource validation mode, the resource is tested once and will then treat all properties of that resource as in sync (if the result returned true) or not in sync. This loses the granularity of change reporting for the resource but prevents flapping and unexpected behavior.

# This will flap because the DSC resource never returns name in SecurityPolicyDsc v2.10.0.0
dsc_securityoption { 'Enforce Anonoymous SID Translation':
  dsc_name => 'Enforce Anonymous SID Translation',
  dsc_network_access_allow_anonymous_sid_name_translation => 'Disabled',
}

# This will idempotently apply
dsc_psrepository { 'PowerShell Gallery':
  validation_mode => 'resource',
  dsc_name        => 'Enforce Anonymous SID Translation',
  dsc_network_access_allow_anonymous_sid_name_translation => 'Disabled',
}

It is important to note that this feature is only supported with a version of puppetlabs-pwshlib equal to or higher than 0.9.0, in which the supporting code for the DSC Base Provider to implement custom insync was shipped.

Finally, while this module's metadata says that the supported Puppet versions are 6.0.0 and up, the implementation of the validation_mode parameter relies on the custom_insync feature of the Puppet Resource API. The custom_insync feature first shipped in the puppet-resource_api version 1.8.14, which itself is only included in Puppet versions equal to or newer than 6.23.0 and 7.8.0 for the 6x and 7x platforms respectively. Using this module against older Puppet versions will result in a warning (example below) and only use the default property-by-property change reporting, regardless of the setting of validation_mode.

Warning: Unknown feature detected: ["custom_insync"]

Troubleshooting

In general, there are three broad categories of problems:

  1. Problems with the way the underlying DSC resource works.
  2. Problems with the type definition, where you can't specify a valid set of properties for the DSC resource
  3. Problems with calling the underlying DSC resource - the parameters aren't being passed correctly or the resource can't be found

Unfortunately, problems with the way the underlying DSC resource works are something we can't help directly with. You'll need to file an issue with the upstream maintainers for the PowerShell module.

Problems with the type definition are when a value that should be valid according to the DSC resource's documentation and code is not accepted by the Puppet wrapper. If and when you run across one of these, please file an issue with the Puppet DSC Builder; this is where the conversion happens and once we know of a problem we can fix it and regenerate the Puppet modules. To help us identify the issue, please specify the DSC module, version, resource, property and values that are giving you issues. Once a fix is available we will regenerate and release updated versions of this Puppet wrapper.

Problems with calling the underlying DSC resource become apparent by comparing <value passed in in puppet> with <value received by DSC>. In this case, please file an issue with the puppetlabs/pwshlib module, which is where the DSC base provider actually lives. We'll investigate and prioritize a fix and update the puppetlabs/pwshlib module. Updating to the pwshlib version with the fix will immediately take advantage of the improved functionality without waiting for this module to be reconverted and published.

For specific information on troubleshooting a generated module, check the troubleshooting guide for the puppet.dsc module.

Known Limitations

  • Currently, because of the way Puppet caches files on agents, use of the legacy puppetlabs-dsc module is not compatible with this or any auto-generated DSC module. Inclusion of both will lead to pluginsync conflicts.

  • Right now, if you have the same version of a PowerShell module with class-based DSC Resources in your PSModulePath as vendored in a Puppetized DSC Module, you cannot use those class-based DSC Resources from inside of Puppet due to a bug in DSC which prevents using a module by path reference instead of name. Instead, DSC will see that there are two DSC Resources for the same module and version and then error out.

  • When PowerShell Script Block Logging is enabled, data marked as sensitive in your manifest may appear in these logs as plain text. It is highly recommended, by both Puppet and Microsoft, that you also enable Protected Event Logging alongside this to encrypt the logs to protect this information.

Configuring the LCM

In order for a Puppetized DSC module to function, the DSC Local Configuration Manager (LCM) RefreshMode must be set to either Push or Disabled. The default value for RefreshMode in WMF 5.0 and WMF 5.1 is Push - so if it has not been set to anything else then there is no action needed on your part. However if the value of the LCM has been set to anything other than Push then the module will not function and so the value must either be changed back or disabled.

The Puppetized DSC modules use the Invoke-DscResource cmdlet to invoke DSC Resources of the target machine. If the RefreshMode is set to Pull, DSC Resources will only run from a DSC Pull Server - in this setting DSC does not allow any DSC Resources to be run interactively on the host.

Module Installation

If you're using this module with Puppet Enterprise and Code Manager, everything should "just work" - no errors or issues acquiring and deploying this or any Puppetized DSC module to nodes.

Unfortunately, due a bug in minitar which prevents it from unpacking archives with long file paths, both r10k and serverless Puppet (via puppet module install) methods of installing modules with long path names will fail. In short, minitar is unable to unpack modules that contain long file paths (though it can create them).

As a workaround, you can retrieve DSC modules from the forge via PowerShell and 7zip:

$ModuleAuthor = 'dsc'
$ModuleName = 'xremotedesktopsessionhost'
$ModuleVersion = '2.0.0-0-1'
$ArchiveFileName = "$ModuleAuthor-$ModuleName-$ModuleVersion.tar.gz"
$DownloadUri = "https://forge.puppet.com/v3/files/$ArchiveFileName"
# Download the module tar.gz to the current directory
Invoke-WebRequest -Uri $DownloadUri -OutFile ./$ArchiveFileName
# Use 7zip to extract the module to the current directory
& 7z x $ArchiveFileName -so | & 7z x -aoa -si -ttar