Powershell DSC - xSQLServer -- xSQLServerSetup error. RRS feed

  • Question

  • Hi All,

    I've been trying to automate the installation of SQL Server using the experiment DSC Module for SQL Server.  This issue occurs in my vagrant environments and vCenter environments.

    This is the entirety of the script that does the meat and potatoes of the install.

    #use xSQLServerSetup of xSQLServer
    #sql install error log can be found at C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\log
    param (
        [string]$SetupCredA = $(throw "error -SetupCredA is required.  Need installer username."),
        [string]$SetupCredPW = $(throw "error -SetupCredPW is required.  Need installer password."),
    	[string]$SvcAccountCredA = $(throw "error -SvcAccountCred is required.  Need to know service account."),
        [string]$SvcAccountPW = $(throw "error -SvcAccountCred is required.  Need to know service account pw."),
        [string]$Features = $(throw "error -Features is required."),	
        [string]$SAArray = $(throw "error -SAArray is required.  Need to know sysadmins.")
    $SetupCred=New-Object System.Management.Automation.PSCredential ($SetupCredA, $(ConvertTo-SecureString $SetupCredPW -AsPlainText -Force))
    $SvcAccountCred=New-Object System.Management.Automation.PSCredential ($SvcAccountCredA, $(ConvertTo-SecureString $SvcAccountPW -AsPlainText -Force))
    switch ($Features) {
        "ServerCoreFeatures" { $Features = $ServerCoreFeatures }
        "ServerGUIFeatures" { $Features = $ServerGUIFeatures }
        "ServerGUIFeaturesWithReporting" { $Features = $ServerGUIFeaturesWithReporting }
        "ReportServerFeatures" { $Features = $ReportServerFeatures }
    $configData = @{
        AllNodes = @(
                    NodeName = "$($env:computername)"
                    PSDscAllowPlainTextPassword = $true
    Configuration SetupSQL
        Import-DSCResource -ModuleName xSQLServer
        Node "$($env:computername)"
            xSQLServerSetup Install-SQL
                SourcePath = $Extract
                SourceFolder = "SQL2014"
                SetupCredential = $SetupCred
                SQLSvcAccount = $SvcAccountCred
                AgtSvcAccount = $SvcAccountCred
                SQLSysAdminAccounts = $SAArray
                UpdateEnabled = "False"
                UpdateSource = "$Extract\SQL2014\Updates"
                ErrorReporting = "True"
                SQLUserDBDir = "M:\Data"
                SQLUserDBLogDir = "L:\Log"
                SQLTempDBDir = "T:\TempDB"
                SQLTempDBLogDir = "T:\TempLog"
                SQLBackupDir = "M:\Backup"
                InstanceName= "MSSQLSERVER"
                Features = $Features
    SetupSQL -ConfigurationData $configData
    Start-DscConfiguration .\SetupSQL -force -wait -verbose

    The environment is prestaged with

    $Modules="C:\Program Files\WindowsPowerShell\Modules"
    mkdir $Extract -force
    write-host "$(get-date) Downloading install resources"
    start-bitstransfer "http://downloads.yosemite.local/files/application/microsoft/sqlinstall/dscrk9.zip" $ResourceKit
    start-bitstransfer "http://downloads.yosemite.local/files/application/microsoft/sqlinstall/sxsfiles.zip" $WinSXSFiles
    start-bitstransfer "http://downloads.yosemite.local/files/application/microsoft/sqlinstall/sql2014.zip" $SQLInstall
    write-host "$(get-date) Download completed"
    Configuration PreStageSQL
        Archive Extract-Resource-Kits
            Ensure = "Present"
            Path = $ResourceKit
            Destination = $Extract        
        Archive Extract-WinSXS-Files
            Ensure = "Present"
            Path = $WinSXSFiles
            Destination = $Extract
            DependsOn = "[Archive]Extract-Resource-Kits"
    	Archive Extract-SQL-Files
            Ensure = "Present"
            Path = $SQLInstall
            Destination = $Extract
            DependsOn = "[Archive]Extract-WinSXS-Files"
    	File Move-Resource-Files
           SourcePath = "$Extract\All Resources"
           DestinationPath = $Modules
           Ensure = "Present"
           Type = "Directory"
           Recurse = $True
           MatchSource = $True
           DependsOn = "[Archive]Extract-SQL-Files"
    	WindowsFeature Install-NET35
            Name = "NET-Framework-Core"
            Source = "$Extract\sxs"
            Ensure = "Present"
            DependsOn = "[File]Move-Resource-Files"
    Start-DscConfiguration .\PreStageSQL -force -wait -verbose
    write-host "$(get-date) completed"

    The setup errors out at the end with the following messages in the console/event log.  The SQL Install itself appears to be complete.  I've tried this with UpdateEnabled = "true" as well and it errors at the same location.

    So the install appears to complete successfully but powershell reports an error

    'C:\InstallSQL\SQL2014\setup.exe' started in process ID 192
    VERBOSE: [WINDOWS2012R2]:                            [[xSQLServerSetup]Install-SQL] Importing function 'NetUse'.
    VERBOSE: [WINDOWS2012R2]:                            [[xSQLServerSetup]Install-SQL] Importing function 'ResolvePath'.
    VERBOSE: [WINDOWS2012R2]:                            [[xSQLServerSetup]Install-SQL] Importing function
    VERBOSE: [WINDOWS2012R2]:                            [[xSQLServerSetup]Install-SQL] Importing function
    VERBOSE: [WINDOWS2012R2]:                            [[xSQLServerSetup]Install-SQL] Path:
    VERBOSE: [WINDOWS2012R2]: LCM:  [ End    Set      ]  [[xSQLServerSetup]Install-SQL]  in 500.8120 seconds.
    PowerShell DSC resource MSFT_xSQLServerSetup  failed to execute Set-TargetResource functionality with error message:
    Set-TargetResouce failed
        + CategoryInfo          : InvalidOperation: (:) [], CimException
        + FullyQualifiedErrorId : ProviderOperationExecutionFailure
        + PSComputerName        : WINDOWS2012R2
    The SendConfigurationApply function did not succeed.
        + CategoryInfo          : NotSpecified: (root/Microsoft/...gurationManager:String) [], CimException
        + FullyQualifiedErrorId : MI RESULT 1
        + PSComputerName        : WINDOWS2012R2
    VERBOSE: Operation 'Invoke CimMethod' complete.
    VERBOSE: Time taken for configuration job to complete is 501.455 seconds

    With the following errors in event viewer.  

    Job {5E0C5C09-B7B2-11E4-80B6-000C29F93310} : 
    Message Set-TargetResouce failed 
    HResult -2146233087 
    StackTrack    at System.Management.Automation.Interpreter.ThrowInstruction.Run(InterpretedFrame frame)
       at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
    Job {5E0C5C09-B7B2-11E4-80B6-000C29F93310} : 
    This event indicates that failure happens when LCM is processing the configuration. ErrorId is 0x1. ErrorDetail is The SendConfigurationApply function did not succeed.. ResourceId is [xSQLServerSetup]Install-SQL and SourceInfo is C:\InstallSQL\InstallSQL.ps1::41::9::xSQLServerSetup. ErrorMessage is PowerShell DSC resource MSFT_xSQLServerSetup  failed to execute Set-TargetResource functionality with error message: Set-TargetResouce failed .
    Job {5E0C5C09-B7B2-11E4-80B6-000C29F93310} : 
    DSC Engine Error : 
    	 Error Message The SendConfigurationApply function did not succeed. 
    	Error Code : 1 
    However everything from the SQL install summary appears to have been created.

    Overall summary:
      Final result:                  Passed
      Exit code (Decimal):           0
      Start time:                    2015-02-18 21:09:08
      End time:                      2015-02-18 21:17:01
      Requested action:              Install
    Machine Properties:
      Machine name:                  WINDOWS2012R2
      Machine processor count:       2
      OS version:                    Windows Server 2012
      OS service pack:               
      OS region:                     United States
      OS language:                   English (United States)
      OS architecture:               x64
      Process architecture:          64 Bit
      OS clustered:                  No
    Product features discovered:
      Product              Instance             Instance ID                    Feature                                  Language             Edition              Version         Clustered  Configured
    Package properties:
      Description:                   Microsoft SQL Server 2014 
      ProductName:                   SQL Server 2014
      Type:                          RTM
      Version:                       12
      SPLevel:                       0
      Installation location:         C:\InstallSQL\SQL2014\x64\setup\
      Installation edition:          Enterprise Edition: Core-based Licensing
    Product Update Status:
      None discovered.
    User Input Settings:
      ACTION:                        Install
      AGTSVCACCOUNT:                 Administrator
      AGTSVCPASSWORD:                *****
      AGTSVCSTARTUPTYPE:             Automatic
      ASBACKUPDIR:                   C:\Program Files\Microsoft SQL Server\MSAS12.MSSQLSERVER\OLAP\Backup
      ASCOLLATION:                   Latin1_General_CI_AS
      ASCONFIGDIR:                   C:\Program Files\Microsoft SQL Server\MSAS12.MSSQLSERVER\OLAP\Config
      ASDATADIR:                     C:\Program Files\Microsoft SQL Server\MSAS12.MSSQLSERVER\OLAP\Data
      ASLOGDIR:                      C:\Program Files\Microsoft SQL Server\MSAS12.MSSQLSERVER\OLAP\Log
      ASPROVIDERMSOLAP:              1
      ASSVCACCOUNT:                  NT Service\MSSQLServerOLAPService
      ASSVCPASSWORD:                 <empty>
      ASSVCSTARTUPTYPE:              Automatic
      ASSYSADMINACCOUNTS:            Administrator
      ASTEMPDIR:                     C:\Program Files\Microsoft SQL Server\MSAS12.MSSQLSERVER\OLAP\Temp
      BROWSERSVCSTARTUPTYPE:         Disabled
      CLTRESULTDIR:                  C:\Program Files (x86)\Microsoft SQL Server\DReplayClient\ResultDir\
      CLTSTARTUPTYPE:                Manual
      CLTSVCACCOUNT:                 NT Service\SQL Server Distributed Replay Client
      CLTSVCPASSWORD:                <empty>
      CLTWORKINGDIR:                 C:\Program Files (x86)\Microsoft SQL Server\DReplayClient\WorkingDir\
      COMMFABRICPORT:                0
      CONFIGURATIONFILE:             C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Log\20150218_210907\ConfigurationFile.ini
      CTLRSTARTUPTYPE:               Manual
      CTLRSVCACCOUNT:                NT Service\SQL Server Distributed Replay Controller
      CTLRSVCPASSWORD:               <empty>
      ENABLERANU:                    false
      ENU:                           true
      ERRORREPORTING:                true
      FILESTREAMLEVEL:               0
      FILESTREAMSHARENAME:           <empty>
      FTSVCACCOUNT:                  NT Service\MSSQLFDLauncher
      FTSVCPASSWORD:                 <empty>
      HELP:                          false
      INDICATEPROGRESS:              false
      INSTALLSHAREDDIR:              C:\Program Files\Microsoft SQL Server\
      INSTALLSHAREDWOWDIR:           C:\Program Files (x86)\Microsoft SQL Server\
      INSTALLSQLDATADIR:             <empty>
      INSTANCEDIR:                   C:\Program Files\Microsoft SQL Server\
      INSTANCEID:                    MSSQLSERVER
      INSTANCENAME:                  MSSQLSERVER
      ISSVCACCOUNT:                  NT Service\MsDtsServer120
      ISSVCPASSWORD:                 <empty>
      ISSVCSTARTUPTYPE:              Automatic
      MATRIXCMSERVERNAME:            <empty>
      MATRIXNAME:                    <empty>
      NPENABLED:                     0
      PID:                           *****
      QUIET:                         true
      QUIETSIMPLE:                   false
      RSINSTALLMODE:                 DefaultNativeMode
      RSSHPINSTALLMODE:              DefaultSharePointMode
      RSSVCACCOUNT:                  <empty>
      RSSVCPASSWORD:                 <empty>
      RSSVCSTARTUPTYPE:              Automatic
      SAPWD:                         <empty>
      SECURITYMODE:                  <empty>
      SQLBACKUPDIR:                  C:\Backup
      SQLCOLLATION:                  SQL_Latin1_General_CP1_CI_AS
      SQLSVCACCOUNT:                 Administrator
      SQLSVCPASSWORD:                *****
      SQLSVCSTARTUPTYPE:             Automatic
      SQLSYSADMINACCOUNTS:           Administrator, Administrator
      SQLTEMPDBDIR:                  C:\TempDB
      SQLTEMPDBLOGDIR:               C:\TempLog
      SQLUSERDBDIR:                  C:\Data
      SQLUSERDBLOGDIR:               C:\Log
      SQMREPORTING:                  false
      TCPENABLED:                    1
      UIMODE:                        Normal
      UpdateEnabled:                 true
      UpdateSource:                  C:\InstallSQL\SQL2014\Updates
      USEMICROSOFTUPDATE:            false
      X86:                           false
      Configuration file:            C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Log\20150218_210907\ConfigurationFile.ini
    Detailed results:
      Feature:                       Management Tools - Complete
      Status:                        Passed
      Feature:                       Client Tools Connectivity
      Status:                        Passed
      Feature:                       Client Tools SDK
      Status:                        Passed
      Feature:                       Client Tools Backwards Compatibility
      Status:                        Passed
      Feature:                       Management Tools - Basic
      Status:                        Passed
      Feature:                       Database Engine Services
      Status:                        Passed
      Feature:                       Data Quality Services
      Status:                        Passed
      Feature:                       Full-Text and Semantic Extractions for Search
      Status:                        Passed
      Feature:                       SQL Server Replication
      Status:                        Passed
      Feature:                       Master Data Services
      Status:                        Passed
      Feature:                       Distributed Replay Client
      Status:                        Passed
      Feature:                       Distributed Replay Controller
      Status:                        Passed
      Feature:                       Integration Services
      Status:                        Passed
      Feature:                       Data Quality Client
      Status:                        Passed
      Feature:                       Analysis Services
      Status:                        Passed
      Feature:                       SQL Browser
      Status:                        Passed
      Feature:                       Documentation Components
      Status:                        Passed
      Feature:                       SQL Writer
      Status:                        Passed
      Feature:                       SQL Client Connectivity
      Status:                        Passed
      Feature:                       SQL Client Connectivity SDK
      Status:                        Passed
      Feature:                       Setup Support Files
      Status:                        Passed
    Rules with failures:
    Global rules:
    Scenario specific rules:
    Rules report file:               C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Log\20150218_210907\SystemConfigurationCheck_Report.htm

    The problem I run into is when I attempt to invoke this particular script from CI tools, the error triggers a false exit of the provision.  Additionally the error seems to happen before I can install updates.  

    I am using SQL2014.  Does anybody have any ideas?

    • Moved by Bill_Stewart Wednesday, February 18, 2015 9:39 PM Move to more appropriate forum
    Wednesday, February 18, 2015 9:25 PM

All replies

  • 1.  Has this been tested with 2014 before?
    2.  Remember that all of the "x" desginations are experimental and have had very little testing.
    3.  What have you done to debug this script?
    4.  Is there some reaon why you cannot post the errors?


    Wednesday, February 18, 2015 9:54 PM
  • Hi jrv,

    1. Unsure there is almost 0 documentation of this methodology on the internet.

    2. Remembered, which is why I posted.  I'm hoping somebody else has run into this road block before.

    3. As far as debugging the script, I have tried all variety of options in the form of flags that I can think of.  I haven't had the time to debug the actual xSQLServer resource.

    4.  What do you mean?  Those are the errors. :)  These are the only things the system has presented.

    I'd like to continue using the DSC methodology as it falls inline with our current automation patterns.  However it seems using a pre-built ini or a flagged install might have to be the primary method.  This is disappointing because it means that puppet still does a better job of configuring SQL than DSC, it's also disappointing to hear the suggestion that the latest DSC resources aren't being developed for the latest products.

    Nathan Julsrud

    Thursday, February 19, 2015 3:53 PM
  • I can only remind you that the "x" stands for eXperimental.  It is just for testing and familiarization.  The product is incomplete.

    See: http://www.powershellmagazine.com/2015/02/18/windows-management-framework-5-0-february-preview-available/

    The preview of the DSC debugger is available.


    Thursday, February 19, 2015 4:00 PM
  • Thanks for the response.  I'll look into ti's release notes and see what I can do with it.

    Nathan Julsrud

    Thursday, February 19, 2015 4:18 PM
  • Hi Nathan,

    I have been having the same trouble.  I have recently figured out that it looks like the $SQLData object with the Test-TargetResource never returns the "Conn" or "BC" Features and hence throws this error.  There might be others.

    I figured it out by putting in Write-Verbose in the if block where it checks to make sure that the features have been installed.

    If you don't need these features then i would just exclude them.


    Tuesday, February 24, 2015 9:02 AM
  • Hi

    means this that is not possible to install a sql server with dsc?

    i tried various configurations, they all left me with a  'set-targetResource error'.

    Is there any working example for a standalone installation on a server 2016?



    Tuesday, February 11, 2020 6:01 AM