[Bug] Microsoft.SqlServer.Management.Smo.Server object may return null computername

[yoke88]

Environmental information

LCID             Name             DisplayName
----             ----             -----------
2052             zh-CN            中文(简体，中国)

SQL Server:

Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64)   Mar 18 2018 09:11:49   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor) 

简体中文

Host used

• powershell.exe
• ISE
• VS Code
• Other (please specify)

If anything other than powershell.exe was used, please confirm that you can duplicate the issue with powershell.exe

• Still buggy in powershell.exe

Errors Received

警告: [10:23:35][Get-DecryptedObject] Can't access registry keys on mydb. Do you have administrative access to the
Windows registry on [mydb] Otherwise, we're out of ideas.

Steps to Reproduce

$dbatools=Import-Module dbatools -PassThru
& $dbatools{ Get-DecryptedObject -SqlInstance mydb -Type LinkedServer -Verbose}

Expected Behavior

i can get decrypted linkedserver list with password,but it always report an error like the above,so i add some debug in allcommands.ps1, and i found Microsoft.SqlServer.Management.Smo.Server object may return null computername. so the internal function like resolve-netbiosname、resolve-sqlipaddress should handle such situation.

Actual Behavior

详细信息: [10:26:17][Get-DecryptedObject] Querying service master key
详细信息: [mydb]
详细信息: myDB
详细信息: Microsoft.SqlServer.Management.Smo.Server
详细信息:
ComputerName Name Product              Version   HostPlatform IsAzure IsClustered ConnectedAs
------------ ---- -------              -------   ------------ ------- ----------- -----------
             mydb Microsoft SQL Server 13.0.4422 Windows              False


Write-Verbose : 无法将参数绑定到参数“Message”，因为该参数是空值。
所在位置 C:\Windows\system32\WindowsPowerShell\v1.0\Modules\dbatools\allcommands.ps1:80651 字符: 19
+     write-verbose $server.ComputerName
+                   ~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Write-Verbose]，ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.WriteVerbos
   eCommand

Write-Verbose : 无法将参数绑定到参数“Message”，因为该参数是空值。
所在位置 C:\Windows\system32\WindowsPowerShell\v1.0\Modules\dbatools\allcommands.ps1:76456 字符: 19
+     write-verbose $sourceNetBios
+                   ~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Write-Verbose]，ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.WriteVerbos
   eCommand

详细信息: [10:26:17][Get-DecryptedObject] Get entropy from the registry - hopefully finds the right SQL server instance
Get-DecryptedObject : 无法对参数“ComputerName”执行参数验证。参数为 Null 或空。请提供一个不为 Null 或空的参数，然后重
试该命令。
所在位置 行:1 字符: 14
+ & $dbatools{ Get-DecryptedObject -SqlInstance mydb -Type LinkedServer -Verbose}
+              ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException
    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Get-DecryptedObject

警告: [10:26:17][Get-DecryptedObject] Can't access registry keys on mydb. Do you have administrative access to the
Windows registry on [mydb] Otherwise, we're out of ideas.

[wsmelton]

@yoke88 Microsoft.SqlServer.Management.Smo.Server will always return a null ComputerName property because it is not a property of SMO.

We are adding that property to our output in the commands.

[wsmelton]

Can't access registry keys on mydb. Do you have administrative access to the Windows registry on [mydb] Otherwise, we're out of ideas

This error is true to what it states, if you do not have remote access to the registry with the Windows Account you are using then that error is expected to be returned.

[yoke88]

the error is not the real error , invoke-command2 will pass a null computername if Smo.Server return a null ComputerName property, and will always fail

[yoke88]

i suggest to fill the computername property base on other properties instead of a null computername.

[wsmelton]

That won't be acceptable because the Name property is not dependable and can return a false value. The ComputerName is not coming from SMO it is a property we are adding to the SMO object itself. You can review what Connect-DbaInstance is doing to see this being done. The internal command Connect-SqlInstance is just a wrapper for that command now.

[wsmelton]

Please show your debugging results as to why the ComputerName was returning a null value.

[wsmelton]

The primary concern I have is one of the commands you are talking about is heavily used in the module by many commands. So it will not be approved solely on the error you received as I've seen that error multiple times, and in every situation is was because remote access to the registry was blocked for some reason.

[yoke88]

PS C:\> [Microsoft.SqlServer.Management.Smo.Server]"mydb"|fl *name*


ClusterName                 :
ComputerNamePhysicalNetBIOS : MYDB
FilestreamShareName         : MSSQLSERVER
InstanceName                :
NamedPipesEnabled           : False
NetName                     : MYDB
ServiceName                 : MSSQLSERVER
SqlCharSetName              : cp936
SqlSortOrderName            : bin_ascii_8
Name                        : mydb
DomainName                  : SMO
DomainInstanceName          : MYDB

## and the Resolve-NetBiosName will return null if it use computername property
function Resolve-NetBiosName {
    
    [CmdletBinding()]
    param (
        [Parameter(Mandatory)]
        [object]$SqlInstance,
        [PSCredential]$SqlCredential
    )
    $server = Connect-SqlInstance -SqlInstance $SqlInstance -SqlCredential $SqlCredential
    $server.ComputerName
}

[wsmelton]

The computer name will always have a value returned because it is part of our custom type we have in our own library. Please review our code and focus on what Connect-SqlInstance and Connect-DbaInstance is doing. This command is the center of our module and the SMO object we output has the ComputerName property because we are adding it to, it gets the value from our type DbaInstanceParameter:

[3] > [Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter]"MyDb"

ComputerName       : MyDb
InstanceName       : MSSQLSERVER
Port               : 1433
NetworkProtocol    : Any
IsLocalHost        : False
FullName           : MyDb
FullSmoName        : MyDb
SqlComputerName    : [MyDb]
SqlInstanceName    : [MSSQLSERVER]
SqlFullName        : [MyDb]
IsConnectionString : False
Type               : Default
LinkedLive         : False
LinkedServer       :
InputObject        : MyDb

If you pass in a named instance:

[4] > [Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter]"MyDb\MyInstance"

ComputerName       : MyDb
InstanceName       : MyInstance
Port               : 0
NetworkProtocol    : Any
IsLocalHost        : False
FullName           : MyDb\MyInstance
FullSmoName        : MyDb\MyInstance
SqlComputerName    : [MyDb]
SqlInstanceName    : [MyInstance]
SqlFullName        : [MyDb\MyInstance]
IsConnectionString : False
Type               : Default
LinkedLive         : False
LinkedServer       :
InputObject        : MyDb\MyInstance

[yoke88]

get-decrypedObject use [Microsoft.SqlServer.Management.Smo.Server]

function Get-DecryptedObject {
    
    param (
        [Parameter(Mandatory)]
        [Microsoft.SqlServer.Management.Smo.Server]$SqlInstance,
        [Parameter(Mandatory)]
        [ValidateSet("LinkedServer", "Credential")]
        [string]$Type,
        [switch]$EnableException
    )

    $server = $SqlInstance
    $sourceName = $server.Name

    # Query Service Master Key from the database - remove padding from the key
    # key_id 102 eq service master key, thumbprint 3 means encrypted with machinekey
    Write-Message -Level Verbose -Message "Querying service master key"
    $sql = "SELECT substring(crypt_property,9,len(crypt_property)-8) as smk FROM sys.key_encryptions WHERE key_id=102 and (thumbprint=0x03 or thumbprint=0x0300000001)"
    try {
        $smkbytes = $server.Query($sql).smk
    } catch {
        Stop-Function -Message "Can't execute query on $sourcename" -Target $server -ErrorRecord $_
        return
    }
   ### custom debug code
    write-verbose $server
    write-verbose $env:computername
   ### here $sourceNetBios will be null because resolve-netbiosname use computerName Property
    $sourceNetBios = Resolve-NetBiosName $server
    write-verbose $sourceNetBios
    $instance = $server.InstanceName
    $serviceInstanceId = $server.ServiceInstanceId

    Write-Message -Level Verbose -Message "Get entropy from the registry - hopefully finds the right SQL server instance"

    try {
        ### Here Invoke-command2 will pass a null computername and do nothing
        ### The real error will be hide because the error handle return a custom error message and always explains no enough permission 
        [byte[]]$entropy = Invoke-Command2 -Raw -Credential $Credential -ComputerName $sourceNetBios -argumentlist $serviceInstanceId {
            $serviceInstanceId = $args[0]
            $entropy = (Get-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$serviceInstanceId\Security\" -ErrorAction Stop).Entropy
            return $entropy
        }
    } catch {