dsccommunity/sqlserverdsc

This module contains DSC resources for deployment and configuration of Microsoft SQL Server.

SqlServerDsc

The SqlServerDsc module contains DSC resources for deployment and configuration of Microsoft SQL Server.

Code of Conduct

This project has adopted this Code of Conduct.

Releases

For each merge to the branch main a preview release will be deployed to PowerShell Gallery. Periodically a release version tag will be pushed which will deploy a full release to PowerShell Gallery.

Contributing

Please check out common DSC Community contributing guidelines and the specific Contributing to SqlServerDsc guidelines.

Change log

A full list of changes in each version can be found in the change log.

Documentation

The documentation can be found in the SqlServerDsc Wiki. The DSC resources schema files is used to automatically update the documentation on each PR merge.

Examples

You can review the Examples directory in the SqlServerDsc module for some general use scenarios for all of the resources that are in the module.

The resource examples are also available in the SqlServerDsc Wiki.

sqlserverdsc's Issues

xSqlServerSetup feature list is case sensitive

When specifying the Features attribute of xSqlServerSetup, the feature list is case sensitive

xSqlServerSetup SqlServer {
    Features= 'SqlEngine'
}

If case sensitivity is required by setup.exe, then some validation should be performed on the case of the given features. If case sensitivity is not required, then it looks like this could be fixed by replacing calls to $Features.Contains("SQLENGINE") (which is case sensitive) with $Features -contains 'SQLENGINE' (which is not case sensitive)

BREAKING CHANGE: Install from ISO fails due to hardcoded reliance on Source folder

If you mount a SQL Server ISO to the disk of a machine then the SourceFolder is always set as "Source" which in the SQL Server ISO isnt a valid path

xSQLServer issue on Azure VM

Hello,

Im using xSQLServer DSC resource through Azure Automation DSC and it returns this issue. It successfully installs SQL Server but something fails.

Best,
Kaido

"Exception":  {

                  "Message":  "PowerShell DSC resource MSFT_xSQLServerSetup  failed to execute Set-TargetResource functionality with error message: Test-TargetResource returned false after calling set. ",
                  "Data":  {

                           },
                  "InnerException":  {
                                         "ErrorRecord":  "Test-TargetResource returned false after calling set.",
                                         "StackTrace":  "   at System.Management.Automation.Interpreter.ThrowInstruction.Run(InterpretedFrame frame)\r\n   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)",
                                         "WasThrownFromThrowStatement":  true,
                                         "Message":  "Test-TargetResource returned false after calling set.",
                                         "Data":  "System.Collections.ListDictionaryInternal",
                                         "InnerException":  "System.Exception: Test-TargetResource returned false after calling set.",
                                         "TargetSite":  "System.Collections.ObjectModel.Collection`1[System.Management.Automation.PSObject] Invoke(System.Collections.IEnumerable)",
                                         "HelpLink":  null,
                                         "Source":  "System.Management.Automation",
                                         "HResult":  -2146233087
                                     },
                  "TargetSite":  null,
                  "StackTrace":  null,
                  "HelpLink":  null,
                  "Source":  null,
                  "HResult":  -2146233079
              },
"TargetObject":  null,
"CategoryInfo":  {
                     "Category":  7,
                     "Activity":  "",
                     "Reason":  "InvalidOperationException",
                     "TargetName":  "",
                     "TargetType":  ""
                 },
"FullyQualifiedErrorId":  "ProviderOperationExecutionFailure",
"ErrorDetails":  null,
"InvocationInfo":  null,
"ScriptStackTrace":  null,
"PipelineIterationInfo":  [

                          ]

}

SqlAGJobStatus: New resource proposal

Consider creating a resource to set Agent jobs status between nodes of an AG
Looks at all nodes in a specified AG
Looks at all DBs in a specified AG
Looks for any jobs dependant on any DB in the specified AG
Sets job status to Enabled/Disabled for Primary/Secondaries as per passed in status parameter

This would be a child resource of xSQLServerAOGroupSyncJobs. It would be called after syncing jobs between all nodes of an AG

BREAKING CHANGE: Check for SQLPS & SQLServer modules required

With the latest updates to SSMS the SQLPS module was renamed to SQLServer so with this in mind we should have a check in all the resources for the change of module name for this along the lines of

If (Get-Module SQLPS -ListAvailable) 
    {
        Write-Verbose "SQLPS Module Found"
        $module = Get-Module SQLPS -ListAvailable
    } 
    else 
    {
    Write-Verbose "SQLPS Module Not found - Using updated SQLServer module "
    $module = Get-Module SQLServer -ListAvailable
    }

Import-Module $module -WarningAction SilentlyContinue -ErrorAction Stop

Though this would need to be actioned in the same way across all of the resources where

Import-Module -Name SQLPS -WarningAction SilentlyContinue -ErrorAction Stop

is currently in place

xSQLServerSetup Features property is case sensitive

Installing with this value Features = "SQLEngine" produces this error message when applying the DSC configuration:

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

The SQL install log provides this message:

Overall summary:
  Final result:                  Failed: see details below
  Exit code (Decimal):           -2061893627
  Exit facility code:            1306
  Exit error code:               5
  Exit message:                  Missing system administrator account. To continue, provide at least one Windows account to provision as a SQL Server system administrator.
  Start time:                    2016-03-07 15:22:41
  End time:                      2016-03-07 15:23:04
  Requested action:              Install

In the Set-TargetResource, the check to validate the list of features uses the array contains function if($Features.Contains("SQLENGINE")) and when it is used this way, it will not find "SQLEngine" in the collection.

xSQLServerLogin cannot be used for the same login on multiple SQL server instances on the same server

Because the login name is used as the key property for the xSQLServerLogin resource, you can't use multiple xSQLServerLogin resources as there will be key clashes:

Resources have identical key properties but there are differences in the following non-key properties:
'SQLInstanceName'. Values 'SECONDINSTANCE' don't match values 'MSSQLSERVER'. Please update these property values so that
they are identical in both cases.

Error when source path contains blank space in xSqlServerSetup

Function netuse in xPDT doesn't quote the path so the command net use failes if source path contains a blank space and comes back with the help text.

Example:
xSQLServerSetup "SQLServerSetup"
{
InstanceName = "MSSQLSERVER"
SetupCredential = $Credential
SourcePath = "\ServerName\Sources\SQL 2012 SP2"
}

I would recommend using a string builder as it makes quoting much easier.

By the way, same should happen when password contains a $ or an other symbol that is expected to be interpreted by net use.

SqlAGSyncLogins: New resource proposal

Consider creating a resource to synchronise Logins between nodes of an AG
Looks at all nodes in a specified AG
Looks at all DBs in a specified AG
Looks for any login in any DB in the Primary node of the specified AG
Copies the login from primary AG node to all secondaries

Could be used to sync both Windows and SQL logins?

Support for 2016 Needs to be added

Support for SQL 2016 needs to be added and tested.

SqlDatabaseRole: cannot be used for the same login on the same server

Test-ConflictingResources : A conflict was detected between resources '[xSQLServerDatabaseRole]dbrolereader (C:\temp\sqllogin.ps1::34::8::xSQLServerDatabaseRole)' and
'[xSQLServerDatabaseRole]dbrolewriter (C:\temp\sqllogin.ps1::42::14::xSQLServerDatabaseRole)' in node 'localhost'. Resources have identical key properties but there are
differences in the following non-key properties: 'Role'. Values 'db_Datareader' don't match values 'db_Datawriter'. Please update these property values so that they are
identical in both cases.

A fix would be to use a string[] for role's instead of string

SqlSetup: Add support for shared features.

It would be nice to select shared features (components under Tools in https://msdn.microsoft.com/en-us/library/ms144259.aspx#Feature) for a SQL Server installation through DSC.

This might need its own resource type, as there can only be one set of shared features installed per installed SQL Server edition and architecture (IIRC).

xSQLAOGroupEnsure doesn't handle the SetupCredential correctly

While building the pester test for xSQLAOGroupEnsure resource I have found that there are inconsistencies in how it handles the SetupCredential parameter across the functions.

The following are the inconsistencies I have found:

1. On all three *-TargetResource functions the SetupCredential parameter is mandatory but not always used.
2. In Set-TargetResouce the parameter SetupCredential is mandatory and passed to Connect-SQL but if the user wants to use Windows Auth then they shouldn't have to pass this credential (or at least set it to $null which would allow Connect-SQL to use it correctly)
3. In Get-TargetResource the parameter SetupCredential is mandatory but not used. It should be optional and passed to the Connect-SQL and Test-TargetResource functions appropriately
4. In Test-TargetResource the parameter SetupCredential is mandatory but not used. It should be optional and passed to the Connect-SQL

SqlSetup: Module does not support installed feature BIDS

https://www.bountysource.com/issues/27794416-trouble-installing-the-feature-rs_shpwfe

Looks like get-TargetResource does not support detection of the following rolls which we need for our SQL Server install.

Issues during using this DSCResource

Hi have use the xSQLServer Resource to install SQLExpress Adv (DEU) on Server Core here are my Experience, maybe that helps the developer :

1. If i started with the Resource on a german language Server, there is a Issue with LocalizedData in the xPDT.psm1. I copy en-Us folder to a de-DE and the Issues are gone. So it would be better to check this, so the others in world could use the resource
2. My Installation-Resource (setup.exe) is on the UNC-Path in the network, the Resource script called the function GetSQLVersion, but there is no net_use command first to make a secure Connection to the UNC-Source. So the function call failed and the resource execution failed.
3. If you use netuse from the xPDT.psm1 and the Setup Credential has a complex Password (maybe double Quote inside), the netuse command failed and the connection couldn’t be established. Maybe it is better to use with pinvoke the WNetAddConnection2 or NetUseAdd methods. I use the implementation from http://stackoverflow.com/questions/1477328/calling-wnetaddconnection2-from-powershell and it worked fine.
4. The second problem with complex password is in the execution of the setup.exe. If you defined a complex SQLService-Account-Password, then the Installation failed (maybe double Quote inside) with wrong password. So it would be better to escaped the arguments. A good starting point for this is http://blogs.msdn.com/b/twistylittlepassagesallalike/archive/2011/04/23/everyone-quotes-arguments-the-wrong-way.aspx. If I use the rules in the blog the complex password is correct committed to the setup.exe
5. If you check the features, then the check is CASEsensitiv, so if you not take the correct CASE in the Resource-Property then the installed Feature is not recognized correct. So maybe it is better to make a IGNORE-CASE compare. But this issue is addressed on https://github.com/PowerShell/xSQLServer/commits/dev ‘Resolved problem with $Features due to Contains method being case sen…’.

I hope this information helps, maybe you already know this all ;-), but I spend a lot of time and I thought it would be a good idea to share this information
best regards jens m.

SqlSetup: Enhancement Request...Additional Features

Could an additional resource be created or incorporated into the [MSFT_xSQLServerSetup] to perform the following configuration actions:
1.Set the number of Error Logs
TSQL: EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, ?
2. Disable the Named Pipes Protocol
3. Resize the TempDB ... set the number of tempdb files based on number of CPUs...?
4. Create a resource that would allow for the creation and configuration of MSSQL Agent Jobs
5. Modify and enforce the 'AuditLevel'
TSQL: EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', REG_DWORD, 3

Default SourcePath Error

I am trying to install SQL 2014 with xSQLServerSetup resource, node configuration is given below.

Node $AllNodes.Where{$_.InstallType -eq "SingleServer" }.NodeName
{
    $SecureSvcPassword = ConvertTo-SecureString -String $Node.SQLSvcAccountPassword -AsPlainText -Force
    xSQLServerSetup DefaultSQLInstance
    {
        InstanceName = $Node.SQLInstanceName
        SourcePath = "C:\BizTalk\SqlServer\"
        SecurityMode = "Windows"
        SetupCredential = $InstallCredential
        Features = $Node.SQLFeatures
        InstallSharedDir = "C:\Program Files\Microsoft SQL Server"
        InstallSharedWOWDir = "C:\Program Files (x86)\Microsoft SQL Server"
        SQLSvcAccount = New-Object System.Management.Automation.PSCredential($Node.SQLSvcAccount,$SecureSvcPassword)
    }

on Execution its by default looking for a directory 'SQLServer2012.en \ setup.exe' under the specified source. Not sure but i believe it should be looking setup.exe under the supplied source.

SqlServerNetwork: Add support for protocol configuration

It would be very nice if I could specify that I wanted Named Pipes or TCP/IP enabled as part of my DSC.

This could be done as either additional parameters for each setup parameter or a single parameter that accepted a path to a configurationfile.ini.

The command line/configuration options for the network protocols have been present in the setup.exe since SQL 2008
How to: Install SQL Server 2008 from the Command Prompt

New Examples for 1.6 Release Missing

I have really enjoyed working with the xSQLServer resource and have been looking forward to the 1.6 release. I'm reworking my deployments to include multiple AAG groups per instance and noticed that no new examples were released with 1.6 or 1.7.

Does anyone have a working example for creating AAG they could share as a starting point?

Thanks!

Installing 32 bit SQL Engine on 64 bit OS

Error:
Cannot find path 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL' because it does not exist.

Path should be:
HKLM:\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\Instance Names\SQL

Process of release cycle to PS Gallery

Hi,

I have a general question regarding release process.
I was wondering when can we expect changes in Dev branch to be merged into master and released to PS Gallery as 1.4.0.0?
Is there an agreed process or release train, on how often or at what times this is done?
Is there any guidelines that someone could point me to that explain this?

This is just for general awareness, would be good to know what to expect.

Andy info would be appreciated.

Thanks, Arturas

Could not find mandatory property SetupCredential. Add this property and try again.

I've tried using the example setup config and tried modifying down to a super simple config where I pass in a Get-Credential object as a param assigning that to the SetupCredential and I still get this error. No matter how I pass it, I cannot get past this error. Below is my sample ConfigurationData object.

$SecurePassword = ConvertTo-SecureString -String "P@ssw3rd" -AsPlainText -Force
$InstallerServiceAccount = New-Object System.Management.Automation.PSCredential ("sqlserver\sqluser", $SecurePassword)
$LocalSystemAccount = New-Object System.Management.Automation.PSCredential ("SYSTEM", $SecurePassword)

$ConfigurationData = @{
    AllNodes = @(
        @{
            NodeName = "sqlserver"
            PSDscAllowPlainTextPassword = $true
            SourcePath = "D:\"
            InstallerServiceAccount = $InstallerServiceAccount
            LocalSystemAccount = $LocalSystemAccount
            AdminAccount = "sqlserver\sqluser"
            InstanceName = "MSSQLSERVER"
            Features = "SQLENGINE,RS,CONN,BC,SSMS,ADV_SSMS"

        }
    )
}

SqlDatabaseFile: New resource proposal

SqlDatabaseFile

I would like to see a new resource SqlDatabaseFile which can add and modify data file and/or log file.
Comments on this appreciated.

Description

I can see this resource take multiple names of files. So it can create one file or several files depending on the users requirement. But I think the resource should not create Log and Data files at the same time. User should implement two steps n the configuration file for adding both log and data files.
I don't see this module to be able to move data or log file to a new path, maybe that can be a improvement. With modify I mean it can make sure sizes are always the same, if user wants that.

Usage

I personally want to be able to use this to modify the tempdb as well as other databases.

Schema suggestion

ServerName - The host name of the SQL Server.
InstanceName - The instance name of the SQL Server. Default is 'MSSQLSERVER'.
Ensure - If the file should me present or absent, and if it present if the sizes should be enforced. Valid values are 'Present', 'PresentAndSize', 'Absent'.
Name - Name of the file. Will be used as name and display name.
Path - The path where file will be created. It defaults to the path of the first file returned.
FileType - The type of file to create. Valid values are 'DATA','LOG'. Defaults to 'DATA'.
FileGroup - The file group to add the data file to. Defaults to 'PRIMARY'. Ignored if -FileType is 'LOG'.
GrowthType - The auto growth type. Valid values are 'Percent' or 'MB'.
Growth - The auto growth size for the file. Value is in percent or MB depending of -GrowthType.
IntialSize - The initial size of the file in MB. If -Ensure is set to 'PresentAndSize' then the initial size will be tried to be enforced.
MaxSize - The max size of the file in MB, or -1for unlimited size. If -Ensure is set to 'PresentAndSize' and -MaxSize does not equal -1 then the max size will be tried to be enforced.

Use of Win32_Product WMI class causes msiinstaller to reconfigure all installed programs.

The two setup resources uses the Win32_Product WMI class which is not recommended for use because it causes the msiinstaller to reconfigure every installed application registered. This is very slow and risks issues with other applications.

https://support.microsoft.com/en-nz/kb/974524

The use of this class should be replaced with an alternative class.

SqlAGSyncJob: New resource proposal

Consider creating a resource to synchronise Agent jobs between nodes of an AG
Looks at all nodes in a specified AG
Looks at all DBs in a specified AG
Looks for any jobs dependant on a DB in the Primary node of the specified AG
Copies job from primary AG node to all secondaries

xSQLServerAOGroupJobStatus can be used to ensure job's are in a specified state (enabled vs disabled) for primary or secondary nodes

SQL 2016 Support

Could someone pick this up and run with it?

Enhancement Request...Support for SQL Server AlwaysOn Availability groups

would be awesome ! ... and you know it ;)

Enhancement Request...Set MSSQL Min and Max Memory to the same values

Request: Please allow an individually to set the "min" and "max" MSSQL memory settings to each each other if a user wanted such. For example; in current build process; I like setting the Min and the Max memory to the same value. I would like that to be dynamically set as well. If this is already part of the overall configuration, please give me and example of how to perform that...say in the [SQLPush_SingleServer.ps1] as this is the template I am using.

Pester Tests Need to be implemented for all existing Modules

We need additional people to help write Pester tests. This is a requirement to take the resource out of experimental status.

When will Version 1.4.0 be released

I have started using resources in this version, and was surprised to see that it wasn't yet released.
When will this be released?
Using the following command, I only show version 1.3.0:
find-module -includes DscResource -verbose
Please advise - thank you!
Angela Bouman

Server roles

Is it possible to grant server roles to users? It would be great if you could grant them using xSQLServerLogin resource.

SourcePath and UpdateEnabled

Hi Team
Having an issue with this module, not sure if it's my issue or issue with the code.
Using the sample included I cannot get the sourcepath or updateenabled/updatesource parameters to work.
Digging through the various modules I found that there were hard coded values in xSQLServerSetup.psm1 and they were not getting replaced with my parameter values.
I removed the hard coded values from the psm1 file and it correctly picked up my parameter values.
Is this expected behaviour?
Cheers

xSQLServerPowerPlan Get-DscConfiguration

Get-DscConfiguration of xSQLServerPowerPlan returns object:

ConfigurationName    : SQLServerInstance
DependsOn            : {[xSQLServerSetup]{name}}
ModuleName           : xSQLServer
ModuleVersion        : 1.6.0.0
PsDscRunAsCredential :
ResourceId           : [xSQLServerPowerPlan]{name}
SourceInfo           :
Ensure               : True
PSComputerName       : {server}
CimClassName         : MSFT_xSQLServerPowerPlan

Where Ensure Should be Present

xSQLServerSetup references Setup ProcessID but doesnt capture it

In the possible variables there is a PID Variable being used that is meant to be used to capture the process for the SQL Setup.exe

However the PID variable doesn't get set properly and because of this causes the Set method to close early which then invokes the test method straight away causing it to error out

xSQLServer requires refactoring to reduce need on xPDT & xSQLServerHelper

Looking at the xSQLServerSetup there is alot of reliance on helper functions that are included in here that very much look like they were built for PowerShell v2

Is there still any real requirement to use these helper modules?

SqlSetup: Sets a lot of settings on the sql resource but doesn't enforce consistency

xSqlSetup sets a lot of settings on the sql resource but doesn't enforce consistency. In test, it checks if all the features are there and if they are it says it's good.

Where is "NetUse" supposed to come from? Please support local copy of SQL Setup

This assumes setup.exe will always be on a remote UNC and so requires credentials, a UNC and path.

However, the function NetUse on line 597 and 601 are not in the module.

In functions where I reference a path I check if it contains "" for uncs and ":" for drive letters. If it is a drive letter, I don't bother with credentials.

It would be better if the resource supported local copies of the SQL source as well.

Installing sql server 2014 with xSqlServerSetup error - Test-TargetResource returned false after calling set.

Hi

Im trying to install SQL server 2014 using the module xSQLserver. The 2 mof files are created without issues.
I have created the configuration mof and running the Start.DscConfiguration on the SQL server itself for testing. When running the
Start-DscConfiguration -Force -Path C:\Keys -ComputerName 'server' -Wait -Verbose
I get an error:

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        : server.contoso.com

VERBOSE: [server]: LCM:  [ End    Set      ]
The SendConfigurationApply function did not succeed.
    + CategoryInfo          : NotSpecified: (root/Microsoft/...gurationManager:String) [], CimException
    + FullyQualifiedErrorId : MI RESULT 1
    + PSComputerName        : server.contoso.com

VERBOSE: Operation 'Invoke CimMethod' complete.
VERBOSE: Time taken for configuration job to complete is 45.841 seconds

Have you seen this before? Any tips how to proceed?

The whole output of the Start-DscConfiguration:

PS C:\Temp> Start-DscConfiguration -Force -Path C:\Keys -ComputerName 'server.contoso.com' -Wait -Verbose
VERBOSE: Perform operation 'Invoke CimMethod' with following parameters, ''methodName' = SendConfigurationApply,'className' = MSFT_DSCLocalConfigurationManager,'namespaceName' = root/Microsoft/Windows/DesiredStateConfiguration'.
VERBOSE: An LCM method call arrived from computer server with user sid S-1-5-21-195968190-741174349-770780043-93293.
VERBOSE: [server]: LCM:  [ Start  Set      ]
VERBOSE: [server]: LCM:  [ Start  Resource ]  [[File]InstallDir]
VERBOSE: [server]: LCM:  [ Start  Test     ]  [[File]InstallDir]
VERBOSE: [server]:                            [[File]InstallDir] The destination object was found and no action is required.
VERBOSE: [server]: LCM:  [ End    Test     ]  [[File]InstallDir]  in 0.0150 seconds.
VERBOSE: [server]: LCM:  [ Skip   Set      ]  [[File]InstallDir]
VERBOSE: [server]: LCM:  [ End    Resource ]  [[File]InstallDir]
VERBOSE: [server]: LCM:  [ Start  Resource ]  [[File]SQLUserDBDir]
VERBOSE: [server]: LCM:  [ Start  Test     ]  [[File]SQLUserDBDir]
VERBOSE: [server]:                            [[File]SQLUserDBDir] The destination object was found and no action is required.
VERBOSE: [server]: LCM:  [ End    Test     ]  [[File]SQLUserDBDir]  in 0.0000 seconds.
VERBOSE: [server]: LCM:  [ Skip   Set      ]  [[File]SQLUserDBDir]
VERBOSE: [server]: LCM:  [ End    Resource ]  [[File]SQLUserDBDir]
VERBOSE: [server]: LCM:  [ Start  Resource ]  [[File]SQLUserLogDir]
VERBOSE: [server]: LCM:  [ Start  Test     ]  [[File]SQLUserLogDir]
VERBOSE: [server]:                            [[File]SQLUserLogDir] The destination object was found and no action is required.
VERBOSE: [server]: LCM:  [ End    Test     ]  [[File]SQLUserLogDir]  in 0.0000 seconds.
VERBOSE: [server]: LCM:  [ Skip   Set      ]  [[File]SQLUserLogDir]
VERBOSE: [server]: LCM:  [ End    Resource ]  [[File]SQLUserLogDir]
VERBOSE: [server]: LCM:  [ Start  Resource ]  [[File]TempDBDir]
VERBOSE: [server]: LCM:  [ Start  Test     ]  [[File]TempDBDir]
VERBOSE: [server]:                            [[File]TempDBDir] The destination object was found and no action is required.
VERBOSE: [server]: LCM:  [ End    Test     ]  [[File]TempDBDir]  in 0.0000 seconds.
VERBOSE: [server]: LCM:  [ Skip   Set      ]  [[File]TempDBDir]
VERBOSE: [server]: LCM:  [ End    Resource ]  [[File]TempDBDir]
VERBOSE: [server]: LCM:  [ Start  Resource ]  [[File]BackupDBDir]
VERBOSE: [server]: LCM:  [ Start  Test     ]  [[File]BackupDBDir]
VERBOSE: [server]:                            [[File]BackupDBDir] The destination object was found and no action is required.
VERBOSE: [server]: LCM:  [ End    Test     ]  [[File]BackupDBDir]  in 0.0000 seconds.
VERBOSE: [server]: LCM:  [ Skip   Set      ]  [[File]BackupDBDir]
VERBOSE: [server]: LCM:  [ End    Resource ]  [[File]BackupDBDir]
VERBOSE: [server]: LCM:  [ Start  Resource ]  [[xSQLServerSetup]sql2014]
VERBOSE: [server]: LCM:  [ Start  Test     ]  [[xSQLServerSetup]sql2014]
VERBOSE: [server]:                            [[xSQLServerSetup]sql2014] Loading module from path 'C:\Program Files\WindowsPowerShell\Modules\xSQLServer\1.5.0.0\xPDT.psm1'.
VERBOSE: [server]:                            [[xSQLServerSetup]sql2014] Importing function 'GetxPDTVariable'.
VERBOSE: [server]:                            [[xSQLServerSetup]sql2014] Importing function 'NetUse'.
VERBOSE: [server]:                            [[xSQLServerSetup]sql2014] Importing function 'ResolvePath'.
VERBOSE: [server]:                            [[xSQLServerSetup]sql2014] Importing function 'StartWin32Process'.
VERBOSE: [server]:                            [[xSQLServerSetup]sql2014] Importing function 'WaitForWin32ProcessEnd'.
VERBOSE: [server]:                            [[xSQLServerSetup]sql2014] Path: e:\SQL2014\setup.exe
VERBOSE: [server]: LCM:  [ End    Test     ]  [[xSQLServerSetup]sql2014]  in 1.6410 seconds.
VERBOSE: [server]: LCM:  [ Start  Set      ]  [[xSQLServerSetup]sql2014]
VERBOSE: [server]:                            [[xSQLServerSetup]sql2014] Importing function 'GetxPDTVariable'.
VERBOSE: [server]:                            [[xSQLServerSetup]sql2014] Importing function 'NetUse'.
VERBOSE: [server]:                            [[xSQLServerSetup]sql2014] Importing function 'ResolvePath'.
VERBOSE: [server]:                            [[xSQLServerSetup]sql2014] Importing function 'StartWin32Process'.
VERBOSE: [server]:                            [[xSQLServerSetup]sql2014] Importing function 'WaitForWin32ProcessEnd'.
VERBOSE: [server]:                            [[xSQLServerSetup]sql2014] Path: e:\SQL2014\setup.exe
VERBOSE: [server]:                            [[xSQLServerSetup]sql2014] Importing function 'GetxPDTVariable'.
VERBOSE: [server]:                            [[xSQLServerSetup]sql2014] Importing function 'NetUse'.
VERBOSE: [server]:                            [[xSQLServerSetup]sql2014] Importing function 'ResolvePath'.
VERBOSE: [server]:                            [[xSQLServerSetup]sql2014] Importing function 'StartWin32Process'.
VERBOSE: [server]:                            [[xSQLServerSetup]sql2014] Importing function 'WaitForWin32ProcessEnd'.
VERBOSE: [server]:                            [[xSQLServerSetup]sql2014] Path: e:\SQL2014\setup.exe
VERBOSE: [server]:                            [[xSQLServerSetup]sql2014] Arguments: /Quiet="True" /IAcceptSQLServerLicenseTerms="True" /Action="Install" /InstanceName="SQL2014" /Features="sqlengine,ssms"
VERBOSE: [server]:                            [[xSQLServerSetup]sql2014] Process matching path 'e:\SQL2014\setup.exe' started in process ID 2988
VERBOSE: [server]:                            [[xSQLServerSetup]sql2014] Importing function 'GetxPDTVariable'.
VERBOSE: [server]:                            [[xSQLServerSetup]sql2014] Importing function 'NetUse'.
VERBOSE: [server]:                            [[xSQLServerSetup]sql2014] Importing function 'ResolvePath'.
VERBOSE: [server]:                            [[xSQLServerSetup]sql2014] Importing function 'StartWin32Process'.
VERBOSE: [server]:                            [[xSQLServerSetup]sql2014] Importing function 'WaitForWin32ProcessEnd'.
VERBOSE: [server]:                            [[xSQLServerSetup]sql2014] Path: e:\SQL2014\setup.exe
VERBOSE: [server]:                            [[xSQLServerSetup]sql2014] Test-TargetResource returned false after calling set. | ErrorType: MSFT_xSQLServerSetup.TestFailedAfterSet
VERBOSE: [server]: LCM:  [ End    Set      ]  [[xSQLServerSetup]sql2014]  in 43.2510 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        : server.contoso.com

VERBOSE: [server]: LCM:  [ End    Set      ]
The SendConfigurationApply function did not succeed.
    + CategoryInfo          : NotSpecified: (root/Microsoft/...gurationManager:String) [], CimException
    + FullyQualifiedErrorId : MI RESULT 1
    + PSComputerName        : server.contoso.com

VERBOSE: Operation 'Invoke CimMethod' complete.
VERBOSE: Time taken for configuration job to complete is 45.841 seconds`

I create a certificate here and add the .cer to a c:\keys folder....

My whole script is:

 $ConfigData=
@{
    AllNodes = @(
    @{
        NodeName = 'server.contoso.com'
        CertificateFile = $certPath.FullName
        Thumbprint = $cert.Thumbprint
        PSDscAllowDomainUser = $true
     }
     )
}


Configuration SQLInstall
{
    param(
        #[Parameter(Mandatory=$true)]
        #[ValidateNotNullorEmpty()]
        [PsCredential] $Credential,
        [PsCredential] $ServiceAccount,
        [PsCredential] $AgentAccount
        )

    #Import-DSCResource -ModuleName xPSDesiredStateConfiguration 
    Import-DSCResource -ModuleName PSDesiredStateConfiguration
    Import-DSCResource -ModuleName xPSDesiredStateConfiguration
    Import-DscResource -Module xSQLServer

    Node "server.contoso.com" 
    {

        File InstallDir
        {
            DestinationPath = 'C:\MSSQL12.MSSQLSERVER\MSSQL\Data'
            Type = 'Directory'
            Ensure = 'Present'
        }

        File SQLUserDBDir
        {
            DestinationPath = 'E:\MSSQL12.MSSQLSERVER\MSSQL\Data'
            Type = 'Directory'
            Ensure = 'Present'
        }

        File SQLUserLogDir
        {
            DestinationPath = 'F:\MSSQL12.MSSQLSERVER\MSSQL\Data'
            Type = 'Directory'
            Ensure = 'Present'
        }

        File TempDBDir
        {
            DestinationPath = 'G:\MSSQL12.MSSQLSERVER\MSSQL\Data'
            Type = 'Directory'
            Ensure = 'Present'
        }

        File BackupDBDir
        {
            DestinationPath = 'E:\MSSQL12.MSSQLSERVER\MSSQL\Backup'
            Type = 'Directory'
            Ensure = 'Present'
        }


        xSQLServerSetup sql2014
        {
            SetupCredential = $Credential 
            SQLSvcAccount = $ServiceAccount                                                            
            AgtSvcAccount = $AgentAccount
            Features = "sqlengine,ssms"
            InstanceName = "sql2014"
            SQLSysAdminAccounts = "contoso\Sqlins"
            SourcePath = "e:\"
            SourceFolder="SQL2014"   
            InstallSQLDataDir = "C:\MSSQL12.MSSQLSERVER\MSSQL\Data"
            SQLUserDBDir = "e:\MSSQL12.MSSQLSERVER\MSSQL\Data"
            SQLUserDBLogDir = "F:\MSSQL12.MSSQLSERVER\MSSQL\Data"
            SQLTempDBLogDir = "G:\MSSQL12.MSSQLSERVER\MSSQL\Data"
            SQLBackupDir = "e:\MSSQL12.MSSQLSERVER\MSSQL\Backup"                                                                                          
        }

        LocalConfigurationManager
        {
            CertificateId = $node.Thumbprint
            RebootNodeIfNeeded = $true
        }
    }
}


SQLInstall -ConfigurationData $ConfigData -OutputPath C:\Keys -Credential (Get-Credential "contoso\Sqlins") -ServiceAccount (Get-Credential "contoso\service") -AgentAccount (Get-Credential "contoso\Agent")

Set-DscLocalConfigurationManager C:\Keys\

Start-DscConfiguration -Force -Path C:\Keys -ComputerName 'server.contoso.com' -Wait -Verbose

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

I'm trying to run SQL 2014c install using the xSQLserverSetup module with the following error messages:

Directory: C:\Temp\SqlServerSetup

Mode LastWriteTime Length Name

---

-a---- 29/07/2016 11:51 6248 LONSQL60A.mof
-a---- 29/07/2016 11:51 1122 LONSQL60A.meta.mof

PS C:\Program Files\WindowsPowerShell\Modules> Start-DscConfiguration -Path C:\Temp\SqlServerSetup\ -credential (Get-credential) -Verbose -Wait -Force
cmdlet Get-Credential at command pipeline position 1
Supply values for the following parameters:
VERBOSE: Perform operation 'Invoke CimMethod' with following parameters, ''methodName' = SendConfigurationApply,'className' = MSFT_DSCLocalConfigurationManager,'namespaceName' = root/Microsof
t/Windows/DesiredStateConfiguration'.
VERBOSE: An LCM method call arrived from computer CONTOSO.COM with user sid S-1-5-21-3798882980-2146746437-3985048140-44958.
VERBOSE: [CONTOSO.COM]: LCM: [ Start Set ]
VERBOSE: [CONTOSO.COM]: [DSCEngine] Importing the module C:\Windows\system32\WindowsPowerShell\v1.0\Modules\PSDesiredStateConfiguration\DscResources\MSFT_ScriptResour
ce\MSFT_ScriptResource.psm1 in force mode.
VERBOSE: [CONTOSO.COM]: [DSCEngine] Importing the module C:\Windows\system32\WindowsPowerShell\v1.0\Modules\PSDesiredStateConfiguration\DscResources\MSFT_RoleResource
\MSFT_RoleResource.psm1 in force mode.
VERBOSE: [CONTOSO.COM]: [DSCEngine] Importing the module C:\Program Files\WindowsPowerShell\Modules\xSQLServer\1.5.0.0\DscResources\MSFT_xSQLServerSetup\MSFT_xSQLServ
erSetup.psm1 in force mode.
VERBOSE: [CONTOSO.COM]: LCM: [ Start Resource ] [[Script]DisableFirewall]
VERBOSE: [CONTOSO.COM]: LCM: [ Start Test ] [[Script]DisableFirewall]
VERBOSE: [CONTOSO.COM]: [[Script]DisableFirewall] Importing the module MSFT_ScriptResource in force mode.
VERBOSE: [CONTOSO.COM]: LCM: [ End Test ] [[Script]DisableFirewall] in 1.2350 seconds.
VERBOSE: [CONTOSO.COM]: LCM: [ Skip Set ] [[Script]DisableFirewall]
VERBOSE: [CONTOSO.COM]: LCM: [ End Resource ] [[Script]DisableFirewall]
VERBOSE: [CONTOSO.COM]: LCM: [ Start Resource ] [[File]DSCResourceFolder]
VERBOSE: [CONTOSO.COM]: LCM: [ Start Test ] [[File]DSCResourceFolder]
VERBOSE: [CONTOSO.COM]: [[File]DSCResourceFolder] The network name cannot be found.
VERBOSE: [CONTOSO.COM]: [[File]DSCResourceFolder] The related file/directory is: \CONTOSO.COM\Modules.
VERBOSE: [CONTOSO.COM]: [[File]DSCResourceFolder] Building file list from cache.
VERBOSE: [CONTOSO.COM]: [[File]DSCResourceFolder] The destination object was found and no action is required.
VERBOSE: [CONTOSO.COM]: LCM: [ End Test ] [[File]DSCResourceFolder] in 7.6720 seconds.
VERBOSE: [CONTOSO.COM]: LCM: [ Skip Set ] [[File]DSCResourceFolder]
VERBOSE: [CONTOSO.COM]: LCM: [ End Resource ] [[File]DSCResourceFolder]
VERBOSE: [CONTOSO.COM]: LCM: [ Start Resource ] [[WindowsFeature]NTFranmeworkcore]
VERBOSE: [CONTOSO.COM]: LCM: [ Start Test ] [[WindowsFeature]NTFranmeworkcore]
VERBOSE: [CONTOSO.COM]: [[WindowsFeature]NTFranmeworkcore] Importing the module MSFT_RoleResource in force mode.
VERBOSE: [CONTOSO.COM]: [[WindowsFeature]NTFranmeworkcore] The operation 'Get-WindowsFeature' started: Net-Framework-Core
VERBOSE: [CONTOSO.COM]: [[WindowsFeature]NTFranmeworkcore] The operation 'Get-WindowsFeature' succeeded: NET-Framework-Core
VERBOSE: [CONTOSO.COM]: LCM: [ End Test ] [[WindowsFeature]NTFranmeworkcore] in 0.6710 seconds.
VERBOSE: [CONTOSO.COM]: LCM: [ Skip Set ] [[WindowsFeature]NTFranmeworkcore]
VERBOSE: [CONTOSO.COM]: LCM: [ End Resource ] [[WindowsFeature]NTFranmeworkcore]
VERBOSE: [CONTOSO.COM]: LCM: [ Start Resource ] [[xSQLServerSetup]SqlInstaller]
VERBOSE: [CONTOSO.COM]: LCM: [ Start Test ] [[xSQLServerSetup]SqlInstaller]
VERBOSE: [CONTOSO.COM]: [[xSQLServerSetup]SqlInstaller] Importing the module MSFT_xSQLServerSetup in force mode.
VERBOSE: [CONTOSO.COM]: [[xSQLServerSetup]SqlInstaller] Loading module from path 'C:\Program Files\WindowsPowerShell\Modules\xSQLServer\1.5.0.0\xPDT.psm1'.
VERBOSE: [CONTOSO.COM]: [[xSQLServerSetup]SqlInstaller] Importing function 'GetxPDTVariable'.
VERBOSE: [CONTOSO.COM]: [[xSQLServerSetup]SqlInstaller] Importing function 'NetUse'.
VERBOSE: [CONTOSO.COM]: [[xSQLServerSetup]SqlInstaller] Importing function 'ResolvePath'.
VERBOSE: [CONTOSO.COM]: [[xSQLServerSetup]SqlInstaller] Importing function 'StartWin32Process'.
VERBOSE: [CONTOSO.COM]: [[xSQLServerSetup]SqlInstaller] Importing function 'WaitForWin32ProcessEnd'.
VERBOSE: [CONTOSO.COM]: [[xSQLServerSetup]SqlInstaller] Path: Z:\setup.exe
VERBOSE: [CONTOSO.COM]: LCM: [ End Test ] [[xSQLServerSetup]SqlInstaller] in 1.5790 seconds.
VERBOSE: [CONTOSO.COM]: LCM: [ Start Set ] [[xSQLServerSetup]SqlInstaller]
VERBOSE: [CONTOSO.COM]: [[xSQLServerSetup]SqlInstaller] Importing the module MSFT_xSQLServerSetup in force mode.
VERBOSE: [CONTOSO.COM]: [[xSQLServerSetup]SqlInstaller] Loading module from path 'C:\Program Files\WindowsPowerShell\Modules\xSQLServer\1.5.0.0\xPDT.psm1'.
VERBOSE: [CONTOSO.COM]: [[xSQLServerSetup]SqlInstaller] Importing function 'GetxPDTVariable'.
VERBOSE: [CONTOSO.COM]: [[xSQLServerSetup]SqlInstaller] Importing function 'NetUse'.
VERBOSE: [CONTOSO.COM]: [[xSQLServerSetup]SqlInstaller] Importing function 'ResolvePath'.
VERBOSE: [CONTOSO.COM]: [[xSQLServerSetup]SqlInstaller] Importing function 'StartWin32Process'.
VERBOSE: [CONTOSO.COM]: [[xSQLServerSetup]SqlInstaller] Importing function 'WaitForWin32ProcessEnd'.
VERBOSE: [CONTOSO.COM]: [[xSQLServerSetup]SqlInstaller] Path: Z:\setup.exe
VERBOSE: [CONTOSO.COM]: [[xSQLServerSetup]SqlInstaller] Importing function 'GetxPDTVariable'.
VERBOSE: [CONTOSO.COM]: [[xSQLServerSetup]SqlInstaller] Importing function 'NetUse'.
VERBOSE: [CONTOSO.COM]: [[xSQLServerSetup]SqlInstaller] Importing function 'ResolvePath'.
VERBOSE: [CONTOSO.COM]: [[xSQLServerSetup]SqlInstaller] Importing function 'StartWin32Process'.
VERBOSE: [CONTOSO.COM]: [[xSQLServerSetup]SqlInstaller] Importing function 'WaitForWin32ProcessEnd'.
VERBOSE: [CONTOSO.COM]: [[xSQLServerSetup]SqlInstaller] Path: Z:\setup.exe
VERBOSE: [CONTOSO.COM]: [[xSQLServerSetup]SqlInstaller] Arguments: /Quiet="True" /IAcceptSQLServerLicenseTerms="True" /Action="Install" /AGTSVCSTARTUPTYPE=Automatic /
InstanceName="MSSQLSERVER" /Features="SQLENGINE,ADV_SSMS" /SQLSysAdminAccounts="installer"
VERBOSE: [CONTOSO.COM]: [[xSQLServerSetup]SqlInstaller] Process matching path 'Z:\setup.exe' started in process ID 1472
VERBOSE: [CONTOSO.COM]: [[xSQLServerSetup]SqlInstaller] Importing function 'GetxPDTVariable'.
VERBOSE: [CONTOSO.COM]: [[xSQLServerSetup]SqlInstaller] Importing function 'NetUse'.
VERBOSE: [CONTOSO.COM]: [[xSQLServerSetup]SqlInstaller] Importing function 'ResolvePath'.
VERBOSE: [CONTOSO.COM]: [[xSQLServerSetup]SqlInstaller] Importing function 'StartWin32Process'.
VERBOSE: [CONTOSO.COM]: [[xSQLServerSetup]SqlInstaller] Importing function 'WaitForWin32ProcessEnd'.
VERBOSE: [CONTOSO.COM]: [[xSQLServerSetup]SqlInstaller] Path: Z:\setup.exe
VERBOSE: [CONTOSO.COM]: [[xSQLServerSetup]SqlInstaller] Test-TargetResource returned false after calling set. | ErrorType: MSFT_xSQLServerSetup.TestFailedAfterSet
VERBOSE: [CONTOSO.COM]: LCM: [ End Set ] [[xSQLServerSetup]SqlInstaller] in 15.3120 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 : CONTOSO.COM

VERBOSE: [CONTOSO.COM]: LCM: [ End Set ]
The SendConfigurationApply function did not succeed.
+ CategoryInfo : NotSpecified: (root/Microsoft/...gurationManager:String) [], CimException
+ FullyQualifiedErrorId : MI RESULT 1
+ PSComputerName : CONTOSO.COM

VERBOSE: Operation 'Invoke CimMethod' complete.
VERBOSE: Time taken for configuration job to complete is 27.741 seconds

PS C:\Program Files\WindowsPowerShell\Modules>

Here is the message summary:

IOverall summary:
Final result: Failed: see details below
Exit code (Decimal): -2147024895
Exit facility code: 7
Exit error code: 1
Exit message: Incorrect function.
Start time: 2016-07-29 11:58:02
End time: 2016-07-29 11:58:09
Requested action: Install
Exception help link: http://go.microsoft.com/fwlink?LinkId=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=12.0.2000.8&EvtType=0x60797DC7%400xAC9299FD&EvtType=0x60797DC7%400xAC9299FD

Machine Properties:
Machine name: CONTOSO.COM
Machine processor count: 4
OS version: Windows Server 2012
OS service pack:
OS region: United Kingdom
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: Z:\x64\setup
Installation edition: Enterprise

Product Update Status:
None discovered.

User Input Settings:
ACTION: Install
ADDCURRENTUSERASSQLADMIN: false
AGTSVCACCOUNT:
AGTSVCPASSWORD:
AGTSVCSTARTUPTYPE: Automatic
ASBACKUPDIR: Backup
ASCOLLATION: Latin1_General_CI_AS
ASCONFIGDIR: Config
ASDATADIR: Data
ASLOGDIR: Log
ASPROVIDERMSOLAP: 1
ASSERVERMODE: MULTIDIMENSIONAL
ASSVCACCOUNT:
ASSVCPASSWORD:
ASSVCSTARTUPTYPE: Automatic
ASSYSADMINACCOUNTS:
ASTEMPDIR: Temp
BROWSERSVCSTARTUPTYPE: Disabled
CLTCTLRNAME:
CLTRESULTDIR:
CLTSTARTUPTYPE: 0
CLTSVCACCOUNT:
CLTSVCPASSWORD:
CLTWORKINGDIR:
COMMFABRICENCRYPTION: 0
COMMFABRICNETWORKLEVEL: 0
COMMFABRICPORT: 0
CONFIGURATIONFILE:
CTLRSTARTUPTYPE: 0
CTLRSVCACCOUNT:
CTLRSVCPASSWORD:
CTLRUSERS:
ENABLERANU: false
ENU: true
ERRORREPORTING: false
FEATURES: SQLENGINE, ADV_SSMS
FILESTREAMLEVEL: 0
FILESTREAMSHARENAME:
FTSVCACCOUNT:
FTSVCPASSWORD:
HELP: false
IACCEPTSQLSERVERLICENSETERMS: true
INDICATEPROGRESS: false
INSTALLSHAREDDIR: C:\Program Files\Microsoft SQL Server
INSTALLSHAREDWOWDIR: C:\Program Files (x86)\Microsoft SQL Server
INSTALLSQLDATADIR:
INSTANCEDIR: C:\Program Files\Microsoft SQL Server
INSTANCEID:
INSTANCENAME: MSSQLSERVER
ISSVCACCOUNT: NT AUTHORITY\Network Service
ISSVCPASSWORD:
ISSVCSTARTUPTYPE: Automatic
MATRIXCMBRICKCOMMPORT: 0
MATRIXCMSERVERNAME:
MATRIXNAME:
NPENABLED: 0
PID: *****
QUIET: true
QUIETSIMPLE: false
ROLE:
RSINSTALLMODE: DefaultNativeMode
RSSHPINSTALLMODE: DefaultSharePointMode
RSSVCACCOUNT:
RSSVCPASSWORD:
RSSVCSTARTUPTYPE: Automatic
SAPWD:
SECURITYMODE:
SQLBACKUPDIR:
SQLCOLLATION: SQL_Latin1_General_CP1_CI_AS
SQLSVCACCOUNT:
SQLSVCPASSWORD:
SQLSVCSTARTUPTYPE: Automatic
SQLSYSADMINACCOUNTS: installer
SQLTEMPDBDIR:
SQLTEMPDBLOGDIR:
SQLUSERDBDIR:
SQLUSERDBLOGDIR:
SQMREPORTING: false
TCPENABLED: 0
UIMODE: Normal
UpdateEnabled: true
UpdateSource: MU
USEMICROSOFTUPDATE: false
X86: false

Configuration file: C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Log\20160729_115801\ConfigurationFile.ini

Rules with failures:

Global rules:

Scenario specific rules:

Rules report file: C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Log\20160729_115801\SystemConfigurationCheck_Report.htm

Exception summary:
The following is an exception stack listing the exceptions in outermost to innermost order
Inner exceptions are being indented

Exception type: System.IO.IOException
Message:
Incorrect function.

HResult : 0x80070001
Data: 
  DisableWatson = true
Stack: 
    at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
    at System.IO.File.InternalCopy(String sourceFileName, String destFileName, Boolean overwrite)
    at Microsoft.SqlServer.Configuration.UIExtension.HelpFileMigrator.CopyHelpFilestoLocal()
    at Microsoft.SqlServer.Configuration.UIExtension.StartAction.ExecuteAction(String actionId)
    at Microsoft.SqlServer.Chainer.Infrastructure.Action.Execute(String actionId, TextWriter errorStream)
    at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.<>c__DisplayClasse.<ExecuteActionWithRetryHelper>b__b()
    at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.ExecuteActionHelper(ActionWorker workerDelegate)

Problem mocking class types for variable declarations

I need to figure out if this is possible to accomplish. Any comments appreciated. Trying to mock a DSC Resource on a system that does not have SQL Server. Doing that I came across this problem that I have a used specific types for variables. Like the example below, it uses the type Microsoft.SqlServer.Management.Smo.PermissionSetBase.

function Merge-SQLPermissionSet {
    param (
        [Parameter(Mandatory)]
        [Microsoft.SqlServer.Management.Smo.PermissionSetBase[]]
        [ValidateNotNullOrEmpty()]
        $Object
    )

    $baseObject = New-Object -TypeName ($Object[0].GetType())

    foreach ( $currentObject in $Object ) {
        foreach( $Property in $($currentObject | Get-Member -Type Property) ) {
            if( $currentObject.$($Property.Name) ) {
                $baseObject.$($Property.Name) = $currentObject.$($Property.Name)
            }
        }
    }

    return $baseObject
}

I tried to solve that by using a class and adding that as a type (below example is simplified).

$mockPermissionSetBase = @"
    namespace Microsoft.SqlServer.Management.Smo
    {
        public class PermissionSetBase 
        {
            public PermissionSetBaset(){}
            public bool MyValue = false;
        }
    }
"@

Add-Type -TypeDefinition $mockPermissionSetBase

But this does not work since that type will only exist during runtime, not during parsing, giving me syntax errors (missing types in variables declarations).
Adding it as a PowerShell class does not work either since it classes does not support namespaces.

The only way I see past this is to use [Object]. But would really like to keep the types as-is.

@mbreakey3 do you have any feedback on this?

xSQLAOGroupEnsure BackupPreference and Priority are hard coded. Implement as variables and default the values.

SqlSetup: PBDMSSVCPASSWORD and PBENGSVCACCOUNT not available

Hi! Thanks a ton for this module, it is very helpful. I'm trying to install SQL Server 2016 and noticed that the /NPENABLED, /TCPENABLED, /PBDMSSVCPASSWORD, and /PBENGSVCACCOUNT installation flags are not available via the xSQLServerSetup resource, but are valid flags to pass through to the installer. If that wasn't on purpose, I'd like to request that those flags be exposed. Thanks again!

Source: https://msdn.microsoft.com/en-us/library/ms144259.aspx?f=255&MSPPError=-2147217396

SqlSetup: Add support for sysprepped SQL Server.

It is possible to prepare SQL Server most of the way with SysPrep (https://msdn.microsoft.com/en-us/library/ee210754.aspx).

This allows partial installation of SQL Server to a machine that will be sysprepped as a template (with /ACTION=PrepareImage for instances, /ACTION=PrepareFailoverCluster for failover clusters), then the installation can be completed on the restored images (with /ACTION=CompleteImage for instances, /ACTION=CompleteFailoverCluster for failover clusters).

This could be modelled as:

xSQLServerSetup SQL {
    ...
    SysPrep = 'True'
}

The default value would be 'False'.

Set-TargetResource can then proceed as follows:

	Instance Not Installed	Instance Image Prepared	Instance Installed
SysPrep = 'True'	/ACTION=PrepareImage /ACTION=PrepareFailoverCluster	No Action	Error - cannot SysPrep installed instance
SysPrep = 'False'	/ACTION=Install /ACTION=InstallFailoverCluster	/ACTION=CompleteImage /ACTION=CompleteFailoverCluster	No Action

Improvements to xSQLServerEndPoint

Looking at AuthorizedUser parameter in particular.

It is not required according to the schema. But without it you might get a terminating error, it will send a empty string into if(!$SQL.Logins.Contains($AuthorizedUser)) (not sure about this one).

Also if one sets the AuthorizedUser parameter to a user, if that user is not already present as a login you will get a termination error. This should be pointed out in the description of the parameter.

It also doesn't take in account if someone uses different users for each instance. If you use several accounts, then all those accounts need Connect permission in all instances. I would suggest making the AuthorizedUser optional, or change so it can handle more than one account. If we make it optional, then I have an new resource in a PR (xSQLServerEndpointPermission) which sets Connect permission for an user.

@aultt, as orginal author, do you have any feedback on this?

GetFirstItemPropertyValue in MSFT_xSQLServerSetup.psm1 doesn't work if there's only one value to retrieve

When GetFirstItemPropertyValue is run on a key that only contains one value, the line:
$FirstName = ((Get-ItemProperty -Path "$Path\$Name") | Get-Member -MemberType NoteProperty | Where-Object {$_.Name.Substring(0,2) -ne "PS"}).Name[0]
causes only the first character of the name to be returned.
Changing this line to
$FirstName = @(((Get-ItemProperty -Path "$Path\$Name") | Get-Member -MemberType NoteProperty | Where-Object {$_.Name.Substring(0,2) -ne "PS"}).Name)[0]
corrects this without breaking keys that contain multiple values.

I'll make a pull request and submit a change for this as soon as I work out how GitHub works :)

Attention: kwirkykat

Kwirkykat,

You are doing a great job with this repository...with that said, I was wondering if you could do all of us a favor and produce some documentation, at regular interval, containing:

1. the current state of the master repository [the read-me is extremely long..just need points]
2. state of open issues and open pull requests and what the pull/merge requests include

Why you ask? I saw that Troy Ault was in the process of getting his stuff into the master...as it took care of my request for SQL 2016 and some other changes...not sure where it all stands. I wanted to download the newest repository with the newest code...but saw it did not include Troy's stuff.

Basically, kind of looking for a forecasting...indicating what will be merged and what that merge includes. Essentially, if we were doing "sprints" what our sprint duration is and what is included in that sprint. Some "low-level" management reports for the repository.

It would be great, if not I understand we all know you are super busy. Keep up the good work, we all really appreciate the time and energy you have put into this repository.

Matt Haines

Default value for xSQLServerSetup.UpdateSource causes deployment error

Hi,

I think that current default value for xSQLServerSetup.UpdateSource of ".\Updates" is causing unexpected error even if xSQLServerSetup.UpdateEnabled = "False" is set.

I suggest that this parameter should be updated to default MU.
To reproduce this, you can download an iso image from MSDN subscription for SQL Server 2012 with SP3, if you extract contents and use it as your source.
Then use this Consiguration to install management tools:

Configuration DataTier
{
    Import-DscResource -Module xSQLServer

    # A Configuration expects at least one Node
    Node $AllNodes.NodeName
    {
        # Set DCM Settings for each Node 
        LocalConfigurationManager 
        { 
            RebootNodeIfNeeded = $True 
            ConfigurationMode = "ApplyOnly" 
        } 

        WindowsFeature "NET-Framework-Core"
        {
            Ensure = "Present"
            Name = "NET-Framework-Core"
        }

        xSqlServerSetup "SQLMT"
        {
            DependsOn = "[WindowsFeature]NET-Framework-Core"
            SourcePath = $Node.SourcePath
            SourceFolder = "SQLServer2012_Developer_with_SP3_x64"
            InstanceName = "NULL"
            Features = "SSMS,ADV_SSMS"
            SetupCredential = $Node.InstallerServiceAccount
            UpdateEnabled = "False"
            #UpdateSource = ""
        }
    } 
}

if this is run then you get very unfriendly error:

VERBOSE: [PLLWINMXMLDBINT]: LCM:  [ Start  Set      ]  [[xSQLServerSetup]SQLMT]
VERBOSE: [PLLWINMXMLDBINT]:                            [[xSQLServerSetup]SQLMT] Importing function 'NetUse'.
VERBOSE: [PLLWINMXMLDBINT]:                            [[xSQLServerSetup]SQLMT] Importing function 'ResolvePath'.
VERBOSE: [PLLWINMXMLDBINT]:                            [[xSQLServerSetup]SQLMT] Importing function 'StartWin32Process'.
VERBOSE: [PLLWINMXMLDBINT]:                            [[xSQLServerSetup]SQLMT] Importing function 'WaitForWin32ProcessEnd'
.
VERBOSE: [PLLWINMXMLDBINT]:                            [[xSQLServerSetup]SQLMT] Path: C:\Install\SQLServer2012_Developer_wi
th_SP3_x64\setup.exe
VERBOSE: [PLLWINMXMLDBINT]:                            [[xSQLServerSetup]SQLMT] Importing function 'NetUse'.
VERBOSE: [PLLWINMXMLDBINT]:                            [[xSQLServerSetup]SQLMT] Importing function 'ResolvePath'.
VERBOSE: [PLLWINMXMLDBINT]:                            [[xSQLServerSetup]SQLMT] Importing function 'StartWin32Process'.
VERBOSE: [PLLWINMXMLDBINT]:                            [[xSQLServerSetup]SQLMT] Importing function 'WaitForWin32ProcessEnd'
.
VERBOSE: [PLLWINMXMLDBINT]:                            [[xSQLServerSetup]SQLMT] Path: C:\Install\SQLServer2012_Developer_wi
th_SP3_x64\setup.exe
VERBOSE: [PLLWINMXMLDBINT]:                            [[xSQLServerSetup]SQLMT] Arguments: /Quiet="True" /IAcceptSQLServerL
icenseTerms="True" /Action="Install" /InstanceName="NULL" /UpdateEnabled="False" /UpdateSource=".\Updates" /Features="SSMS,
ADV_SSMS"
VERBOSE: [PLLWINMXMLDBINT]:                            [[xSQLServerSetup]SQLMT] Process matching path 'C:\Install\SQLServer
2012_Developer_with_SP3_x64\setup.exe' started in process ID 4892
VERBOSE: [PLLWINMXMLDBINT]:                            [[xSQLServerSetup]SQLMT] Importing function 'NetUse'.
VERBOSE: [PLLWINMXMLDBINT]:                            [[xSQLServerSetup]SQLMT] Importing function 'ResolvePath'.
VERBOSE: [PLLWINMXMLDBINT]:                            [[xSQLServerSetup]SQLMT] Importing function 'StartWin32Process'.
VERBOSE: [PLLWINMXMLDBINT]:                            [[xSQLServerSetup]SQLMT] Importing function 'WaitForWin32ProcessEnd'
.
VERBOSE: [PLLWINMXMLDBINT]:                            [[xSQLServerSetup]SQLMT] Path: C:\Install\SQLServer2012_Developer_wi
th_SP3_x64\setup.exe
VERBOSE: [PLLWINMXMLDBINT]: LCM:  [ End    Set      ]  [[xSQLServerSetup]SQLMT]  in 86.4380 seconds.
PowerShell DSC resource MSFT_xSQLServerSetup  failed to execute Set-TargetResource functionality with error message: 
Set-TargetResouce failed 
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : ProviderOperationExecutionFailure
    + PSComputerName        : PLLWINMXMLDBINT.cig.local

The SendConfigurationApply function did not succeed.
    + CategoryInfo          : NotSpecified: (root/Microsoft/...gurationManager:String) [], CimException
    + FullyQualifiedErrorId : MI RESULT 1
    + PSComputerName        : PLLWINMXMLDBINT.cig.local

VERBOSE: Operation 'Invoke CimMethod' complete.
VERBOSE: Time taken for configuration job to complete is 90.163 seconds

To get actual error you need to dig into SQL Setup log on target machine:

Exception summary:
The following is an exception stack listing the exceptions in outermost to innermost order
Inner exceptions are being indented

Exception type: Microsoft.SqlServer.Chainer.Infrastructure.InputSettingValidationException
    Message: 
        The folder path, 'C:\Install\SQLServer2012_Developer_with_SP3_x64\Updates', for the UpdateSource parameter does not exist or access is denied. Specify a folder path that exists and can be accessed by SQL Server Setup.
    HResult : 0x84b40000
        FacilityCode : 1204 (4b4)
        ErrorCode : 0 (0000)
    Data: 
      SQL.Setup.FailureCategory = InputSettingValidationFailure
      DisableWatson = true
    Stack: 
        at Microsoft.SqlServer.Chainer.Infrastructure.InputSettingService.LogAllValidationErrorsAndThrowFirstOne(ValidationState vs)
        at Microsoft.SqlServer.Configuration.BootstrapExtension.ValidateChainerSettingAction.ExecuteAction(String actionId)
        at Microsoft.SqlServer.Chainer.Infrastructure.Action.Execute(String actionId, TextWriter errorStream)
        at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.ExecuteActionHelper(TextWriter statusStream, ISequencedAction actionToRun, ServiceContainer context)
    Inner exception type: Microsoft.SqlServer.Configuration.SmartSetupExtension.InvalidUpdateSourcePath
        Message: 
                The folder path, 'C:\Install\SQLServer2012_Developer_with_SP3_x64\Updates', for the UpdateSource parameter does not exist or access is denied. Specify a folder path that exists and can be accessed by SQL Server Setup.
        HResult : 0x85150001
                FacilityCode : 1301 (515)
                ErrorCode : 1 (0001)
        Data: 
          UpdateSource = C:\Install\SQLServer2012_Developer_with_SP3_x64\Updates
          WatsonData = Microsoft.SqlServer.Configuration.SmartSetupExtension.InvalidUpdateSourcePath@1

It looks that SQL Setup will check for updates folder even thou you specify not to use updates.
I think better option to have a default of MU instead of .\Updates which will result in better user experience.

Regards Arturas K.

SqlSetup: Trouble installing the feature RS_SHPWFE

When i try running xSqlServerSetup with the feature RS_SHPWFE (Reporting
Services Add-in for SharePoint Products) the dsc-configuration throws "Set-Tar
getResouce failed" even though the feature actually gets installed.

It looks like the Test-TargetResource can't handle this feature and will always consider it not installed even though it actually is.

The result of this is that the Set-TargetResource always triggers and always fails, while the setup is actually OK.

Enhancement Request...Surface Area Configuration

Could a DSC resource be built in order to perform different Surface Area Configurations? Examples in TSQL would include:
exec sp_configure 'show advanced options', 1;
RECONFIGURE;
exec sp_configure 'Agent', 1;
exec sp_configure 'Agent XPs', 1;
exec sp_configure 'optimize for ad hoc workloads', 1
RECONFIGURE;

xSQLAOGroupEnsure hard TCP port is hardcoded to 5022 Change to variable with defaulted valued of 5022