DSCSQLBuildEncrypted.ps1

#requires -Version 5
$StartTime = [System.Diagnostics.Stopwatch]::StartNew()

$computers = 'OHSQL9012'
$OutputPath = 'F:\DSCConfig'
$KeyPath = 'F:\publicKeys'


$cim = New-CimSession -ComputerName $computers
Function check-even($num){[bool]!($num%2)}


Function Get-Cert 
{ 
    Param 
    ( 
        [System.String]$RemoteMachine, 
        [System.String]$SaveLocation = "F:\publicKeys" 
    ) 
    if (!(test-path $SaveLocation))
    {
        new-item -path $SaveLocation -type Directory
    }
    $CertStore = New-Object System.Security.Cryptography.X509Certificates.X509Store -ArgumentList  "\\$($RemoteMachine)\My", "LocalMachine" 
    $CertStore.Open('ReadOnly') 
    $certificate  = $CertStore.Certificates | Where-Object {$_.EnhancedKeyUsageList.friendlyName -eq "Document Encryption"} 
    [byte[]]$Bytes  = $certificate.Export('Cert') 
    [string]$SaveLiteralPath = "$SaveLocation\$RemoteMachine.$env:UserDNSDomain.cer" 
    Remove-Item -Path $SaveLiteralPath -Force -ErrorAction Ignore 
    Set-Content -Path $SaveLiteralPath -Value $Bytes -Encoding Byte -Force | out-null 
}

foreach ($computer in $computers)
{
    Get-Cert -RemoteMachine $computer -SaveLocation $KeyPath
}

Get-cert -RemoteMachine $env:COMPUTERNAME -SaveLocation $KeyPath

[DSCLocalConfigurationManager()]
Configuration LCM_Reboot_CentralConfig 
{    
    Param(
        [string[]]$ComputerName
    )
    Node $computers
    {
        Settings
        {
            ConfigurationID                = $GUID
            CertificateID                  =(Get-PfxCertificate -FilePath "$KeyPath\$computers.$env:USERDNSDOMAIN.cer").Thumbprint
            RefreshFrequencyMins           = 30
            ConfigurationModeFrequencyMins = 15
            RefreshMode                    = "Push"
            AllowModuleOverwrite           = $true 
            RebootNodeIfNeeded = $True    
            ConfigurationMode = 'ApplyAndAutoCorrect'
        }  
    }
}
#LCM_Reboot_CentralConfig -OutputPath $OutputPath

foreach ($computer in $computers)
{
    $GUID = (New-Guid).Guid
    LCM_Reboot_CentralConfig -ComputerName $Computer -OutputPath $OutputPath 
    Set-DSCLocalConfigurationManager -Path $OutputPath  -CimSession $cim –Verbose
}

Configuration SQLBuild
{
    Import-DscResource –Module PSDesiredStateConfiguration
    Import-DscResource -Module xSQLServer
   
    Node $AllNodes.NodeName
    {
        LocalConfigurationManager
        {
            CertificateId = $Node.Thumbprint
        }

        WindowsFeature "NET"
        {
            Ensure = "Present"
            Name = "NET-Framework-Core"
            Source = $Node.NETPath 
        }

        if($Features -ne "")
        {
           xSqlServerSetup ($Node.NodeName)
           {
               DependsOn = '[WindowsFeature]NET'
               SourcePath = $Node.SourcePath
               SetupCredential = $Node.InstallerServiceAccount
               InstanceName = $Node.InstanceName
               Features = $Features
               SQLSysAdminAccounts = $Node.AdminAccount
               InstallSharedDir = "G:\Program Files\Microsoft SQL Server"
               InstallSharedWOWDir = "G:\Program Files (x86)\Microsoft SQL Server"
               InstanceDir = "G:\Program Files\Microsoft SQL Server"
               InstallSQLDataDir = "G:\MSSQL\Data"
               SQLUserDBDir = "G:\MSSQL\Data"
               SQLUserDBLogDir = "L:\MSSQL\Data"
               SQLTempDBDir = "T:\MSSQL\Data"
               SQLTempDBLogDir = "L:\MSSQL\Data"
               SQLBackupDir = "G:\MSSQL\Backup"
           }
           xSqlServerFirewall ($Node.NodeName)
           {
              SourcePath = $Node.SourcePath
              InstanceName = $Node.InstanceName
              Features = $Node.Features
           
              DependsOn = ("[xSqlServerSetup]" + $Node.NodeName)
           }

           xSQLServerMemory ($Node.Nodename)
           {
               Ensure = "Present"
               DynamicAlloc = $True
           
               DependsOn = ("[xSqlServerSetup]" + $Node.NodeName)
           }
           xSQLServerMaxDop($Node.Nodename)
           {
               Ensure = "Present"
               DynamicAlloc = $true
           
               DependsOn = ("[xSqlServerSetup]" + $Node.NodeName)     
           }
        }
    }
}

$ConfigurationData = @{
    AllNodes = @(
        @{
            NodeName = "*"
            PSDscAllowPlainTextPassword = $false
            PSDscAllowDomainUser =$true
            NETPath = "\\ohdc9000\SQLBuilds\SQLAutoInstall\WIN2012R2\sxs"
            SourcePath = "\\ohdc9000\SQLAutoBuilds\SQL2014"
            InstallerServiceAccount = Get-Credential -UserName CORP\AutoSvc -Message "Credentials to Install SQL Server"
            AdminAccount = "CORP\user1"  
            # For build server encryption
            CertificateFile =(Get-PfxCertificate -FilePath "$KeyPath\$env:COMPUTERNAME.$env:USERDNSDOMAIN.cer").Thumbprint 
        }

    )
}

ForEach ($computer in $computers) {
            $ConfigurationData.AllNodes += @{
            NodeName        = $computer
            InstanceName    = "MSSQLSERVER"
            Features        = "SQLENGINE,IS,SSMS,ADV_SSMS"     
            CertificateFile = "$KeyPath\$computer.$env:USERDNSDOMAIN.cer"
            Thumbprint = (Get-PfxCertificate -FilePath "$KeyPath\$computer.$env:USERDNSDOMAIN.cer").Thumbprint    
            }
    
    
   $Destination = "\\"+$computer+"\\c$\Program Files\WindowsPowerShell\Modules"
   if (Test-Path "$Destination\xSqlServer"){Remove-Item -Path "$Destination\xSqlServer"-Recurse -Force}
   Copy-Item 'C:\Program Files\WindowsPowerShell\Modules\xSqlServer' -Destination $Destination -Recurse -Force
}


SQLBuild -ConfigurationData $ConfigurationData -OutputPath $OutputPath

Workflow StartConfigs 
{ 
    param([string[]]$computers,
        [System.string] $Path)
 
    foreach –parallel ($Computer in $Computers) 
    {   
        Start-DscConfiguration -ComputerName $Computer -Path $Path -Verbose -Wait -Force
    }
}

StartConfigs -Computers $computers -Path $OutputPath

#Ttest
<#
Workflow TestConfigs 
{ 
    param([string[]]$computers)
    foreach -parallel ($Computer in $Computers) 
    {
        Write-verbose "$Computer :"
        test-dscconfiguration -ComputerName $Computer
    }
}

TestConfigs -computers $computers
#>

$StartTime.Elapsed