Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

DefaultTrace check succeeds when run individually but fails when run as part of Instance checks. #696

Open
garyhampson opened this issue Aug 9, 2019 · 11 comments

Comments

@garyhampson
Copy link

commented Aug 9, 2019

Bug Report

General Troubleshooting steps

  • Verified running the latest release of dbachecks?

Does (Find-Module dbachecks).Version match (Get-Module dbachecks).Version.ToString()

image

  • Verified errors are not related to permissions?
  • Can duplicate in new/clean PowerShell session (clean = powershell -NoProfile)?

Version Information

  • Operating System (Name|Version): Microsoft Windows Server 2016 Datacenter
  • PowerShell Version: 5.1.14393.3053
  • SQL Server (Edition|Version): Microsoft SQL Server 2016 (SP2-CU7-GDR) (KB4505222) - 13.0.5366.0 (X64) Jun 15 2019 08:22:25 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor)

Steps to Reproduce

Invoke-DbcCheck -SqlInstance GARYSQL01 -ComputerName GARYSQL01 -Check Instance
Invoke-DbcCheck -SqlInstance GARYSQL01 -ComputerName GARYSQL01 -Check DefaultTrace

  • Attach any screenshots (if possible/allowed)
  • Attach output from PowerShell console (if possible/allowed)

`Executing script C:\Program Files\WindowsPowerShell\Modules\dbachecks\1.2.12\checks\Instance.Tests.ps1
.
.
.
Describing Ad Hoc Distributed Queries

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

Describing XP CmdShell

Context Testing XP CmdShell on GARYSQL01
  [+] XPCmdShell is set to False on GARYSQL01 44ms

Describing Default Trace

Context Checking Default Trace on GARYSQL01
  [-] The Default Trace should be enabled on GARYSQL01 7ms
    Expected 1, because We expect the Default Trace to be enabled but got, but got 'We Could not Connect to $Instance'.
    117:     $AllInstanceInfo.DefaultTrace.ConfiguredValue | Should -Be 1 -Because "We expect the Default Trace to be enabled but got $($AllInstanceInfo.DefaultTrace.Trace.ConfiguredValue)"
    at Assert-DefaultTrace, C:\Program Files\WindowsPowerShell\Modules\dbachecks\1.2.12\internal\assertions\Instance.Assertions.ps1: line 117
    at <ScriptBlock>, C:\Program Files\WindowsPowerShell\Modules\dbachecks\1.2.12\checks\Instance.Tests.ps1: line 809`

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

Describing Default Trace

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

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

Description of Bug

The DefaultTrace check fails saying it cannot connect to the Sql Instance when run as part of the full Instance checks, but runs successfully when run as an individual check. The preceding checks (Ad-Hoc distributed queries and xp_cmdshell) do not have any issues connecting to the instance when run as part of the full Instance checks.

@garethnewman

This comment has been minimized.

Copy link

commented Aug 13, 2019

can re-produce, will have a look. Thanks.

@garethnewman

This comment has been minimized.

Copy link

commented Aug 13, 2019

issue seems to be with $There at this block in Instance.Asserstions.ps1, we hit the else when running instance checks

'DefaultTrace' { if ($There) { try { $SpConfig = Get-DbaSpConfigure -SqlInstance $Instance -ConfigName 'DefaultTraceEnabled' $DefaultTrace = [pscustomobject] @{ ConfiguredValue = $SpConfig.ConfiguredValue } } catch { $There = $false $DefaultTrace = [pscustomobject] @{ ConfiguredValue = 'Exception: We Could not Connect to $Instance' } } } else { $There = $false $DefaultTrace = [pscustomobject] @{ ConfiguredValue = 'We Could not Connect to $Instance' } } }

Note: I put the word exception in there so I could see which block was being called, it's not in the code usually.

@garethnewman

This comment has been minimized.

Copy link

commented Aug 13, 2019

Update: all the checks fail for instance that use this function: Get-AllInstanceInfo inside Instance.Assertions.ps1

@garethnewman

This comment has been minimized.

Copy link

commented Aug 13, 2019

all seems to be around here
function Get-AllInstanceInfo { # Using the unique tags gather the information required Param($Instance, $Tags, $There) # Using there so that if the instance is not contactable, no point carrying on with gathering more information switch ($tags) { 'ErrorLog' { if ($There) { try {

before switch ($tags) $There = $true after switch ($tags) $There = $false ???

@garethnewman garethnewman referenced a pull request that will close this issue Aug 13, 2019
@garyhampson

This comment has been minimized.

Copy link
Author

commented Aug 15, 2019

Hi @garethnewman - I think I've found the root cause of the issue. In the Instance.Assertions.ps1 file, this line needs to be changed:

Count = (Get-DbaDump -SqlInstance $psitem -WarningAction SilentlyContinue).Count
to

Count = (Get-DbaDump -SqlInstance $Instance -WarningAction SilentlyContinue).Count

Once I did that in my local, I didn't have this issue. The $There value was getting set to $false because the call to Get-DbaDump in the MemoryDump section was failing due to the wrong parameter being used in the call to that function.

@garethnewman

This comment has been minimized.

Copy link

commented Aug 15, 2019

Hey Gary, good work, seems like you found it quicker than I did! I need to learn how to debug powershell. I put Write-Host everywhere to find it! :) if you see above I raised a PR that will fix this where I come to the exact same conclusion, so we can't both be wrong! :)

@garyhampson

This comment has been minimized.

Copy link
Author

commented Aug 15, 2019

Hi @garethnewman - Have to get my eyes checked, because I didn't see that you had a pull request, so thanks for that! Can confirm that your proposed solution will fix this issue.

@tboggiano

This comment has been minimized.

Copy link
Collaborator

commented Aug 15, 2019

I've tried this fix and it seems to only work if default trace is enabled. Plus this line looks to be incorrect

$AllInstanceInfo.DefaultTrace.ConfiguredValue | Should -Be 1 -Because "We expect the Default Trace to be enabled but got $($AllInstanceInfo.DefaultTrace.Trace.ConfiguredValue)"

As there is no .Trace value for the last AllInstanceInfo reference. Even with that corrected with Default Trace disabled I still get an error.

DefaultTraceEnabled
DefaultTraceDisabled

I don't know what the answer is but when I write similar test for RemoteAccess and ScanForStartupProcdures I get the exact same thing.

@garethnewman

This comment has been minimized.

Copy link

commented Aug 15, 2019

Hey, I think my PR should fix this. Feel free to test it.

@tboggiano

This comment has been minimized.

Copy link
Collaborator

commented Aug 15, 2019

OK. I see based on other things failing it spitting out all that red is ok, I still question the one line of code if that $($AllInstanceInfo.DefaultTrace.Trace.ConfiguredValue) is correct, there is no .Trace value from what I can see it should be just $($AllInstanceInfo.DefaultTrace.ConfiguredValue) at the end.

@garethnewman

This comment has been minimized.

Copy link

commented Aug 15, 2019

Ahh, ok yeah that looks like another issue, I only tested with trace enabled to look at Gary's specific issue of instance checks vs defaulttrace check.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
3 participants
You can’t perform that action at this time.