Get-DbaRegServer error if -SQLCredential is a Windows Account

chuckh1958 commented 2 years ago

Verified issue does not already exist?

Yes

What error did you receive?

InvalidOperation: C:\Program Files\PowerShell\Modules\dbatools\1.1.89\allcommands.ps1:56134:17 Line | 56134 | … $servers += ($serverstore.DatabaseEngineServerGroup.GetDe … | ~~~~~~~~~~~~~ | You cannot call a method on a null-valued expression.

Steps to Reproduce

# provide your command(s) executed pertaining to dbatools
# please include variable values (redacted or fake if needed) for reference

$me=Get-Credential # Enter a valid windows login and password when prompted Get-DbaRegServer -SqlInstance -SqlCredential $me

Are you running the latest release?

No response

Other details or mentions

If I change the -SQLCredentential to a SQL Server authenticated login it works.

Other DBATools commands seem to work fine when SQLCredential is set to a Windows credential

What PowerShell host was used when producing this error

PowerShell Core (pwsh.exe), VS Code (terminal)

PowerShell Host Version

Name Value

PSVersion 7.2.3 PSEdition Core GitCommitId 7.2.3 OS Microsoft Windows 10.0.19044 Platform Win32NT PSCompatibleVersions {1.0, 2.0, 3.0, 4.0…} PSRemotingProtocolVersion 2.3 SerializationVersion 1.1.0.1 WSManStackVersion 3.0

SQL Server Edition and Build number

Microsoft SQL Server 2019 (RTM-CU11) (KB5003249) - 15.0.4138.2 (X64) May 27 2021 17:34:14 Copyright (C) 2019 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Datacenter 10.0 (Build 17763: )

.NET Framework Version

PSChildName Version

v2.0.50727 2.0.50727.4927 v3.0 3.0.30729.4926 Windows Communication Foundation 3.0.4506.4926 Windows Presentation Foundation 3.0.6920.4902 v3.5 3.5.30729.4926 Client 4.8.04084 Full 4.8.04084 Client 4.0.0.0

chuckh1958 commented 2 years ago

Get-Module DBATools | select Version

Version

1.1.89

andreasjordan commented 2 years ago

The command uses the following code:

$instance = 'XXX'
$me = Get-Credential
$serverstore = Get-DbaRegServerStore -SqlInstance $instance -SqlCredential $me
$serverstore.DatabaseEngineServerGroup.GetDescendantRegisteredServers()

The error is raised in the last line, so $serverstore.DatabaseEngineServerGroup seems to be $null. Can you run the code and see if this raises the same error and then have a look at $serverstore.DatabaseEngineServerGroup and $serverstore.

I hope that helps us find the root cause.

chuckh1958 commented 2 years ago

I got the error on the last line.

PS >$instance="IT-DB-P-04" PS > $me=Get-Credential

PowerShell credential request Enter your credentials. User: mydomain\myuser Password for user mydomain\myuser: **

PS > $serverstore = Get-DbaRegServerStore -SqlInstance $instance -SqlCredential $me PS > $serverstore.DatabaseEngineServerGroup.GetDescendantRegisteredServers() InvalidOperation: You cannot call a method on a null-valued expression.

The null value is $serverstore.DatabaseEngineServerGroup …

PS > $serverstore

ComputerName : IT-DB-P-04 InstanceName : MSSQLSERVER SqlInstance : IT-DB-P-04 AnalysisServicesServerGroup : AnalysisServicesServerGroupName : AnalysisServicesServerGroup AzureDataStudioConnectionStore : CentralManagementServerGroup : CentralManagementServerGroupName : CentralManagementServerGroup DatabaseEngineServerGroup : DatabaseEngineServerGroupName : DatabaseEngineServerGroup DisplayName : Central Management Servers IntegrationServicesServerGroup : IntegrationServicesServerGroupName : IntegrationServicesServerGroup IsLocal : False KeyChain : RegisteredServersStore ReportingServicesServerGroup : ReportingServicesServerGroupName : ReportingServicesServerGroup ServerGroups : {} SqlServerCompactEditionServerGroup : SqlServerCompactEditionServerGroupName : SqlServerCompactEditionServerGroup

andreasjordan commented 2 years ago

To get the information, Get-DbaRegServerStore does this:

$instance = 'XXX'
$me = Get-Credential
$server = Connect-DbaInstance -SqlInstance $instance -SqlCredential $me
$store = New-Object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore($server.ConnectionContext.SqlConnectionObject)

Maybe there is an issue when using windows authentication.

Can you run the powershell session under the windows user you would like to connect with? Then you don't need the -SqlCredential parameter.

andreasjordan commented 2 years ago

Maybe I found a solution.

Can you please run this and see if it works?

$instance = 'XXX'
$me = Get-Credential
$server = Connect-DbaInstance -SqlInstance $instance -SqlCredential $me
$serverstore = New-Object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore($server.ConnectionContext)
$serverstore.DatabaseEngineServerGroup.GetDescendantRegisteredServers()

andreasjordan commented 2 years ago

Difference is that we change $server.ConnectionContext.SqlConnectionObject to $server.ConnectionContext.

chuckh1958 commented 2 years ago

That works!

andreasjordan commented 2 years ago

Ok, I'll open a PR for that.

chuckh1958 commented 2 years ago

Thank you. Hopefully update-module will be fixed by then too. I tried to run it for dbatools but get an error that it can’t find the NuGet package provider even though I can see that version 2.8.5.208 is installed.

From: Andreas Jordan @.> Sent: Tuesday, May 10, 2022 9:55 AM To: dataplat/dbatools @.> Cc: Hamilton, Chuck @.>; Author @.> Subject: Re: [dataplat/dbatools] Get-DbaRegServer error if -SQLCredential is a Windows Account (Issue #8338)

Ok, I'll open a PR for that.

— Reply to this email directly, view it on GitHubhttps://github.com/dataplat/dbatools/issues/8338#issuecomment-1122430344, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AT6JVUHD7KRPSWYTH7IFR63VJJTCHANCNFSM5VE7DWTQ. You are receiving this because you authored the thread.Message ID: @.**@.>>

dataplat / dbatools