SqlTraceFlag: Cannot bind argument to parameter 'ReferenceObject' because it is null.

[joshshand]

Details of the scenario you tried and the problem that is occurring

Re-testing SqlTraceFlag with basic configuration.

Assemblies are now loaded as per resolved issue #1680 but now running into new problems 😢 .

First error thrown relates to Test-TargetResource where TraceFlags is compared with Actual TraceFlags: https://github.com/dsccommunity/SqlServerDsc/blob/main/source/DSCResources/DSC_SqlTraceFlag/DSC_SqlTraceFlag.psm1#L375

Not sure why ReferenceObject would be null - I tried running sections of the code manually and it seems to generate the required object.

@Fiander @johlju you may have interest in this. Thanks

Verbose logs showing the problem

VERBOSE: Perform operation 'Invoke CimMethod' with following parameters, ''methodName' = SendConfigurationApply,'className' = MSFT_DSCLocalConfigurationManager,'namespaceName' = root/Microsoft/Windows/Desi
redStateConfiguration'.
VERBOSE: An LCM method call arrived from computer SOMENAME with user sid SOMESID.
VERBOSE: [SOMENAME]: LCM:  [ Start  Set      ]
VERBOSE: [SOMENAME]: LCM:  [ Start  Resource ]  [[SqlTraceFlag]Set_SqlTraceFlags]
VERBOSE: [SOMENAME]: LCM:  [ Start  Test     ]  [[SqlTraceFlag]Set_SqlTraceFlags]
VERBOSE: [SOMENAME]:                            [[SqlTraceFlag]Set_SqlTraceFlags] Determines the current state of the TraceFlags Compared to the desired TraceFlags 'MSSQLSERVER'.
VERBOSE: [SOMENAME]:                            [[SqlTraceFlag]Set_SqlTraceFlags] Information: PowerShell module SqlServer not found, trying to use older SQLPS module. (SQLCOMMON0024)
VERBOSE: [SOMENAME]:                            [[SqlTraceFlag]Set_SqlTraceFlags] Importing PowerShell module 'SQLPS' with version '1.0' from path 'C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Pow
erShell\Modules\SQLPS\SQLPS.psd1'. (SQLCOMMON0025)
VERBOSE: [SOMENAME]:                            [[SqlTraceFlag]Set_SqlTraceFlags] Get the current TraceFlags that are set on instance MSSQLSERVER.
Cannot bind argument to parameter 'ReferenceObject' because it is null.
    + CategoryInfo          : InvalidData: (:) [], CimException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.CompareObjectCommand
    + PSComputerName        : localhost
 
VERBOSE: [SOMENAME]: LCM:  [ End    Test     ]  [[SqlTraceFlag]Set_SqlTraceFlags]  in 2.7510 seconds.
The PowerShell DSC resource '[SqlTraceFlag]Set_SqlTraceFlags' with SourceInfo 'C:\users\SOME_USERNAME\Desktop\Example.ps1::8::9::SqlTraceFlag' 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: (:) [], CimException
    + FullyQualifiedErrorId : NonTerminatingErrorFromProvider
    + PSComputerName        : localhost
 
VERBOSE: [SOMENAME]: LCM:  [ End    Set      ]
The SendConfigurationApply function did not succeed.
    + CategoryInfo          : NotSpecified: (root/Microsoft/...gurationManager:String) [], CimException
    + FullyQualifiedErrorId : MI RESULT 1
    + PSComputerName        : localhost
 
VERBOSE: Operation 'Invoke CimMethod' complete.
VERBOSE: Time taken for configuration job to complete is 3.989 seconds

Suggested solution to the issue

N/A

The DSC configuration that is used to reproduce the issue (as detailed as possible)

Configuration Example
{

    Import-DscResource -ModuleName 'SqlServerDsc'

    node localhost
    {
        SqlTraceFlag 'Set_SqlTraceFlags'
        {
            InstanceName         = 'MSSQLSERVER'
            TraceFlags           = 3226
            RestartService       = $true
        }
    }
}

Example

SQL Server edition and version the target node is running

Microsoft SQL Server 2016 (SP2-CU15-GDR) (KB4583461) - 13.0.5865.1 (X64)

SQL Server PowerShell modules present on the target node

Name  Version Path
----  ------- ----
SQLPS 1.0     C:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules\SQLPS\SQLPS.psd1

The 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}

Version and build of PowerShell the target node is running

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

Version of the DSC module that was used

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

[Fiander]

Hmm... strange.
But: in my lab, i have 10 sql servers, all equal, but when i work on one of them with powershell, wmi fails. On all others wmi just works fine.
I found this reference: https://www.reddit.com/r/PowerShell/comments/dvg1iu/cannot_find_type/

@johlju What do you think? Could this be the same? If so, how do you want this to be fixed?

[johlju]

@joshshand Two things to test to rule things out.

1. Could you add ServerName to the configuration with hosts FQDN. It defaults to the helper function Get-ComputerName that returns $env:COMPUTERNAME (below). Let's rule out a bug here by overriding ServerName.

SqlServerDsc/source/DSCResources/DSC_SqlTraceFlag/DSC_SqlTraceFlag.psm1

Line 29 in d7cddae

$ServerName = (Get-ComputerName),

2. I see you are using module SQLPS that is bundle with SQL Server. Could you possible try using the module SqlServer from PowerShell Gallery to see if gives the same error (SqlServer is not the same as SqlServerDsc, but a replacement of built-in SQLPS). Just to rule out a difference between SQLPS and SqlServer. The assembly used differ between SQLPS (in GAC) and in SqlServer (most likely newer bundle with the module). SQLPS should work if possible, but might be something here that differ between them that need to be taken account for.
   The function Get-TargetResource uses the type Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer:

SqlServerDsc/source/DSCResources/DSC_SqlTraceFlag/DSC_SqlTraceFlag.psm1

Line 43 in d7cddae

$sqlManagement = New-Object -TypeName 'Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer' -ArgumentList $ServerName

[joshshand]

@johlju and @Fiander I hopefully have a decent update for you here:

1. Explicit servername made no difference - same issue
2. Removed SQLPS module from environment variables and installed SqlServer. Tested with verbose output, correct SqlServer module referenced. Same error.
3. Added some debug lines to DSC_SqlTraceFlag.psm1 @ L360:

Write-Verbose "BREAK"
Write-Verbose "getTargetResourceResult:"

$stringOut = $getTargetResourceResult | Out-String
Write-Verbose $stringOut

In my console output:

Key                 Value       Name
---                 -----       ----
TraceFlagsToInclude             TraceFlagsToInclude
RestartService                  RestartService
InstanceName        MSSQLSERVER InstanceName
TraceFlagsToExclude             TraceFlagsToExclude
TraceFlags                      TraceFlags
ServerName          PARTIALS    ServerName
RestartTimeout                  RestartTimeout

I noticed TraceFlags is in fact null...because when I have a "fresh" server there are no flags set.
4. Manually added a TraceFlag for 3226 via sql configuration manager. Re-Test DSC. Passes with no failure.
5. Add NEW TraceFlag to DSC 3226, 834 and apply. Also works and applies both Trace Flags.
6. I can only conclude that logic needs to be added for https://github.com/dsccommunity/SqlServerDsc/blob/main/source/DSCResources/DSC_SqlTraceFlag/DSC_SqlTraceFlag.psm1#L375 to handle the possibility of no initial TraceFlags i.e. null.

[Fiander]

Just found that for the Test-TargetResource 5 test were missing. The tests with an empty server. Just added those tests, and they failed.
Wrote a fix in Test-TargetResource sow it would not fail the tests anymore.

Its kind of busy at the real job at the moment, sow i did not have the time yet to use this module myself.

[johlju]

@joshshand great job debugging this!

@Fiander thank you for taking the time to make a fix for this! I will review it. And, no worries - bugs happen - part of life 😄 Awesome job! 🙇

[Fiander]

heej, I'm not worried :-) just stupid for forgetting those tests. Mainly because i already did all the preparation for those tests.

[johlju]

It happens to everyone. When coding one is so in the code so one forget stuff. I missed it in the review too. 😅

[joshshand]

Thanks again for sorting this, look forward to testing the next release!

[johlju]

The release will be available shortly, it is building now.

[joshshand]

Hey @Fiander @johlju - Just a final message to say thanks.

I have tested 15.1.1 and can confirm SqlTraceFlag now appears to be working!

Appreciate your time and effort.

Cheers from NZ