'Variable' null/empty for SQL Server 2016 instance STIG

[jmetzger-novetta]

I'm attempting to run the SQL Server 2016 instance STIG on a SQL Server 2019 instance (which should be OK as mentioned in https://github.com/microsoft/PowerStig/issues/730).

Here's my script:

configuration DSCPowerStigConfig
{
	Import-DscResource -ModuleName PowerStig
	
	Node localhost
        {
		SqlServer SqlServerSettings
		{
			StigVersion = '2.10'
			SqlRole = 'Instance'
			ServerInstance = 'INSTANCE-NAME'
			SqlVersion = '2016'			
		}	
	}
}

DSCPowerStigConfig -Verbose
Start-DscConfiguration -Path '.\DSCPowerStigConfig' -Wait -Verbose -Force

When executed, all the SqlScriptQueryRule items (except V-214029) hit an exception at the 'test' step:

VERBOSE: [HOSTNAME]: [[SqlScriptQuery][V-213939][medium][SRG-APP-000091-DB-000325]MSSQLSERVER::[SqlServer]SqlServerSettings]
 Executing the Test query on the instance 'MSSQLSERVER' on the server 'HOSTNAME'.
VERBOSE: [HOSTNAME]: [[SqlScriptQuery][V-213939][medium][SRG-APP-000091-DB-000325]MSSQLSERVER::[SqlServer]SqlServerSettings]
 Found PowerShell module SqlServer already imported in the session. (SQLCOMMON0026)
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

There aren't any organizational settings for these rules and there do not appear to be any variables used in the test script. The exception remains if I attempt to delete the empty <Variable /> and <VariableValue /> elements from SqlServer-2016-Instance-2.10.xml, but the STIG will run fine if I enter dummy values:

      <Variable>x={0}</Variable>
      <VariableValue>x</VariableValue>

Is there something I need to add into the DSC config or the organizational settings file to prevent this exception? Or is this a bug?

[erjenkin]

I would assume you are missing the audit directory based on that rule. Can you try to use the whole dsc config from that example? The path needs to be "'C:\Audits'

configuration Example
{
    param
    (
        [parameter()]
        [string]
        $NodeName = 'localhost'
    )

    Import-DscResource -ModuleName PowerStig

    Node localhost
    {

        File SqlAudit
        {
            Ensure = "Present"
            Type   = "Directory"
            DestinationPath = "C:\Audits"
        }

        SqlServer BaseLine
        {
            SqlVersion     = '2016'
            SqlRole        = 'Instance'
            ServerInstance = 'SQL2019onServer'
        }
    }
}

Example

[jmetzger-novetta]

I'm not sure that's it -- I had manually created the C:\Audits directory previously.

I also deleted the directory and then ran the example config with the additional File block to ensure the C:\Audits dir is present. After starting this configuration, I see that C:\Audits has been created, but I still see the aforementioned exception for each of the SqlScriptQueryRule entities.

Is there anything else I might be missing?

[erjenkin]

Just deployed SQL2019 on Server 2019 and the configuration passes - with the exception of one rule - V-213975 - guessing because this option does not exist on SQL2019 After skipping that single rule, the configuration applies.

[jmetzger-novetta]

@erjenkin -- thanks so much for testing that out. I think I figured out what the problem is.

The only other PS module I'd installed aside from PowerSTIG was SqlServer, which I needed it to run another piece of software that assesses STIG compliance. It looks like the presence of the SqlServer module was causing the behavior I documented.

To test:

• I compiled your example config.
• I deleted the SqlServer module from my PS modules folder, then applied the example config. The DSC configuration was applied successfully.
• I copied the SqlServer module back into my PS modules folder, removed the config and stopped the DSC process using the snippet below, then re-applied your example config. It generated the exception I logged in my previous post (screenshot below).

Remove-DscConfigurationDocument -Stage Current,Pending,Previous -Verbose -Force
###
### Find the process that is hosting the DSC engine
###
$CimParameters = @{
    ClassName = 'Msft_Providers'
    Filter    = "provider='dsctimer' OR provider='dsccore'"
}
$dscProcessID = Get-CimInstance @CimParameters |
    Select-Object -ExpandProperty HostProcessIdentifier
###
### Stop the process
###
Get-Process -Id $dscProcessID | Stop-Process

• I removed the config and stopped the DSC process once again using the snippet, deleted the SqlServer module from my PS modules folder, then re-applied your example config. The DSC configuration was applied successfully once again.

I'm not sure why the presence of the SqlServer module is causing this behavior, but it seems to be responsible.