May 16, 2019 at 10:48

PowerShell Desired State Configuration and Files: Part 1. Configuring DSC Pull Server to work with SQL Database

  • Tutorial


PowerShell Desired State Configuration (DSC) makes it easy to deploy and configure 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:

  • There are no complete reports on the status of systems. If the necessary configuration was not applied on some servers, then without these reports we will not know about it. It is quite difficult to get information from the built-in report server, and for a large number of hosts it is also a long time.
  • 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 a common repository of mof configuration files, modules, and registration keys.

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

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

Note: For Windows Server 2016, you must use AccessDatabaseEngine2016x86 , because Microsoft.Jet.OLEDB.4.0 is no longer supported.

I will not dwell on the deployment process of the polling DSC server, it is very well described here . I note only a couple of points. If we deploy the polling DSC on the same web server with WSUS or Kaspersky Security Center, then in the configuration creation script it is necessary 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 resolved in Kaspersky Security Center v11).
  2. Enable32BitAppOnWin64   = $true

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

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

Creating a SQL Database


  1. Create an empty SQL database named DSC.



  2. Create an account to connect to this database. Pre-verify that authentication of both Windows and SQL accounts is enabled on the SQL server.



  3. Go to the User Mapping section. Select a database, in this case, DSC. We give the rights of the owner of the database.

  4. 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 empty devices.mdb as part of the PS module PSDesiredStateConfiguration through 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 collect the data from - in our case, it is a Microsoft Access database. Click Next.

  3. Select the file from where we import the scheme.

  4. We indicate where to import - we have this SQL database.

  5. We select the SQL server (Server Name) and the database into which we will import the data (DataBase).

  6. We select the option Copy data from one or more tables or views (copying data from tables or views).

  7. We select the tables from which we will import the database schema.

  8. Check Run Immediately and click Finish.

  9. Done.

  10. As a result, tables should appear in the DSC database.


Configuring a .mdb “proxy” file


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

Let's create a system ODBC connection to the SQL server (the bit depth of the connection must match the bit depth of 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=",'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 MS Access installed. Go to the System DSN tab and create a system connection (Add).

  2. We indicate 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 use the previously created login with the name DSC.

  5. Specify the database in the DSC connection settings.

  6. Click Finish.

  7. Before completing the settings, we verify that the connection is working (Test Data Source).

  8. Done.


Creating the devices.mdb database in MS Access. We start 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 for communication with the data source.

  2. In the new window, select the Machine Data Source tab and click OK. In the new window, enter the credentials for connecting to the SQL server.

  3. We select the tables that need to be linked. We tick off the Save Password checkbox and click OK. Password should be saved every time for all three tables.

  4. In the indices you need to select the following:
    - TargetName for the dbo_Devices table;



    - NodeName or IPAddress for dbo_RegistrationData;



    - NodeName or IPAddress for dbo_StatusReport.

  5. Rename the tables in MS Access, namely: remove the dbo_ prefix so that DSC can use them.

  6. Done.

  7. Save the file and close MS Access. Now copy the resulting devices.mdb to the DSC server (by default in C: \ Program Files \ WindowsPowershell \ DSCService) and replace it with the existing one (if any).

Configuring a DSC Server to Use SQL


  1. We return 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, bit depth, and connection settings should be the same as when creating the MDB file. You can copy the already configured empty devices.mdb from here.
  2. To use devices.mdb, you need to make changes to the web.config of the polling DSC server (by default, C: \ inetpub \ PSDSCPullServer \ web.config):


- for Windows Server 2012


- for Windows Server 2016



This completes the setup of the DSC server.

Checking the health of the DSC server


  1. Check that the DSC server is accessible through a web browser.

  2. Теперь проверим, правильно ли работает опрашивающий сервер DSC. Для этого в составе модуля xPSDesiredStateConfiguration есть скрипт pullserversetuptests.ps1. Перед запуском этого скрипта необходимо установить модуль Powershell с именем Pester. Устанавливаем его Install-Module -Name Pester.
  3. Открываем C:\Program Files\WindowsPowerShell\Modules\xPSDesiredStateConfiguration\<версия модуля>\DSCPullServerSetup\PullServerDeploymentVerificationTest (в примере версия 8.0.0.0.0).

  4. Открываем PullServerSetupTests.ps1 и проверяем путь к web.config DSC-сервера. Красным выделил путь к web.config, который будет проверять скрипт. Если нужно, меняем этот путь.

  5. Запускаем pullserversetuptests.ps1
    Invoke-Pester .\PullServerSetupTests.ps1
    Все работает.

  6. In SQL Management Studio, we see that the administered hosts send reports to the DSC report server and the data goes to the DSC database on the SQL server.


That's all. In the following articles I plan to tell how to build reports on the received data, and touch on questions about fault tolerance and scalability.
Tags:

Also popular now: