cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Configuring a PowerShell DSC Web Pull Server to use SQL Database
By
BrandonWilson
Published Sep 20 2018 08:11 AM 3,488 Views
BrandonWilson
Microsoft
‎Sep 20 2018 08:11 AM

Configuring a PowerShell DSC Web Pull Server to use SQL Database

‎Sep 20 2018 08:11 AM

 

First published on TechNet on Jul 09, 2018

 

Introduction

 

Hi! Thank you for visiting this blog to find out more about how you can configure a PowerShell DSC Web Pull Server to use an SQL database instead of the "Devices.edb" solution we currently use.

 

Since you made it his far I assume that you're already familiar with PowerShell and PowerShell Desired State Configuration but if not, I encourage you to read more about PowerShell and PowerShell Desired State Configuration.

 

Either way, you are probably ready to experiment with Desired State Configuration or ready to implement a Desired State Configuration architecture within your environment (perhaps even production).

 

I wrote this blog post to show you how you can implement an example Desired State Configuration environment where the Secure Pull Web Server uses a SQL database to store all data.

 

About me

 

Before I do so I will tell you a little bit about myself. My name is Serge Zuidinga and I'm a Dutch Premier Field Engineer with System Center Operations Manager as my core technology.

I started working at Microsoft in September 2014 focusing on supporting customers with their Operations Manager environment(s) and, among other things, the integration with automation products like System Center Orchestrator.

I always had a passion for scripting and application development so this was the ideal situation for me since I could use my passion for PowerShell in combination with Operations Manager and Orchestrator.

I've been seriously working with PowerShell ever since and am currently involved with not only System Center Operations Manager and Orchestrator but with Azure in general and Azure Automation, OMS, EMS, Operations Manager Management Pack Authoring, Visual Studio, Visual Studio Team Foundation Server, PowerShell and PowerShell Desired State Configuration in particular.

 

Let's get started

 

Glad to see you made it through the introduction. So, this is the plan:

  • Step 1: the prerequisites
  • Step 2: implement our example environment
  • Step 3: watch it work
  • Step 4: enjoy our accomplishments

 

Prerequisites

 

Windows Server 2019 Technical Preview

 

To be able to leverage the ability to use an SQL database with our pull server, we need to deploy a Windows Server 2019 Technical Preview server which holds the version of WMF 5.1 that includes the ability to connect to SQL server.

 

We should make sure that we have the latest version of Windows Server 2019 Technical Preview installed since, at least up until build 17639, the MUI file could be missing required elements to support SQL server.

 

Note: There is currently no support for SQL with DSC on Windows Server 2016 (or previous Windows Server versions) even though WMF 5.1 is available for Windows Server 2016!

 

If you want, you can read all about the supported database systems for WMF versions 4.0 and higher at Desired State Configuration Pull Service (“Supported database systems”-section) and please check out this great post by Raimund Andrée on how to use a SQL server 2016 as the backend database for a Desired State Pull Server.

 

We also need to make sure that we have version 8.2.0.0 (or higher) of the “xPSDesiredStateConfiguration”-module installed on our Windows Server 2019 Technical Preview server.

 

Hint: Find-Module -Name xPSDesiredStateConfiguration | Install-Module

 

Note: version 8.3.0.0 is the latest version of the “xPSDesiredStateConfiguration”-module at the time this blog post was written

 

A certificate for enabling a HTTPS binding within IIS is also required for our example environment to work so please make sure you have a web server certificate installed on your Windows Server 2019 Technical Preview server along with the “xPSDesiredStateConfiguration”-module.

 

Finally, access to any SQL server instance to host our database.

 

From a firewall perspective, we only need access to the TCP port the SQL server instance is listening on from our pull server.

 

There’s no need to create a database upfront since this will be taken care of by our pull server (our database will always be created with “DSC” as the name for our database) and both SQL and Windows Authentication is supported.

 

Note: you can use a Domain User account instead of the “Local System”-account the IIS AppPool is configured with by default.

 

If you want to use a Domain User account, you only need to make sure that it has “dbcreator”-permissions configured for the SQL Server instance that will host the “DSC”-database

 

Let’s get cracking!

 

Implement a Secure Web Pull Server

 

Step 1: Install the PowerShell Desired State Configuration by using "Add Roles and features" available through Server Manager or from PowerShell: Add-WindowsFeature -Name DSC-Service

 

Step 2: Get the thumbprint of our web server certificate we are going to use for our HTTPS binding: Get-ChildItem -Path Cert:\LocalMachine\My\ -SSLServerAuthentication Get a unique GUID that we are going to use as a registration key: (New-Guid).Guid Get the SQL connection string that will allow our pull server to connect to the appropriate SQL server instance or modify and use one of the following examples:

 

  • Windows Authentication: Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source=SQL\DSC
  • SQL authentication: Provider=SQLOLEDB.1;Password="password";Persist Security Info=True;User ID=user;Initial Catalog=master;Data Source=SQL\DSC

 

Note : you can leave Initial Catalog=master as is because we'll create and use a specific database (called "DSC") for use with our pull server.

 

Step 3 Create a MOF file that we will use to configure our pull server. You can modify and use this example:

 

 

# === Arguments ================================================ #
# We got these from step 2                                       #
$Thumbprint = "BF6E5EFC44A15FE238CDE2A77D9A12B07B0BA200"
$Guid = "5fd98d96-7864-4006-b60d-0a907a676c6a"
# === Arguments ================================================ #

# === Section Secure Web Pull Server with SQL database ========= #
Configuration SecureWebPullServerWithSQLDatabase {
  Param([string]$NodeName = "localhost",
        [string$Thumbprint = "$(Throw "Provide a valid certificate thumbprint to continue")",
        [string]$Guid = "$(Throw "Provide a valid GUID to continue")")

  Import-DscResource -ModuleName PSDesiredStateConfiguration
  Import-DSCResource -ModuleName xPSDesiredStateConfiguration

  Node $NodeName {
    WindowsFeature DSCServiceFeature {
      Ensure = "Present"
      Name = "DSC-Service"
    }

    xDscWebService SecureWebPullServer {
      Ensure = "Present"
      EndpointName = "SecureWebPullServer"
      Port = 443
      PhysicalPath = "C:\Program Files\WindowsPowerShell\DscService\SecureWebPullServer\Website"
      CertificateThumbPrint = $Thumbprint
      ModulePath = "C:\Program Files\WindowsPowerShell\DscService\SecureWebPullServer\Modules"
      ConfigurationPath = "C:\Program Files\WindowsPowerShell\DscService\SecureWebPullServer\Configuration"
      State = "Started"
      DependsOn = "[WindowsFeature]DSCServiceFeature"
      RegistrationKeyPath = "C:\Program Files\WindowsPowerShell\DscService"
      AcceptSelfSignedCertificates = $true
      UseSecurityBestPractices = $true
      SqlProvider = $true
      SqlConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source=PUT_DMZ_SQL_SERVER_INSTANCE_HERE"
    }

    WindowsFeature IISMGMTConsole {
      Ensure = "Present"
      Name = "Web-Mgmt-Console"
      DependsOn = "[xDscWebService]SecureWebPullServer"
    }

    File RegistrationKeyFile {
      Ensure = "Present"
      Type = "File"
      DestinationPath = "C:\Program Files\WindowsPowerShell\DscService\RegistrationKeys.txt"
      Contents = $Guid
      DependsOn = "[xDscWebService]SecureWebPullServer"
    }
  }
}

# === Section Secure Web Pull Server with SQL database ========= #

SecureWebPullServerWithSQLDatabase -NodeName PUT_SERVER_FQDN_HERE -Thumbprint $Thumbprint -Guid $Guid -OutputPath C:\Windows\Temp -Verbose

 

 

Just open it in Windows PowerShell ISE (I use Visual Studio Code but you can use any editor of your preference) and make the necessary modifications (at least the thumbprint and registration key).

 

Assuming the previous steps went well, we should now have a MOF file in C:\Windows\Temp on our Windows Server 2019 Technical Preview server.

Let's get our pull server configured by consuming the MOF file we just created: Start-DscConfiguration -Path C:\Windows\Temp -Wait -Verbose

Our pull server has now been configured and we are ready to host (partial) configurations and have clients connect to consume the appropriate configurations.

We will create such a partial configuration as an example so that we can serve any connected clients.

 

So, like what we just did we can create a configuration and MOF file that our client(s) will consume.

 

You can modify and use this example:

 

 

Configuration TelnetClient {
    Import-DscResource -ModuleName PSDesiredStateConfiguration

    Node TelnetClient {
        WindowsFeature TelnetClient {
            Name   = 'Telnet-Client'
            Ensure = 'Present'
        }
    }
}

TelnetClient -OutputPath “C:\Program Files\WindowsPowerShell\DscService\SecureWebPullServer\Configuration” -Verbose 

New-DscChecksum -Path “C:\Program Files\WindowsPowerShell\DscService\SecureWebPullServer\Configuration” -OutPath “C:\Program Files\WindowsPowerShell\DscService\SecureWebPullServer\Configuration” -Verbose

 

 

We are now ready to connect one or more clients to our pull server. You can modify and use the following example on a Windows Server (for this example you should not use your pull server) that you want to connect to our pull server:

 

 

[DscLocalConfigurationManager()]
Configuration PartialConfig {
    Param([string]$NodeName = 'localhost')
    Node $NodeName {
        Settings {
            RefreshFrequencyMins            = 30;
            RefreshMode                     = "PULL";
            ConfigurationMode               ="ApplyAndAutocorrect";
            AllowModuleOverwrite            = $true;
            RebootNodeIfNeeded              = $true;
            ConfigurationModeFrequencyMins  = 60;
        }
        ConfigurationRepositoryWeb PullServer {
            ServerURL               = "https://FQDN_SECURE_WEB_PULL_SERVER/PSDSCPullServer.svc/"
            RegistrationKey         = "5fd98d96-7864-4006-b60d-0a907a676c6a"
            ConfigurationNames      = @("TelnetClient")
            #ConfigurationNames      = @("TelnetClient","Web-Mgmt-Console") # Multiple partial configurations
        }
        ReportServerWeb PullServer {
            ServerURL               = "https://FQDN_SECURE_WEB_PULL_SERVER/PSDSCPullServer.svc/"
            RegistrationKey         = "5fd98d96-7864-4006-b60d-0a907a676c6a"
        }
        PartialConfiguration TelnetClient {
            Description                     = "Installs the Telnet Client"
            ConfigurationSource             = @("[ConfigurationRepositoryWeb]PullServer")
        }
    }
}
PartialConfig -OutputPath C:\Windows\Temp -Verbose

 

 

Assuming the previous steps went well, we should now have a Meta MOF file in C:\Windows\Temp on our Windows Server 2019 Technical Preview server that allows for configuring the Local Configuration Manager.

To configure the LCM to actually connect to and retrieve (a) configuration(s) from our pull server, we just need to execute: Set-DscLocalConfigurationManager -Path C:\Windows\Temp -Verbose Step 4

 

Congratulations on implementing your pull server with a SQL database! Sit back and enjoy your newly installed and configured PowerShell DSC Secure Web Pull server with a SQL database! Stay tuned for the next post were I will tell you more on how you can pull reports.

 

 

Disclaimer
The sample scripts are not supported under any Microsoft standard support program or service. The sample scripts are provided AS IS without warranty of any kind. Microsoft further disclaims all implied warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample scripts and documentation remains with you. In no event shall Microsoft, its authors, or anyone else involved in the creation, production, or delivery of the scripts be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample scripts or documentation, even if Microsoft has been advised of the possibility of such damages.

1 Comment
Version history
Last update:
‎Jul 07 2020 07:20 AM
Updated by:
Labels