Running CIS Tests fails but passes when in single test alone

[AndreasEDA]

General Troubleshooting steps

• [ X] Verified running the latest release of dbachecks? Yes, 3.1.0 Preview, but same on older version (3.0.1 and 3.0.2)

Version Information

• Operating System (Name|Version): Windows 2022 Core
• PowerShell Version: 5.1
• SQL Server (Edition|Version): SQL Enterprise 2022

Steps to Reproduce

Running the following with an SQL Service Account (Without local Admin permissions):

1. $srv = $env:computername
2. Set-DbcCisConfig
3. set-dbcconfig -name skip.security.PublicPermission -value $false
4. set-dbcconfig -name skip.instance.SQLMailXPsDisabled -value $true
5. set-dbcconfig -name policy.security.databasemailenabled -Value $true
6. set-dbcconfig -name policy.security.xpcmdshelldisabled -Value $true
7. set-dbcconfig -name policy.security.scanforstartupproceduresdisabled -Value $true
8. Invoke-DbcCheck -Check EngineServiceAdmin -ComputerName $srv -SqlInstance $srv -PassThru -Strict

->Single Check EngineServiceAdmin has passed without error:

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Key Value

---

Tag {EngineServiceAdmin}
ExcludeTag
Script C:\Program Files\WindowsPowerShell\Modules\dbachecks\3.1.0\checks\Instance.Tests.ps1
PassThru True
Strict True

Pester v4.10.1
Executing all tests in 'C:\Program Files\WindowsPowerShell\Modules\dbachecks\3.1.0\checks\Instance.Tests.ps1' with Tags EngineServiceAdmin

Executing script C:\Program Files\WindowsPowerShell\Modules\dbachecks\3.1.0\checks\Instance.Tests.ps1

Describing SQL Engine Service Admin

Context Testing whether SQL Engine account is a local administrator on XXXXX
  [+] The SQL Engine service account should not be a local administrator on XXXXX 7ms

Tests completed in 1.23s
Tests Passed: 1, Failed: 0, Skipped: 0, Pending: 0, Inconclusive: 0

TagFilter : {EngineServiceAdmin}
ExcludeTagFilter :
TestNameFilter :
ScriptBlockFilter :
TotalCount : 1
PassedCount : 1
FailedCount : 0
SkippedCount : 0
PendingCount : 0
InconclusiveCount : 0
Time : 00:00:01.2285526
TestResult : {@{ErrorRecord=; ParameterizedSuiteName=; Describe=SQL Engine Service Admin;
Parameters=System.Collections.Specialized.OrderedDictionary; Passed=True;
Show=All; FailureMessage=; Time=00:00:00.0078503; Name=The SQL Engine service
account should not be a local administrator on XXXXX; Result=Passed;
Context=Testing whether SQL Engine account is a local administrator on
XXXXX; StackTrace=}}

XXXXXXXXXXXXXXXXXXXXXXXX

9. Now the CIS tests the same EngineServiceAdmin Check fails. BUT when i run the CIS Check as local admin it passes. So why the same check passes when executed alone but fails when executed in the CIS Check?

Command:
Invoke-DbcCheck -Check CIS -ExcludeCheck LoginAuditSuccessful,NonStandardPort,SQLMailXPsDisabled,LoginMustChange,ContainedDBSQLAuth -ComputerName $srv -SqlInstance $srv -PassThru -Strict

Error Message:

Describing SQL Engine Service Admin

Context Testing whether SQL Engine account is a local administrator on XXXXX
  [-] The SQL Engine service account should not be a local administrator on XXXXX 551ms
    Expected $false, because We expected the service account for the SQL Engine to not be a local administrator, but got 'We Could not Connect to $Instance'.
    1018:     $AllInstanceInfo.EngineServiceAdmin.Exist | Should -Be $false -Because "We expected the service account for the SQL Engine to not be a local administrator"
    at Assert-EngineServiceAdmin, C:\Program Files\WindowsPowerShell\Modules\dbachecks\3.1.0\internal\assertions\Instance.Assertions.ps1: line 1018
    at <ScriptBlock>, C:\Program Files\WindowsPowerShell\Modules\dbachecks\3.1.0\checks\Instance.Tests.ps1: line 1040

---

Result:
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXx
Key Value

---

ExcludeTag {LoginAuditSuccessful, NonStandardPort, SQLMailXPsDisabled, LoginMustChange...}
Tag {CIS}
Script {C:\Program Files\WindowsPowerShell\Modules\dbachecks\3.1.0\checks\Agent.Tests.ps1, C...
PassThru True
Strict True

Pester v4.10.1
Executing all tests in 'C:\Program Files\WindowsPowerShell\Modules\dbachecks\3.1.0\checks\Agent.Tests.ps1', 'C:\Program Files\WindowsPowerShell\Modules\dbachecks\3.1.0\checks\Database.Tests.ps1', 'C:\Program Files\WindowsPowerShell\Modules\dbachecks\3.1.0\checks\Instance.Tests.ps1', 'C:\Program Files\WindowsPowerShell\Modules\dbachecks\3.1.0\checks\Server.Tests.ps1' with Tags CIS

Executing script C:\Program Files\WindowsPowerShell\Modules\dbachecks\3.1.0\checks\Agent.Tests.ps1

Describing Database Mail XPs

Context Testing Database Mail XPs on XXXXX
  [+] Testing Database Mail XPs is set to True on XXXXX 1.66s

Executing script C:\Program Files\WindowsPowerShell\Modules\dbachecks\3.1.0\checks\Database.Tests.ps1

Describing Trustworthy Option

Context Testing database trustworthy option on XXXXX
  [+] Database AdminDB should have Trustworthy set to false on XXXXX 86ms
  [+] Database master should have Trustworthy set to false on XXXXX 2ms
  [+] Database model should have Trustworthy set to false on XXXXX 1ms
  [+] Database ORDIPRONEW should have Trustworthy set to false on XXXXX 6ms
  [+] Database ordiprorep should have Trustworthy set to false on XXXXX 1ms
  [+] Database ordiprorepTempDB should have Trustworthy set to false on XXXXX 2ms
  [+] Database tempdb should have Trustworthy set to false on XXXXX 1ms
  [+] Database TemporaryUserDB should have Trustworthy set to false on XXXXX 1ms

Describing Database Orphaned User

Context Testing database orphaned user event on XXXXX
  [+] Database AdminDB should return 0 orphaned user on XXXXX 141ms
  [+] Database master should return 0 orphaned user on XXXXX 109ms
  [+] Database model should return 0 orphaned user on XXXXX 96ms
  [+] Database msdb should return 0 orphaned user on XXXXX 123ms
  [+] Database ORDIPRONEW should return 0 orphaned user on XXXXX 111ms
  [+] Database ordiprorep should return 0 orphaned user on XXXXX 113ms
  [+] Database ordiprorepTempDB should return 0 orphaned user on XXXXX 108ms
  [+] Database tempdb should return 0 orphaned user on XXXXX 93ms
  [+] Database TemporaryUserDB should return 0 orphaned user on XXXXX 99ms

Describing Contained Database Auto Close

Context Testing contained database auto close option on XXXXX
  [+] Database ORDIPRONEW should have auto close set to false on XXXXX 16ms

Describing CLR Assemblies SAFE_ACCESS

Context Testing that all user-defined CLR assemblies are set to SAFE_ACCESS on XXXXX
  [+] Database AdminDB user-defined CLR assemblies are set to SAFE_ACCESS on XXXXX 100ms
  [+] Database master user-defined CLR assemblies are set to SAFE_ACCESS on XXXXX 81ms
  [+] Database model user-defined CLR assemblies are set to SAFE_ACCESS on XXXXX 78ms
  [+] Database msdb user-defined CLR assemblies are set to SAFE_ACCESS on XXXXX 76ms
  [+] Database ORDIPRONEW user-defined CLR assemblies are set to SAFE_ACCESS on XXXXX 75ms
  [+] Database ordiprorep user-defined CLR assemblies are set to SAFE_ACCESS on XXXXX 82ms
  [+] Database ordiprorepTempDB user-defined CLR assemblies are set to SAFE_ACCESS on XXXXX 77ms
  [+] Database tempdb user-defined CLR assemblies are set to SAFE_ACCESS on XXXXX 67ms
  [+] Database TemporaryUserDB user-defined CLR assemblies are set to SAFE_ACCESS on XXXXX 80ms

Describing Guest User

Context Testing Guest user has CONNECT permission on XXXXX
  [+] Database Guest user should return no CONNECT permissions in AdminDB on XXXXX 843ms
  [+] Database Guest user should return no CONNECT permissions in model on XXXXX 817ms
  [+] Database Guest user should return no CONNECT permissions in ORDIPRONEW on XXXXX 942ms
  [+] Database Guest user should return no CONNECT permissions in ordiprorep on XXXXX 955ms
  [+] Database Guest user should return no CONNECT permissions in ordiprorepTempDB on XXXXX 845ms
  [+] Database Guest user should return no CONNECT permissions in TemporaryUserDB on XXXXX 850ms

Describing AsymmetricKeySize

Context Testing Asymmetric Key Size is 2048 or higher on XXXXX
  [+] Database AdminDB Asymmetric Key Size should be at least 2048 on XXXXX 70ms
  [+] Database model Asymmetric Key Size should be at least 2048 on XXXXX 69ms
  [+] Database ORDIPRONEW Asymmetric Key Size should be at least 2048 on XXXXX 65ms
  [+] Database ordiprorep Asymmetric Key Size should be at least 2048 on XXXXX 68ms
  [+] Database ordiprorepTempDB Asymmetric Key Size should be at least 2048 on XXXXX 75ms
  [+] Database TemporaryUserDB Asymmetric Key Size should be at least 2048 on XXXXX 70ms

Describing SymmetricKeyEncryptionLevel

Context Testing Symmetric Key Encryption Level at least AES_128 or higher on XXXXX
  [+] Database AdminDB Symmetric Key Encryption Level should have AES_128 or higher on XXXXX 71ms
  [+] Database model Symmetric Key Encryption Level should have AES_128 or higher on XXXXX 71ms
  [+] Database ORDIPRONEW Symmetric Key Encryption Level should have AES_128 or higher on XXXXX 77ms
  [+] Database ordiprorep Symmetric Key Encryption Level should have AES_128 or higher on XXXXX 64ms
  [+] Database ordiprorepTempDB Symmetric Key Encryption Level should have AES_128 or higher on XXXXX 73ms
  [+] Database TemporaryUserDB Symmetric Key Encryption Level should have AES_128 or higher on XXXXX 73ms

Executing script C:\Program Files\WindowsPowerShell\Modules\dbachecks\3.1.0\checks\Instance.Tests.ps1

Describing Dedicated Administrator Connection

Context Testing Dedicated Administrator Connection on XXXXX
  [+] DAC is set to False on XXXXX 50ms

Describing SA Login Renamed

Context Checking that sa login has been renamed on XXXXX
  [+] sa login has been renamed on XXXXX 4ms

Describing SA Login Disabled

Context Checking that sa login has been disabled on XXXXX
  [+] sa login is disabled on XXXXX 8ms

Describing Login SA cannot exist

Context Checking that a login named sa does not exist on XXXXX
  [+] sa login does not exist on XXXXX 5ms

Describing OLE Automation

Context Testing OLE Automation on XXXXX
  [+] OLE Automation is set to False on XXXXX 46ms

Describing Error Log Count

Context Checking error log count on XXXXX
  [+] Error log count should be greater or equal to 12 on XXXXX 135ms

Describing CLR Enabled

Context Testing CLR Enabled on XXXXX
  [+] CLR Enabled is set to False on XXXXX 50ms

Describing Cross Database Ownership Chaining

Context Testing Cross Database Ownership Chaining on XXXXX
  [+] Cross Database Ownership Chaining should be disabled on XXXXX 5ms

Describing Ad Hoc Distributed Queries

Context Testing Ad Hoc Distributed Queries on XXXXX
  [+] Ad Hoc Distributed Queries is set to False on XXXXX 44ms

Describing XP CmdShell

Context Testing XP CmdShell on XXXXX
  [+] XPCmdShell is set to True on XXXXX 40ms

Describing Scan For Startup Procedures

Context Testing Scan For Startup Procedures on XXXXX
  [+] Scan For Startup Procedures is set to True on XXXXX 4ms

Describing Default Trace

Context Checking Default Trace on XXXXX
  [+] The Default Trace should be enabled on XXXXX 4ms

Describing OLE Automation Procedures Disabled

Context Checking OLE Automation Procedures on XXXXX
  [+] The OLE Automation Procedures should be disabled on XXXXX 9ms

Describing Remote Access Disabled

Context Testing Remote Access on XXXXX
  [+] The Remote Access should be disabled on XXXXX 5ms

Describing Latest Build

Context Testing Latest Build on XXXXX
  [+] The Latest Build of SQL should be installed on XXXXX 4ms

Describing Login BUILTIN Administrators cannot exist

Context Checking that a login named BUILTIN\Administrators does not exist on XXXXX
  [+] BUILTIN\Administrators login does not exist on XXXXX 5ms

Describing Local Windows Groups Not Have SQL Logins

Context Checking that local Windows groups do not have SQL Logins on XXXXX
  [+] Local Windows groups should not SQL Logins on XXXXX 5ms

Describing Failed Login Auditing

Context Testing if failed login auditing is in place on XXXXX
  [+] The failed login auditing should be set on XXXXX 93ms

Describing SqlAgentProxiesNoPublicRole

Context Testing to see if the public role has access to the SQL Agent proxies on XXXXX
  [+] The public role should not have access to the SQL Agent Proxies on XXXXX 86ms

Describing Hide Instance

Context Checking the Hide an Instance of SQL Server Database Engine property on XXXXX
  [+] The Hide an Instance of SQL Server Database Engine property on SQL Server instance XXXXX 6ms

Describing SQL Engine Service Admin

Context Testing whether SQL Engine account is a local administrator on XXXXX
  [-] The SQL Engine service account should not be a local administrator on XXXXX 551ms
    Expected $false, because We expected the service account for the SQL Engine to not be a local administrator, but got 'We Could not Connect to $Instance'.
    1018:     $AllInstanceInfo.EngineServiceAdmin.Exist | Should -Be $false -Because "We expected the service account for the SQL Engine to not be a local administrator"
    at Assert-EngineServiceAdmin, C:\Program Files\WindowsPowerShell\Modules\dbachecks\3.1.0\internal\assertions\Instance.Assertions.ps1: line 1018
    at <ScriptBlock>, C:\Program Files\WindowsPowerShell\Modules\dbachecks\3.1.0\checks\Instance.Tests.ps1: line 1040

Describing SQL Agent Service Admin

Context Testing whether SQL Agent account is a local administrator on XXXXX
  [-] The SQL Agent service account should not be a local administrator on XXXXX 6ms
    Expected $false, because We expected the service account for the SQL Agent to not be a local administrator, but got 'We Could not Connect to $Instance'.
    1013:     $AllInstanceInfo.AgentServiceAdmin.Exist | Should -Be $false -Because "We expected the service account for the SQL Agent to not be a local administrator"
    at Assert-AgentServiceAdmin, C:\Program Files\WindowsPowerShell\Modules\dbachecks\3.1.0\internal\assertions\Instance.Assertions.ps1: line 1013
    at <ScriptBlock>, C:\Program Files\WindowsPowerShell\Modules\dbachecks\3.1.0\checks\Instance.Tests.ps1: line 1060

Describing SQL Full Text Service Admin

Context Testing whether SQL Full Text account is a local administrator on  XXXXX
  [-] The SQL Full Text service account should not be a local administrator on XXXXX 5ms
    Expected $false, because We expected the service account for the SQL Full Text to not be a local administrator, but got 'We Could not Connect to $Instance'.
    1023:     $AllInstanceInfo.FullTextServiceAdmin.Exist | Should -Be $false -Because "We expected the service account for the SQL Full Text to not be a local administrator"
    at Assert-FullTextServiceAdmin, C:\Program Files\WindowsPowerShell\Modules\dbachecks\3.1.0\internal\assertions\Instance.Assertions.ps1: line 1023
    at <ScriptBlock>, C:\Program Files\WindowsPowerShell\Modules\dbachecks\3.1.0\checks\Instance.Tests.ps1: line 1080

Describing Login Check Policy

Context Testing if the CHECK_POLICY is enabled on all logins on XXXXX
  [-] All logins should have the CHECK_POLICY option set to ON on XXXXX 288ms
    Expected 0, because We expected the CHECK_POLICY for the all logins to be enabled, but got 'We Could not Connect to $Instance'.
    1027:     $AllInstanceInfo.LoginCheckPolicy.Count | Should -Be 0 -Because "We expected the CHECK_POLICY for the all logins to be enabled"
    at Assert-LoginCheckPolicy, C:\Program Files\WindowsPowerShell\Modules\dbachecks\3.1.0\internal\assertions\Instance.Assertions.ps1: line 1027
    at <ScriptBlock>, C:\Program Files\WindowsPowerShell\Modules\dbachecks\3.1.0\checks\Instance.Tests.ps1: line 1096

Describing Login Password Expiration

Context Testing if the login password expiration is enabled for sql logins in the sysadmin role on XXXXX
  [-] All sql logins should have the password expiration option set to ON in the sysadmin role on XXXXX 6ms
    Expected 0, because We expected the password expiration policy to set on all sql logins in the sysadmin role, but got 'We Could not Connect to $Instance'.
    1032:     $AllInstanceInfo.LoginPasswordExpiration.Count | Should -Be 0 -Because "We expected the password expiration policy to set on all sql logins in the sysadmin role"
    at Assert-LoginPasswordExpiration, C:\Program Files\WindowsPowerShell\Modules\dbachecks\3.1.0\internal\assertions\Instance.Assertions.ps1: line 1032
    at <ScriptBlock>, C:\Program Files\WindowsPowerShell\Modules\dbachecks\3.1.0\checks\Instance.Tests.ps1: line 1113

Describing Public Role Permissions

Context Testing if the public role permissions don't have permissions  on XXXXX
  [+] All permissions should be set to CIS standards on the public role on XXXXX 5ms

Describing SQL Browser Service

Context Testing SQL Browser Service on XXXXX
  [+] SQL Browser service should be Stopped as only one instance is installed on XXXXX 8ms
  [+] SQL Browser service startmode should be Disabled as only one instance is installed on XXXXX 6ms

Executing script C:\Program Files\WindowsPowerShell\Modules\dbachecks\3.1.0\checks\Server.Tests.ps1

Describing Server Protocols

Context Checking SQL Server protocols on XXXXX
  [+] All SQL Server Instances should be configured to run only TCP/IP protocol on XXXXX 6ms

Tests completed in 25.15s
Tests Passed: 70, Failed: 5, Skipped: 0, Pending: 0, Inconclusive: 0

TagFilter : {CIS}
ExcludeTagFilter : {LoginAuditSuccessful, NonStandardPort, SQLMailXPsDisabled, LoginMustChange...}
TestNameFilter :
ScriptBlockFilter :
TotalCount : 75
PassedCount : 70
FailedCount : 5
SkippedCount : 0
PendingCount : 0
InconclusiveCount : 0
Time : 00:00:25.1458317
TestResult : {@{ErrorRecord=; ParameterizedSuiteName=; Describe=Database Mail XPs;
Parameters=System.Collections.Specialized.OrderedDictionary; Passed=True;
Show=All; FailureMessage=; Time=00:00:01.6595378; Name=Testing Database Mail
XPs is set to True on XXXXX; Result=Passed; Context=Testing Database
Mail XPs on XXXXX; StackTrace=}, @{ErrorRecord=;
ParameterizedSuiteName=; Describe=Trustworthy Option;
Parameters=System.Collections.Specialized.OrderedDictionary; Passed=True;
Show=All; FailureMessage=; Time=00:00:00.0860918; Name=Database AdminDB should
have Trustworthy set to false on XXXXX; Result=Passed; Context=Testing
database trustworthy option on XXXXX; StackTrace=}, @{ErrorRecord=;
ParameterizedSuiteName=; Describe=Trustworthy Option;
Parameters=System.Collections.Specialized.OrderedDictionary; Passed=True;
Show=All; FailureMessage=; Time=00:00:00.0020350; Name=Database master should
have Trustworthy set to false on XXXXX; Result=Passed; Context=Testing
database trustworthy option on XXXXX; StackTrace=}, @{ErrorRecord=;
ParameterizedSuiteName=; Describe=Trustworthy Option;
Parameters=System.Collections.Specialized.OrderedDictionary; Passed=True;
Show=All; FailureMessage=; Time=00:00:00.0018695; Name=Database model should
have Trustworthy set to false on XXXXX; Result=Passed; Context=Testing
database trustworthy option on XXXXX; StackTrace=}...}
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXx

[AndreasEDA]

ok The problem is that hideinstance check requires elevation. And even if i disable the check with
set-dbcconfig -name skip.security.hideinstance -value $true
it still checks it! I have to look at the code...
Any suggestion to completely disable this check?

[AndreasEDA]

ok, indeed, Skip the hideinstance check does not prevent from checking it.... I'm looking for a way to really prevent to execute this check....

[AndreasEDA]

It seems that the Get-CheckInformation function does return "HideInstance" even if you pass it as the parameter "$ExcludeCheck"

eg.
Invoke-DbcCheck -Check CIS -ExcludeCheck Hideinstance -ComputerName $srv -SqlInstance $srv

->The tags returned from Get-CheckInformation does include "Hideinstance" tag also. So that is the reason the check is also executed. Should i make a bug report?

[AndreasEDA]

I just changed the Get-CheckInformation function and i have added this line at the bottom (Just before"Return $CheckInfo"

$CheckInfo = $CheckInfo |Where-Object {$item = $; -not $ExcludeCheck.Where({$item -like "${}*"}, 'First')}

->That removes from the $Checkinfo array all the items in the $ExcludeCheck Array

->This works for me now. I hope you can update the Get-CheckInformation function
Thank you very much