PowerShell Desired State Configuration thiab file: part 1. Configuring DSC Pull Server to work with a SQL database

PowerShell Desired State Configuration (DSC) ua kom yooj yim rau txoj haujlwm ntawm kev xa tawm thiab teeb tsa lub operating system, server lub luag haujlwm, thiab cov ntawv thov thaum koj muaj ntau pua servers.

Tab sis thaum siv DSC ntawm thaj chaw, piv txwv li. tsis nyob rau hauv MS Azure, muaj ob peb nuances. Lawv tshwj xeeb tshaj yog pom tau yog tias lub koom haum loj (los ntawm 300 chaw ua haujlwm thiab cov servers) thiab tseem tsis tau pom lub ntiaj teb ntim khoom:

• Tsis muaj cov ntawv qhia tag nrho ntawm cov xwm txheej ntawm cov tshuab. Yog tias qhov kev teeb tsa xav tau tsis tau siv rau ntawm qee lub servers, yog tias tsis muaj cov ntawv ceeb toom no peb yuav tsis paub txog nws. Nws yog qhov nyuaj heev kom tau txais cov ntaub ntawv los ntawm cov neeg ua haujlwm qhia ua haujlwm, thiab rau ntau tus tswv tsev nws kuj tuaj yeem siv sijhawm ntev.
• Lacks scalability thiab txhaum kam rau ua. Nws yog tsis yooj yim sua los tsim ib tug ua liaj ua teb ntawm DSC rub web servers uas yuav muaj ib tug txhaum-tolerant database thiab ib tug ntau cia ntawm mof cov ntaub ntawv rau configurations, modules thiab sau npe yuam sij.

Hnub no kuv yuav qhia koj seb koj tuaj yeem daws qhov teeb meem thawj zaug thiab tau txais cov ntaub ntawv rau kev tshaj tawm. Txhua yam yuav yooj yim dua yog tias SQL tuaj yeem siv los ua database. MS cog lus built-in kev txhawb nqa tsuas yog hauv Windows Server 2019 lossis hauv kev tsim Windows server 1803. Nqa cov ntaub ntawv siv OleDB tus kws kho mob ib yam nkaus yuav tsis ua haujlwmvim DSC Server siv lub npe parameter uas tsis txaus siab los ntawm OleDbCommand.

Kuv pom cov qauv no: rau cov neeg siv Windows Server 2012 thiab 2016, koj tuaj yeem ua tau suab nkauj siv SQL database ua tus backend rau DSC query server. Txhawm rau ua qhov no, peb yuav tsim "proxy" hauv daim ntawv ntawm .mdb cov ntaub ntawv nrog cov rooj sib txuas, uas yuav hloov cov ntaub ntawv tau txais los ntawm cov neeg siv khoom qhia rau SQL server database.

Nco tseg: Rau Windows Server 2016 koj yuav tsum siv AccessDatabaseEngine2016x86vim Microsoft.Jet.OLEDB.4.0 tsis muaj kev txhawb nqa lawm.

Kuv yuav tsis mus rau hauv kev nthuav dav txog cov txheej txheem ntawm kev xa DSC rub server, nws tau piav qhia zoo heev S, SѓS,. Kuv yuav cia li sau ob peb lub ntsiab lus. Yog tias peb xa DSC ruber ntawm tib lub vev xaib server nrog WSUS lossis Kaspersky Security Center, tom qab ntawd hauv kev teeb tsa tsim tsab ntawv peb yuav tsum hloov pauv cov kev txwv hauv qab no:

1. UseSecurityBestPractices     = $false

   Txwv tsis pub, TLS 1.0 yuav raug kaw thiab koj yuav tsis tuaj yeem txuas rau SQL database. Kaspersky Security Center kuj tseem yuav tsis ua haujlwm (qhov teeb meem yuav tsum tau daws hauv Kaspersky Security Center v11).

2. Enable32BitAppOnWin64   = $true

   Yog tias koj tsis hloov qhov no, koj yuav tsis tuaj yeem khiav AppPool DSC server ntawm IIS nrog WSUS.

3. Thaum txhim kho DSC Server nrog WSUS, lov tes taw zoo li qub thiab dynamic caching rau DSC site.

Cia peb txav mus los teeb tsa DSC server siv SQL database.

Tsim SQL database

1. Cia peb tsim qhov khoob SQL database hu ua DSC.

   
   
   

2. Cia peb tsim ib tus account los txuas rau cov ntaub ntawv no. Ua ntej, xyuas tias SQL neeg rau zaub mov tso cai rau kev lees paub ntawm ob lub Windows thiab SQL accounts.

   
   
   

3. Mus rau ntu Tus neeg siv daim ntawv qhia. Xaiv cov database, hauv qhov no DSC. Peb muab cov cai ntawm tus tswv database.

   

4. Ua li cas.

   

Tsim Schema rau DSC Database

Muaj ob txoj hauv kev los tsim ib lub tswv yim rau DSC database:

• ntawm nws tus kheej, los ntawm TSQL tsab ntawv

  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 cov ntaub ntawv los ntawm khoob devices.mdb ua ib feem ntawm PS module PSDesiredStateConfiguration los ntawm SQL Cov Ntaub Ntawv Ntshuam Wizard.

  Lub Devices.mdb peb yuav ua haujlwm nrog nyob hauv C:WindowsSysWOW64WindowsPowerShellv1.0ModulesPSDesiredStateConfigurationPullServer.

1. Txhawm rau import cov ntaub ntawv, khiav SQL Server Ntshuam thiab Export Wizard.

   

2. Peb xaiv qhov twg peb yuav tau txais cov ntaub ntawv los ntawm - hauv peb rooj plaub nws yog Microsoft Access database. Nyem Next.

   

3. Xaiv cov ntaub ntawv uas peb import daim duab.

   

4. Peb qhia qhov twg los import - rau peb nws yog SQL database.

   

5. Xaiv lub SQL neeg rau zaub mov (Server Name) thiab lub database uas peb yuav import cov ntaub ntawv (DataBase).

   

6. Xaiv qhov kev xaiv Luam cov ntaub ntawv los ntawm ib lossis ntau lub rooj los yog kev pom (kev theej cov ntaub ntawv los ntawm cov ntxhuav lossis kev pom).

   

7. Peb xaiv cov ntxhuav los ntawm qhov peb yuav import cov database schema.

   

8. Kos lub Khiav Tam Sim No checkbox thiab nyem Finish.

   

9. Ua li cas.

   

10. Yog li ntawd, cov rooj yuav tsum tshwm sim hauv DSC database.

    

Teeb tsa .mdb “proxy” cov ntaub ntawv

Tsim kev sib txuas ODBC rau SQL server. Nws tau xav tias MS Access tsis tau nruab rau ntawm lub server khiav DSC, yog li teeb tsa databases.mdb ua tiav ntawm tus tswv tsev nruab nrab nrog MS Access ntsia.

Cia peb tsim qhov system ODBC txuas rau SQL neeg rau zaub mov (qhov kev sib txuas bitness yuav tsum phim MS Access bitness - 64 lossis 32). Nws tuaj yeem tsim tau siv:
- 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

- lossis manually, siv qhov kev sib txuas wizard:

1. Qhib cov cuab yeej tswj hwm. Peb xaiv ODBC cov ntaub ntawv qhov chaw nyob ntawm qhov version ntawm MS Access tau teeb tsa. Mus rau qhov System DSN tab thiab tsim qhov system txuas (Ntxiv).

   

2. Peb qhia tias peb yuav txuas rau SQL server. Nyem Ua kom tiav.

   

3. Qhia lub npe thiab server kom txuas rau. Tom qab ntawd ib qho kev sib txuas nrog tib qhov kev txwv yuav tsum tau tsim ntawm DSC server.

   

4. Peb qhia tias txhawm rau txuas rau SQL neeg rau zaub mov, peb siv lub npe tsim yav dhau los nrog lub npe DSC.

   

5. Peb qhia meej cov ntaub ntawv hauv DSC kev sib txuas teeb tsa.

   

6. Nyem Ua kom tiav.

   

7. Ua ntej ua tiav qhov teeb tsa, peb xyuas tias kev sib txuas ua haujlwm (Test Data Source).

   

8. Ua li cas.

   

Tsim ib tug devices.mdb database hauv MS Access. Tua tawm MS Access thiab tsim cov ntaub ntawv khoob hu ua devices.mdb.

1. Mus rau Cov Ntaub Ntawv Sab Nraud thiab nyem rau ntawm ODBC Database. Hauv qhov rai uas tshwm, xaiv Tsim ib lub rooj sib txuas los txuas rau cov ntaub ntawv qhov chaw.

   

2. Nyob rau hauv lub qhov rais tshiab, xaiv lub Tshuab Data Source tab thiab nyem OK. Hauv qhov rai tshiab, nkag mus rau cov ntaub ntawv pov thawj los txuas rau SQL server.

   

3. Xaiv cov rooj uas yuav tsum tau txuas. Kos lub thawv Txuag password thiab nyem OK. Txuag tus password txhua zaus rau tag nrho peb lub rooj.

   

4. Hauv cov indexes koj yuav tsum xaiv cov hauv qab no:
   - TargetName rau lub rooj dbo_Devices;

   
   
   - NodeName lossis IPAddress rau dbo_RegistrationData;

   
   
   - NodeName lossis IPAddress rau dbo_StatusReport.

   

5. Cia peb hloov npe cov ntxhuav hauv MS Access, uas yog: tshem tawm dbo_ prefix kom DSC tuaj yeem siv tau.

   

6. Ua li cas.

   

7. Txuag cov ntaub ntawv thiab kaw MS Access. Tam sim no peb luam cov txiaj ntsig devices.mdb mus rau DSC server (los ntawm lub neej ntawd hauv C: Program FilesWindowsPowershellDSCService) thiab hloov qhov uas twb muaj lawm nrog nws (yog tias nws muaj).

Configuring DSC Server siv SQL

1. Peb rov qab mus rau DSC server. Txhawm rau txuas rau SQL neeg rau zaub mov nrog peb cov ntaub ntawv npe, cia peb tsim ODBC kev sib txuas tshiab ntawm DSC server. Lub npe, qhov tob me ntsis, thiab kev sib txuas yuav tsum zoo ib yam li thaum tsim cov ntaub ntawv MDB. Koj tuaj yeem luam cov khoom uas twb tau teeb tsa tas lawm.mdb ntawm no.
2. Txhawm rau siv devices.mdb, koj yuav tsum hloov pauv rau web.config ntawm DSC rub server (default yog C: inetpubPSDSCPullServerweb.config):

- rau Windows Server 2012

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

- rau Windows Server 2016

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

Qhov no ua tiav DSC server teeb.

Tshawb xyuas cov haujlwm ntawm DSC server

1. Cia peb kuaj xyuas tias DSC server nkag tau los ntawm lub vev xaib browser.

   

2. Tam sim no cia saib seb DSC rub server puas ua haujlwm raug. Ua li no, xPSDesiredStateConfiguration module suav nrog cov ntawv rubserversetuptests.ps1. Ua ntej khiav tsab ntawv no, koj yuav tsum nruab Powershell module hu ua Pester. Nruab nws Install-Module -Npe Pester.
3. Qhib C:Program FilesWindowsPowerShellModulesxPSDesiredStateConfiguration <module version>DSCPullServerSetupPullServerDeploymentVerificationTest (hauv qhov piv txwv version 8.0.0.0.0).

   

4. Qhib PullServerSetupTests.ps1 thiab tshawb xyuas txoj hauv kev mus rau web.config ntawm DSC server. Txoj kev mus rau web.config, uas yuav txheeb xyuas cov ntawv, yog qhov tseem ceeb hauv xim liab. Yog tias tsim nyog, peb hloov txoj kev no.

   

5. Khiav pullserversetuptests.ps1
   Invoke-Pester.PullServerSetupTests.ps1
   Се работает.

   

6. Hauv SQL Management Studio peb pom tias cov tswv tswj hwm xa cov ntawv ceeb toom mus rau DSC qhia neeg rau zaub mov thiab cov ntaub ntawv xaus rau hauv DSC database ntawm SQL server.

   

Yog tag nrho. Hauv cov ntawv hauv qab no kuv npaj yuav qhia koj yuav ua li cas los tsim cov ntawv ceeb toom ntawm cov ntaub ntawv tau txais, thiab kuv yuav kov cov teeb meem hais txog kev ua txhaum cai thiab scalability.

Tau qhov twg los: www.hab.com