Configuring a PowerShell DSC Web Pull Server to use SQL Database

[Server Man]

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.

I currently also support customer in designing and building a Continuous Integration and Continuous Deployment pipeline with Desired State Configuration and Visual Studio Team Foundation Server besides Operations Manager, Orchestrator and Operations Management Suite.

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:LocalMachineMy -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=SQLDSC
• SQL authentication: Provider=SQLOLEDB.1;Password=”password”;Persist Security Info=True;User ID=user;Initial Catalog=master;Data Source=SQLDSC

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 = “Crogram FilesWindowsPowerShellDscServiceSecureWebPullServerWebsite”
CertificateThumbPrint = $Thumbprint
ModulePath = “Crogram FilesWindowsPowerShellDscServiceSecureWebPullServerModules”
ConfigurationPath = “Crogram FilesWindowsPowerShellDscServiceSecureWebPullServerConfiguration”
State = “Started”
DependsOn = “[WindowsFeature]DSCServiceFeature”
RegistrationKeyPath = “Crogram FilesWindowsPowerShellDscService”
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 = “Crogram FilesWindowsPowerShellDscServiceRegistrationKeys.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:WindowsTemp -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:WindowsTemp 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:WindowsTemp -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 “Crogram FilesWindowsPowerShellDscServiceSecureWebPullServerConfiguration” -Verbose

New-DscChecksum -Path “Crogram FilesWindowsPowerShellDscServiceSecureWebPullServerConfiguration” -OutPath “Crogram FilesWindowsPowerShellDscServiceSecureWebPullServerConfiguration” -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:WindowsTemp -Verbose

Assuming the previous steps went well, we should now have a Meta MOF file in C:WindowsTemp 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:WindowsTemp -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.

Continue reading...