PowerShell DSC resource MSFT_xSQLServerSetup failed to execute Set-TargetResource functionality with error message: Test-TargetResource returned false after calling set. about sqlserverdsc HOT 22 CLOSED

xSQLServerSetup InstallSqlServer
{
    InstanceName = $Node.SqlInstanceName
    Features= $Node.SqlInstallFeatures
    BrowserSvcStartupType = 'Automatic'
    SQLCollation = 'Latin1_General_CI_AS_KS_WS'
    SQLSvcAccount = $SqlServiceCredentialNode2
    AgtSvcAccount = $SqlAgentServiceCredentialNode2
    SQLSysAdminAccounts = $SqlAdministratorCredential.UserName
    SetupCredential = $SqlInstallCredential

    SourcePath = $Node.SourcePath
    SourceFolder = $Node.SourceFolder
    SourceCredential = $SqlInstallCredential

    UpdateSource = '.\Updates'
    UpdateEnabled = 'True'

    SuppressReboot = $False
    ForceReboot = $False
}

        WindowsFeature NET35 {
           Name = 'NET-Framework-Core'
           Source = $node.WindowsSourceSxs
           Ensure = 'Present'
        }

        WindowsFeature NET45 {
           Name = 'NET-Framework-45-Core'
           Ensure = 'Present'
        }

$config = @{
  AllNodes = @(
        #@{NodeName = "Server1";PsDscAllowPlaintextPassword = $true}
       

   )
}
configuration TestScript {

PARAM ( 
        [PSCredential]$SACredential,
        [string]$ModuleSourcePath,
        [string]$ModuleDestinationPath,
        [string]$NetSourcePath,
        #[string]$InstanceName,
        [PSCredential]$ShareCredential,
        [PSCredential]$SourceCredential,
        [PSCredential]$Cred,
        [PSCredential]$Setupcredential,
        [PSCredential]$AgtSvcAccount,
        [PSCredential]$SQLSvcAccount,
       
        #[PSCredential]$Username, # = "domain\username",
        [PSCredential]$Password # = (Get-Content c:\Temp\password.txt | ConvertTo-SecureString)
        #[PSCredential]$AGsvcAccount,
        #[PSCredential]$DEsvcAccount
 
     )



Import-DscResource -Module xSQLServer,PSDesiredStateConfiguration, xActiveDirectory,xstorage

$AGsvcAccount = $SQLServer + "AG"
$DEsvcAccount = $SQLServer + "DE"
$FTSvcAccount = $SQLServer + "FT"
$ASSvcAccount = $SQLServer + "AS"
$ISSvcAccount = $SQLServer + "IS"
$RSSvcAccount = $SQLServer + "RS"
$ASSvcAccount = $SQLServer + "AS"
       
       
$SQLSvcAccountUsername = $DomainName + "\" + $DEsvcAccount
$AgtSvcAccountUsername = $DomainName + "\" + $AGsvcAccount
$FTSvcAccountUsernamee = $DomainName + "\" + $FTSvcAccount
$RSSvcAccountUsername = $DomainName + "\" + $RSSvcAccount
$ISSvcAccountUsername = $DomainName + "\" + $ISSvcAccount
$ASSvcAccountUsername = $DomainName + "\" + $ASSvcAccount   

    
$InstanceName = "BAUTEST4" #fill in the name of the instance if not default
$SQLServer = $env:computername
$DomainName = $env:USERDOMAIN






Node $Allnodes.Nodename {



        Log ParamLog
        {
            Message = "Running SQLInstall. PackagePath = $PackagePath"
        }

  Script ShowUser
        {
            GetScript = { }
            TestScript = { $False }
            SetScript = 
            {
            write-verbose "User: $([System.Security.Principal.WindowsIdentity]::GetCurrent().Name)"
            write-verbose "Current User: $env:UserName"
            }
        }

   File ModuleCopy
        {
            Ensure = "Present"  # You can also set Ensure to "Absent"
            Type = "Directory" # Default is "File".
            Recurse = $true # Ensure presence of subdirectories, too
            SourcePath = $ModuleSourcePath
            DestinationPath = $ModuleDestinationPath 
            Dependson = "[Script]ShowUser"  
        }




   WindowsFeature WindowsPowerShell50 
		{
			Ensure = 'Present' 
			Name = 'PowerShell'
            Dependson = "[File]ModuleCopy"
            PsDscRunAsCredential = $cred
		}

		
  WindowsFeature WindowsPowerShellISE 
		{
			Ensure = 'Present' 
			Name = 'PowerShell-ISE'
            Dependson = "[WindowsFeature]WindowsPowerShell50"
            PsDscRunAsCredential = $cred
		}


WindowsFeature ActiveDirectoryModuleForWindowsPowerShell 
		{
			Ensure = 'Present' 
			Name = 'RSAT-AD-PowerShell'
            Dependson = "[WindowsFeature]WindowsPowerShellISE"
            PsDscRunAsCredential = $cred
		}

 


  xADUser $AGsvcAccount
		{
			#DomainAdministratorCredential = $cred
			DomainName = $DomainName
			UserName =  $AGsvcAccount
			Password = $password
			PasswordNeverExpires = $true
			Ensure = 'Present'
            Path = 'OU=Service Logons - non-compliant,OU=IT,DC=domain,DC=com'
            PsDscRunAsCredential = $cred
            Dependson = "[WindowsFeature]ActiveDirectoryModuleForWindowsPowerShell"
            Enabled = $true
		}

  xADUser $DEsvcAccount
		{
			#DomainAdministratorCredential = $cred
			DomainName = $DomainName
			UserName =  $DEsvcAccount
			Password = $password
			PasswordNeverExpires = $true
			Ensure = 'Present'
            Path = 'OU=Service Logons - non-compliant,OU=IT,DC=domain,DC=com'
            PsDscRunAsCredential = $cred
            Dependson = "[xADUser]$AGsvcAccount"
            Enabled = $true
		}



  xMountImage OSImage
        {
            ImagePath = "\\Share\SQL Team\SQLInstalls\SW_DVD9_Windows_Svr_Std_and_DataCtr_2012_R2_64Bit_English_-4_MLF_X19-82891.ISO"
            #Name = "Win2012R2"
            DependsOn = "[File]ModuleCopy"
            DriveLetter = "O:"
            Ensure = "Present"
            #PsDscRunAsCredential = $cred
        }



  xMountImage SQLImage
        {
            ImagePath = "\\Share\SQL Team\SQLInstalls\SQL Server 2016 Developer\en_sql_server_2016_developer_x64_dvd_8777069.iso"
            #Name = "SQL2016"
            DependsOn = "[xMountImage]OSImage"
            DriveLetter = "K:"
            Ensure = "Present"
            #PsDscRunAsCredential = $cred
        }

       
 
 WindowsFeature NetFramework35Core
        {
            Name = "NET-Framework-Core"
            Ensure = "Present"
            Source = "O:\sources\sxs"
            DependsOn = "[xMountImage]OSImage"
        }
 
 WindowsFeature NetFramework45Core
        {
            Name = "NET-Framework-45-Core"
            Ensure = "Present"
            Source = "O:\sources\sxs"
            DependsOn = "[WindowsFeature]NetFramework35Core"
        }

        # Install SqlServer using ini file
       
   <#    
      Script InstallSQL
        {

             GetScript =
            {
               # Do Nothing
                
            }

             SetScript = {
             $currentTime = Get-Date
             $InstallSQL = C:\DBA\SQLDSC\SQLInstaller.ps1

            }

          TestScript = {
            
                $false
            }
            Dependson = "[xMountImage]SQLImage"

        }


      
#>
 xSQLServersetup SQL2016

     {
        InstanceName = $InstanceName 
        Setupcredential = $Setupcredential
        SApwd = $SACredential
        SourcePath = 'K:'
        SourceFolder = ""
        FEATURES="SQLENGINE,RS,IS,SQL" #FullText,SNAC_SDK,Conn,DQ,BC,DQC,Replication,MDS,Tools
        InstallSharedDir = "C:\Program Files"
        InstallSharedWOWDir = "C:\Program Files (x86)"
        InstanceDir = "D:\Program Files\Microsoft SQL Server"
        InstallSQLDataDir = "H:\SQLData"
        SQLUserDBDir = "H:\SQLData"
        SQLUserDBLogDir = "L:\SQLLogs"
        SQLTempDBDir = "H:\SQLTemp\$InstanceName"
        SQLTempDBLogDir = "L:\SQLTempLog\$InstanceName"
        SQLBackupDir = "H:\SQLData"
        ASDataDir = "C:\Program Files"
        ASLogDir = "C:\Program Files"
        ASBackupDir = "C:\Program Files"
        ASTempDir = "C:\Program Files"
        ASConfigDir = "C:\Program Files"
        UpdateSource = "MU" # set this to windows update, otherwise it will use the default and cause the setup to crash
        UpdateEnabled = 'True'
        SuppressReboot = $False
        ForceReboot = $False
        PsDscRunAsCredential = $cred
        FTSvcAccount = $cred
        ASSvcAccount = $cred
        ASSysAdminAccounts = $cred
        ISSvcAccount = $cred
        RSSvcAccount = $cred
        ASCollation = 'Latin1_General_CI_AS_KS_WS'
        SQLSvcAccount = $SQLSvcAccountCred
        SQLCollation = 'Latin1_General_CI_AS_KS_WS'
        AGTSVCACCOUNT= $AgtSvcAccountCred
        SQLSysAdminAccounts = "domain\dl_sqladmins"
        #PID =
        Dependson = "[WindowsFeature]NetFramework45Core"
     }


Script InstallSSMS
        {

             GetScript =
            {
                # Do Nothing 
                
            }

            SetScript = 
            {
            $currentTime = Get-Date
            $InstallSSMS = C:\DBA\SQLDSC\SSMSInstaller2.ps1  

            }

            TestScript = 
            {
            
                $false
            }
            Dependson = "[xSQLServersetup]SQL2016"

        }


         # Set LCM to reboot if needed
 LocalConfigurationManager
        {
            DebugMode = "All"
            RebootNodeIfNeeded = $true
            ConfigurationMode = 'ApplyAndAutoCorrect'
            AllowModuleOverwrite = $true
            RefreshMode = 'Push'
            ActionAfterReboot = 'ContinueConfiguration'
           
        }
  }
        
    }

    

#Read-Host –AsSecureString | ConvertFrom-SecureString | Out-File C:\DBA\SQLDSC\SACredential.txt
#Read-Host –AsSecureString | ConvertFrom-SecureString | Out-File C:\DBA\SQLDSC\password.txt
#Read-Host –AsSecureString | ConvertFrom-SecureString | Out-File C:\DBA\SQLDSC\AgtSvcAccountpassword.txt
#Read-Host –AsSecureString | ConvertFrom-SecureString | Out-File C:\DBA\SQLDSC\SQLSvcAccountpassword.txt


$Username = "domain\domain\username"
$password = Get-Content C:\DBA\SQLDSC\password.txt | ConvertTo-SecureString
$SApassword = Get-Content C:\DBA\SQLDSC\SACredential.txt | ConvertTo-SecureString
$Cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $Username, $password
#$InstanceName = "SharePoint" #fill in the name of the instance if not default


#$AgtSvcAccountUsername = $AgtSvcAccountUsername
#$SQLSvcAccountUsername = $SQLSvcAccountUsername
$AgtSvcAccountpassword = Get-Content C:\DBA\SQLDSC\AgtSvcAccountpassword.txt | ConvertTo-SecureString
$SQLSvcAccountpassword = Get-Content C:\DBA\SQLDSC\SQLSvcAccountpassword.txt | ConvertTo-SecureString
$AgtSvcAccountCred = new-object -typename System.Management.Automation.PSCredential -argumentlist $AgtSvcAccountUsername, $AgtSvcAccountpassword
$SQLSvcAccountCred = new-object -typename System.Management.Automation.PSCredential -argumentlist $SQLSvcAccountUsername, $SQLSvcAccountpassword



                      $values = @{
                                "Setupcredential" =$cred;
                                "ShareCredential" = $cred;
                                "SACredential" = $cred;
                                "SourceCredential" = $cred;
                                "ModuleSourcePath" = "\\Share\SQL Team\SQLInstalls\SQLInstaller\Modules";
                                "ModuleDestinationPath" = "C:\Program Files\WindowsPowerShell\Modules";
                                "NetSourcePath" = "O:\sources";
                                #"InstanceName" = 'BAU'
                                #"AGTSVCACCOUNT=" = $AgtSvcAccountCred;
                                "SQLSvcAccount" = $cred
                                };

CD C:\DBA\SQLDSC

Testscript @values -configurationdata $config
Start-DscConfiguration  -Path C:\DBA\SQLDSC\TestScript -Credential (Get-Credential -UserName "domain\domain\username" -Message 'Enter Credential for configuration') -Wait -Verbose -force

VERBOSE: [Server1]:                            [[xSQLServerSetup]SQL2016::[TestScript]::[TestScript]] Importing function 'GetxPDTVariable'.
VERBOSE: [Server1]:                            [[xSQLServerSetup]SQL2016::[TestScript]::[TestScript]] Importing function 'NetUse'.
VERBOSE: [Server1]:                            [[xSQLServerSetup]SQL2016::[TestScript]::[TestScript]] Importing function 'ResolvePath'.
VERBOSE: [Server1]:                            [[xSQLServerSetup]SQL2016::[TestScript]::[TestScript]] Importing function 'StartWin32Process'.
VERBOSE: [Server1]:                            [[xSQLServerSetup]SQL2016::[TestScript]::[TestScript]] Importing function 'WaitForWin32ProcessEnd'.
VERBOSE: [Server1]:                            [[xSQLServerSetup]SQL2016::[TestScript]::[TestScript]] Path: K:\setup.exe
VERBOSE: [Server1]:                            [[xSQLServerSetup]SQL2016::[TestScript]::[TestScript]] Test-TargetResource returned false after calling set. 
| ErrorType: MSFT_xSQLServerSetup.TestFailedAfterSet
VERBOSE: [Server1]: LCM:  [ End    Set      ]  [[xSQLServerSetup]SQL2016::[TestScript]::[TestScript]]  in 54.4550 seconds.
PowerShell DSC resource MSFT_xSQLServerSetup  failed to execute Set-TargetResource functionality with error message: Test-TargetResource returned false after 
calling set. 
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : ProviderOperationExecutionFailure
    + PSComputerName        : Server1
 
VERBOSE: [Server1]: LCM:  [ End    Set      ]
The SendConfigurationApply function did not succeed.
    + CategoryInfo          : NotSpecified: (root/Microsoft/...gurationManager:String) [], CimException
    + FullyQualifiedErrorId : MI RESULT 1
    + PSComputerName        : Server1
 
VERBOSE: Operation 'Invoke CimMethod' complete.
VERBOSE: Time taken for configuration job to complete is 65.472 seconds

Overall summary:
  Final result:                  Failed: see details below
  Exit code (Decimal):           -2061762559
  Exit facility code:            1308
  Exit error code:               1
  Exit message:                  The credentials you provided for the 'SQLAgent$BAUTEST4' service is invalid.
  Start time:                    2017-01-10 15:58:04
  End time:                      2017-01-10 15:58:44
  Requested action:              Install

Setup completed with required actions for features.
Troubleshooting information for those features:
  Next step for SQLEngine:       SQL Server Setup was canceled before completing the operation. Try the setup process again.
  Next step for Replication:     SQL Server Setup was canceled before completing the operation. Try the setup process again.
  Next step for FullText:        SQL Server Setup was canceled before completing the operation. Try the setup process again.
  Next step for DQ:              SQL Server Setup was canceled before completing the operation. Try the setup process again.
  Next step for RS:              SQL Server Setup was canceled before completing the operation. Try the setup process again.

Install Summary:


Package properties:
  Description:                   Microsoft SQL Server 2016 
  ProductName:                   SQL Server 2016
  Type:                          RTM
  Version:                       13
  SPLevel:                       0
  Installation location:         K:\x64\setup\
  Installation edition:          Developer

Product Update Status:
  None discovered.

User Input Settings:
  ACTION:                        Install
  ADDCURRENTUSERASSQLADMIN:      false
  AGTSVCACCOUNT:                 domain\server1AG
  AGTSVCPASSWORD:                *****
  AGTSVCSTARTUPTYPE:             Automatic
  ASBACKUPDIR:                   Backup
  ASCOLLATION:                   Latin1_General_CI_AS
  ASCONFIGDIR:                   Config
  ASDATADIR:                     Data
  ASLOGDIR:                      Log
  ASPROVIDERMSOLAP:              1
  ASSERVERMODE:                  MULTIDIMENSIONAL
  ASSVCACCOUNT:                  <empty>
  ASSVCPASSWORD:                 <empty>
  ASSVCSTARTUPTYPE:              Automatic
  ASSYSADMINACCOUNTS:            <empty>
  ASTELSVCACCT:                  <empty>
  ASTELSVCPASSWORD:              <empty>
  ASTELSVCSTARTUPTYPE:           0
  ASTEMPDIR:                     Temp
  BROWSERSVCSTARTUPTYPE:         Disabled
  CLTCTLRNAME:                   <empty>
  CLTRESULTDIR:                  <empty>
  CLTSTARTUPTYPE:                0
  CLTSVCACCOUNT:                 <empty>
  CLTSVCPASSWORD:                <empty>
  CLTWORKINGDIR:                 <empty>
  COMMFABRICENCRYPTION:          0
  COMMFABRICNETWORKLEVEL:        0
  COMMFABRICPORT:                0
  CONFIGURATIONFILE:             
  CTLRSTARTUPTYPE:               0
  CTLRSVCACCOUNT:                <empty>
  CTLRSVCPASSWORD:               <empty>
  CTLRUSERS:                     <empty>
  ENABLERANU:                    false
  ENU:                           true
  EXTSVCACCOUNT:                 <empty>
  EXTSVCPASSWORD:                <empty>
  FEATURES:                      SQLENGINE, REPLICATION, FULLTEXT, DQ, RS
  FILESTREAMLEVEL:               0
  FILESTREAMSHARENAME:           <empty>
  FTSVCACCOUNT:                  NT Service\MSSQLFDLauncher$BAUTEST4
  FTSVCPASSWORD:                 <empty>
  HELP:                          false
  IACCEPTROPENLICENSETERMS:      false
  IACCEPTSQLSERVERLICENSETERMS:  true
  INDICATEPROGRESS:              false
  INSTALLSHAREDDIR:              C:\Program Files\Microsoft SQL Server\
  INSTALLSHAREDWOWDIR:           C:\Program Files (x86)\Microsoft SQL Server\
  INSTALLSQLDATADIR:             H:\SQLData
  INSTANCEDIR:                   D:\Program Files\Microsoft SQL Server
  INSTANCEID:                    BAUTEST4
  INSTANCENAME:                  BAUTEST4
  ISSVCACCOUNT:                  NT AUTHORITY\Network Service
  ISSVCPASSWORD:                 <empty>
  ISSVCSTARTUPTYPE:              Automatic
  ISTELSVCACCT:                  <empty>
  ISTELSVCPASSWORD:              <empty>
  ISTELSVCSTARTUPTYPE:           0
  MATRIXCMBRICKCOMMPORT:         0
  MATRIXCMSERVERNAME:            <empty>
  MATRIXNAME:                    <empty>
  MRCACHEDIRECTORY:              
  NPENABLED:                     0
  PBDMSSVCACCOUNT:               <empty>
  PBDMSSVCPASSWORD:              <empty>
  PBDMSSVCSTARTUPTYPE:           0
  PBENGSVCACCOUNT:               <empty>
  PBENGSVCPASSWORD:              <empty>
  PBENGSVCSTARTUPTYPE:           0
  PBPORTRANGE:                   <empty>
  PBSCALEOUT:                    false
  PID:                           *****
  QUIET:                         true
  QUIETSIMPLE:                   false
  ROLE:                          
  RSINSTALLMODE:                 DefaultNativeMode
  RSSHPINSTALLMODE:              DefaultSharePointMode
  RSSVCACCOUNT:                  NT Service\ReportServer$BAUTEST4
  RSSVCPASSWORD:                 <empty>
  RSSVCSTARTUPTYPE:              Automatic
  SAPWD:                         <empty>
  SECURITYMODE:                  <empty>
  SQLBACKUPDIR:                  H:\SQLData
  SQLCOLLATION:                  Latin1_General_CI_AS_KS_WS
  SQLSVCACCOUNT:                 domain\server1DE
  SQLSVCINSTANTFILEINIT:         false
  SQLSVCPASSWORD:                *****
  SQLSVCSTARTUPTYPE:             Automatic
  SQLSYSADMINACCOUNTS:           domain\user1,domain\dl_sqladmins
  SQLTELSVCACCT:                 NT Service\SQLTELEMETRY$BAUTEST4
  SQLTELSVCPASSWORD:             <empty>
  SQLTELSVCSTARTUPTYPE:          Automatic
  SQLTEMPDBDIR:                  H:\SQLTemp\BAUTEST4
  SQLTEMPDBFILECOUNT:            8
  SQLTEMPDBFILEGROWTH:           64
  SQLTEMPDBFILESIZE:             8
  SQLTEMPDBLOGDIR:               L:\SQLTempLog\BAUTEST4
  SQLTEMPDBLOGFILEGROWTH:        64
  SQLTEMPDBLOGFILESIZE:          8
  SQLUSERDBDIR:                  H:\SQLData
  SQLUSERDBLOGDIR:               L:\SQLLogs
  SUPPRESSPRIVACYSTATEMENTNOTICE: false
  TCPENABLED:                    0
  UIMODE:                        Normal
  UpdateEnabled:                 true
  UpdateSource:                  MU
  USEMICROSOFTUPDATE:            false
  X86:                           false

#"AGTSVCACCOUNT=" = $AgtSvcAccountCred;

[PSCredential]$AgtSvcAccount,

[PSCredential]$AgentServiceAccountCredentials,

AGTSVCACCOUNT= $AgtSvcAccountCred

AGTSVCACCOUNT= $AgentServiceAccountCredentials