SqlScript: Inconsistency in properties validation for some commands

[gillg]

Problem description

Probably due to a change in a background lib (ps version or something else ?) a previous command which was working is not working anymore. But that highlights an inconsistency in the script SqlScript parameters validation.

The Variable is optional but the script fails now if we don't define it.

Verbose logs

VERBOSE: Perform operation 'Invoke CimMethod' with following parameters, ''methodName' = ResourceTest,'className' = MSFT_DSCLocalConfigurationManager,'namespaceName' = root/Microsoft/Windows/DesiredStateConfiguration'.
VERBOSE: An LCM method call arrived from computer XXXXXXXXXX with user sid S-1-5-21-2578696660-564525311-3200226434-1010.
VERBOSE: [XXXXXXXXXX]: LCM:  [ Start  Test     ]  [[SqlScript]DirectResourceAccess]
VERBOSE: [XXXXXXXXXX]:                            [[SqlScript]DirectResourceAccess] Determines if the configuration in the Set script is in desired state.
VERBOSE: [XXXXXXXXXX]:                            [[SqlScript]DirectResourceAccess] Executing the Test script from the file path 'C:\var\log\base-db\tmp-test.sql' on the instance 'MSSQLSERVER' on the server
'localhost'.
VERBOSE: [XXXXXXXXXX]:                            [[SqlScript]DirectResourceAccess] Populating RepositorySourceLocation property for module SqlServer.
VERBOSE: [XXXXXXXXXX]:                            [[SqlScript]DirectResourceAccess] Loading module from path 'C:\Program Files\WindowsPowerShell\Modules\SqlServer\22.0.59\SqlNotebook.psm1'.
VERBOSE: [XXXXXXXXXX]:                            [[SqlScript]DirectResourceAccess] Loading module from path 'C:\Program Files\WindowsPowerShell\Modules\SqlServer\22.0.59\SqlServer.psm1'.
VERBOSE: [XXXXXXXXXX]:                            [[SqlScript]DirectResourceAccess] Populating RepositorySourceLocation property for module SqlServer.
VERBOSE: [XXXXXXXXXX]:                            [[SqlScript]DirectResourceAccess] Loading module from path 'C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.1.18256\sqlnotebook.psm1'.
VERBOSE: [XXXXXXXXXX]:                            [[SqlScript]DirectResourceAccess] Loading module from path 'C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.1.18256\SqlServer.psm1'.
VERBOSE: [XXXXXXXXXX]:                            [[SqlScript]DirectResourceAccess] Preferred module SqlServer found. (SQLCOMMON0023)
VERBOSE: [XXXXXXXXXX]:                            [[SqlScript]DirectResourceAccess] Importing PowerShell module 'SqlServer' with version '22.0.59' from path 'C:\Program
Files\WindowsPowerShell\Modules\SqlServer\22.0.59\SqlServer.psm1'. (SQLCOMMON0025)
Cannot validate argument on parameter 'Variable'. The argument is null or empty. Provide an argument that is not null or empty, and then try the command again.
    + CategoryInfo          : InvalidData: (:) [], CimException
    + FullyQualifiedErrorId : ParameterArgumentValidationError,Invoke-SqlScript
    + PSComputerName        : localhost

VERBOSE: [XXXXXXXXXX]:                            [[SqlScript]DirectResourceAccess] The configuration is in desired state.
VERBOSE: [XXXXXXXXXX]: LCM:  [ End    Test     ]  [[SqlScript]DirectResourceAccess] False in 0.8220 seconds.
The PowerShell DSC resource '[SqlScript]DirectResourceAccess' with SourceInfo '' threw one or more non-terminating errors while running the Test-TargetResource functionality. These errors are logged to the ETW channel called
Microsoft-Windows-DSC/Operational. Refer to this channel for more details.
    + CategoryInfo          : InvalidOperation: (root/Microsoft/...gurationManager:String) [], CimException
    + FullyQualifiedErrorId : NonTerminatingErrorFromProvider
    + PSComputerName        : localhost

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

How to reproduce

Nevermind the content of the sql files below the script below is now faling with logs above.

$DscScriptName = "SqlScript"
$ScriptParams = @{
  ServerName="localhost"; 
  InstanceName="MSSQLSERVER"; 
  TestFilePath="C:\var\log\base-db\tmp-test.sql"; 
  GetFilePath="C:\var\log\base-db\tmp-get.sql"; 
  SetFilePath="C:\var\log\base-db\Installer-Script.sql"; 
  QueryTimeout=1200
}
Invoke-DscResource -Method Test -ModuleName $((Get-DscResource -Name $DscScriptName)[0].ModuleName) -Name $DscScriptName -Verbose:$True -Property $ScriptParams

EDIT: You must use the version 22 of the module SqlServer Install-Module SqlServer -RequiredVersion 22.0.59 -Force

Expected behavior

The script should pass with or without Variable defined.

Current behavior

By adding Variable=@("test=test") in script parameters that works.

Refering to (Get-CimClass -ClassName DSC_SqlScript -Namespace root\Microsoft\Windows\DesiredStateConfiguration).CimClassProperties | fl The "Variable" option can be null

Name               : Variable
Value              :
CimType            : StringArray
Flags              : Property, NullValue
Qualifiers         : {Description, write}
ReferenceClassName :

But the validation made by the powershell engine here is failing https://github.com/dsccommunity/SqlServerDsc/blob/4602416a69867c24c9788dd9afd99b27982ecacd/source/Modules/SqlServerDsc.Common/SqlServerDsc.Common.psm1#L2090

Suggested solution

The powershell function Invoke-SqlScript should defaults to $null for the argument $Variable

Does anyone has an idea why it is working on another server with the exact same module version, sql files, psversiontable, windows build version ?

Operating system the target node is running

OsName               : Microsoft Windows Server 2019 Datacenter
OsOperatingSystemSKU : DatacenterServerEdition
OsArchitecture       : 64-bit
WindowsVersion       : 1809
WindowsBuildLabEx    : 17763.1.amd64fre.rs5_release.180914-1434
OsLanguage           : en-US
OsMuiLanguages       : {en-US}

PowerShell version and build the target node is running

Name                           Value
----                           -----
PSVersion                      5.1.17763.3770
PSEdition                      Desktop
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}
BuildVersion                   10.0.17763.3770
CLRVersion                     4.0.30319.42000
WSManStackVersion              3.0
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1

Module version used

Name         Version Path
----         ------- ----
SqlServerDsc 16.0.0  C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc\16.0.0\SqlServerDsc.psd1

(also tried with 16.1.0 with the exact same behaviour)

[gillg]

I just found a slight difference between a working server and a failing server:

Working: [[SqlScript]DirectResourceAccess] Importing PowerShell module 'SqlServer' with version '21.1.18256' from path 'C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.1.18256\SqlServer.psm1'. (SQLCOMMON0025) Failing: [[SqlScript]DirectResourceAccess] Importing PowerShell module 'SqlServer' with version '22.0.59' from path 'C:\Program Files\WindowsPowerShell\Modules\SqlServer\22.0.59\SqlServer.psm1'. (SQLCOMMON0025)

I made the test... It's the root cause !

The new version 22.0.59 was just published yesterday... I'm very unlucky but that means SqlServerDsc will probably have many unexpected issues very soon !

You should ask for Get-InstalledModule -Name SqlServer in your issue template now 😅

[johlju]

You should ask for Get-InstalledModule -Name SqlServer in your issue template now

We do. In the template for "Problem with resource". 🙂

 Get-Module -Name '*sql*' -ListAvailable | ? Name -ne 'SqlServerDsc' | ft Name,Version,Path

[johlju]

I saw that the new version of SqlServer was released so it might break stuff. It works with the preview version (22.0.49-preview), but apparently there are more breaking changes in this new release.

The PowerShell function Invoke-SqlScript should defaults to $null for the argument $Variable.

It should only pass in the parameter Variable if it is passed to the function. So it would need to be removed if it is not passed.

    if (-not $PSBoundParameters.ContainsKey('Variable'))
    {
       $PSBoundParameters.Remove('Variable')
    }

But instead of continue on with this "reverse-logic" it could be simplified to add parameters to a new hashtable that is passed to Invoke-SqlCmd, the new hastable will then contain the parameter if it is part of $PSBoundParameters.

[johlju]

I can review a PR that fixes this if someone sends one in.