PowerShell Desired State Configuration and File: Part 1. Configuring a DSC Pull Server to Work with a SQL Database

PowerShell Desired State Configuration (DSC) greatly simplifies the work of deploying and configuring the operating system, server roles, and applications when you have hundreds of servers.

But when using DSC on-premises, i.e. not in MS Azure, there are a couple of nuances. They are especially tangible if the organization is large (from 300 workstations and servers) and it has not yet opened the world of containers:
')

• No full system status reports. If the desired configuration has not been applied on any servers, then without these reports we will not know about it. It is rather difficult to obtain information from the built-in report server, and for a large number of hosts it is also long.
• There is no scalability and fault tolerance. It is impossible to build a farm of polling DSC web servers that would have a single fault-tolerant database and common storage of mof-files of configurations, modules and registry keys.

Today I will tell you how you can solve the first problem and get data for building reports. Everything would be simpler if SQL could be used as a database. MS promises native support only in Windows Server 2019 or in build Windows server 1803. It also fails to collect data using the OleDB provider, since the DSC server uses a named parameter that is not fully supported by OleDbCommand.

I found this way: for those who use Windows Server 2012 and 2016, you can configure the use of the SQL database as a backend for the polling DSC server. To do this, create a “proxy” in the form of an .mdb file with linked tables that will redirect data received from client reports to the SQL server database.

Note: For Windows Server 2016, you need to use AccessDatabaseEngine2016x86 , since Microsoft.Jet.OLEDB.4.0 is no longer supported.

I will not elaborate on the deployment process of the polling DSC server, it is very well described here . I will note only a couple of points. If we deploy the polling DSC on one web server with WSUS or Kaspersky Security Center, then in the configuration creation script we need to change the following parameters:

1. UseSecurityBestPractices     = $false 

   
   Otherwise, TLS 1.0 will be disabled, you will not be able to connect to the SQL database. Kaspersky Security Center will not work either (the problem should be solved in Kaspersky Security Center v11).

2.  Enable32BitAppOnWin64   = $true 

   
   If you do not make this change, you will not be able to start the DSC server AppPool on IIS with WSUS.
3. When installing a DSC server with WSUS, disable static and dynamic caching for the DSC site.

Let's proceed to setting up the DSC server to use the SQL database.

Creating SQL Database

1. Create an empty SQL database named DSC.
   
   
   
   
2. Create an account to connect to this database. Check beforehand that authentication of both Windows and SQL accounts is enabled on the SQL server.
   
   
   
   
3. Go to the section User Mapping. Choose a database, in this case - DSC. We give the rights of the owner of the database.
   
   
4. Is done.
   
   

Creating a schema for a DSC database

There are two ways to create a schema for a DSC database:

• independently, through a script in TSQL
  
  

   SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Devices]( [TargetName] [nvarchar](255) NOT NULL, [ConfigurationID] [nvarchar](255) NOT NULL, [ServerCheckSum] [nvarchar](255) NOT NULL, [TargetCheckSum] [nvarchar](255) NOT NULL, [NodeCompliant] [bit] NOT NULL, [LastComplianceTime] [datetime] NULL, [LastHeartbeatTime] [datetime] NULL, [Dirty] [bit] NOT NULL, [StatusCode] [int] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[RegistrationData]( [AgentId] [nvarchar](255) NOT NULL, [LCMVersion] [nvarchar](255) NULL, [NodeName] [nvarchar](255) NULL, [IPAddress] [nvarchar](255) NULL, [ConfigurationNames] [nvarchar](max) NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO CREATE TABLE [dbo].[StatusReport]( [JobId] [nvarchar](50) NOT NULL, [Id] [nvarchar](50) NOT NULL, [OperationType] [nvarchar](255) NULL, [RefreshMode] [nvarchar](255) NULL, [Status] [nvarchar](255) NULL, [LCMVersion] [nvarchar](50) NULL, [ReportFormatVersion] [nvarchar](255) NULL, [ConfigurationVersion] [nvarchar](255) NULL, [NodeName] [nvarchar](255) NULL, [IPAddress] [nvarchar](255) NULL, [StartTime] [datetime] NULL, [EndTime] [datetime] NULL, [Errors] [nvarchar](max) NULL, [StatusData] [nvarchar](max) NULL, [RebootRequested] [nvarchar](255) NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO 

• import data from the empty devices.mdb in the PS-module PSDesiredStateConfiguration via the SQL Data Import Wizard.
  
  Devices.mdb, which we will work with, is located in C: \ Windows \ SysWOW64 \ WindowsPowerShell \ v1.0 \ Modules \ PSDesiredStateConfiguration \ PullServer.
  

1. To import data, run the SQL Server Import and Export Wizard.
   
   
2. We choose where we will take the data from - in our case it is a Microsoft Access database. Click Next.
   
   
3. Select the file from which to import the scheme.
   
   
4. Specify where to import - we have a SQL database.
   
   
5. We select a SQL server (Server Name) and a database into which we will import data (DataBase).
   
   
6. Select the option Copy data from one table or more tables or views (copying data from tables or views).
   
   
7. Select the tables from which we will import the database schema.
   
   
8. Put a tick Run Immediately and click Finish.
   
   
9. Is done.
   
   
10. As a result, tables should appear in the DSC database.
    
    

Configure the .mdb "proxy" file

Creating an ODBC connection to a SQL server. It is assumed that MS Access is not installed on the server with DSC, therefore the databases.mdb setup is performed on an intermediate host with MS Access installed.

Let's create a system ODBC connection to the SQL server (the connection width should be the same as MS Access - 64 or 32). It can be created using:
- Powershell cmdlet:

 Add-OdbcDsn –Name DSC –DriverName 'SQL Server' –Platform '<64-bit or 32-bit>' –DsnType System –SetPropertyValue @('Description=DSC Pull Server',"Server=<Name of your SQL Server>",'Trusted_Connection=yes','Database=DSC') –PassThru 

- or manually, using the connection wizard:

1. Open Administrative tools. We select ODBC data sources depending on the version of installed MS Access. Go to the System DSN tab and create a system connection (Add).
   
   
2. Specify that we will connect to the SQL-server. Click Finish.
   
   
3. Specify the name and server to connect. Then a connection with the same parameters will need to be created on the DSC server.
   
   
4. We indicate that to connect to the SQL server, we used the previously created login with the name DSC.
   
   
5. Specify the database in the DSC connection settings.
   
   
6. Click Finish.
   
   
7. Before completing the configuration, check that the connection is working (Test Data Source).
   
   
8. Is done.
   
   

Creating devices.mdb database in MS Access. Run MS Access and create an empty database called devices.mdb.

1. Go to the External Data tab, click on the ODBC Database. In the window that appears, select Create a linked table to link to the data source.
   
   
2. In the new window, select the Machine Data Source tab and click OK. In the new window, enter the credentials to connect to the SQL-server.
   
   
3. We select the tables that need to be linked. Check the Save password checkbox and click OK. Password save each time for all three tables.
   
   
4. In the indices you need to choose the following:
   - TargetName for the dbo_Devices table;
   
   
   
   - NodeName or IPAddress for dbo_RegistrationData;
   
   
   
   - NodeName or IPAddress for dbo_StatusReport.
   
   
5. Let's rename the tables to MS Access, namely: remove the dbo_ prefix so that the DSC can use them.
   
   
6. Is done.
   
   
7. Save the file and close MS Access. Now, copy the received devices.mdb to the DSC server (by default in C: \ Program Files \ WindowsPowershell \ DSCService) and replace it with the existing one (if it exists).

Setting up a DSC server to use SQL

1. Go back to the DSC server. To connect to the SQL server with our proxy file, create a new ODBC connection on the DSC server. The name, and bitness, and connection settings should be the same as when creating the MDB file. You can copy already configured empty devices.mdb from here.
   
2. To use devices.mdb, you need to make changes to the web.config of the DSC polling server (by default - C: \ inetpub \ PSDSCPullServer \ web.config):

- for Windows Server 2012

 <add key="dbprovider" value="System.Data.OleDb"> <add key="dbconnectionstr" value="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\WindowsPowerShell\DscService\Devices.mdb;"> 

- for Windows Server 2016

 <add key="dbprovider" value="System.Data.OleDb"> <add key="dbconnectionstr" value="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Program Files\WindowsPowerShell\DscService\Devices.mdb;"> 

This completes the DSC server setup.

DSC server health check

1. Check that the DSC server is accessible via a web browser.
   
   
2. Now let's check if the polling DSC server is working properly. For this, the xPSDesiredStateConfiguration module has a pullserversetuptests.ps1 script. Before running this script, you need to install a Powershell module named Pester. Install it. Install-Module -Name Pester.
3. Open C: \ Program Files \ WindowsPowerShell \ Modules \ xPSDesiredStateConfiguration \ <module version> \ DSCPullServerSetup \ PullServerDeploymentVerificationTest (for example, version 8.0.0.0.0).
   
   
4. Open PullServerSetupTests.ps1 and check the path to the DSC server web.config. Red highlighted the path to web.config, which will check the script. If necessary, change this path.
   
   
5. Launch pullserversetuptests.ps1
   Invoke-Pester. \ PullServerSetupTests.ps1
   Everything is working.
   
   
6. In SQL Management Studio, we see that the administrated hosts send reports to the DSC report server and the data enters the DSC database on the SQL server.
   
   

That's all. In the next articles I plan to tell you how to build reports on the obtained data, and touch on questions about fault tolerance and scalability.