Issues in getting right value of SID

Question

I am trying to implement active geo-replication using Azuresql (https://learn.microsoft.com/en-us/azure/azure-sql/database/active-geo-replication-configure-portal?view=azuresql&tabs=portal)

In order to setup the cross-subscription geo-replication there are few t-sql commands (https://learn.microsoft.com/en-us/azure/azure-sql/database/active-geo-replication-configure-portal?view=azuresql&tabs=portal#cross-subscription-geo-replication) that needs to be run. Due to certain security concerns I am converting the t-sql commands to equivalent poweshell script and trying to execute through the Azure DevOps pipeline.

On running the below script I am getting sid value which is not hexadecimal. It returns a data with datatype System.Array. I again tried to convert the data to hexadecimal value but it does seem to match.

try {

$server = "YourServerName"
$database = "master"
$username = "testadmin"
$password = "testpassword"
$query1 = "create login geodrsetup with password = 'ComplexPassword01';"
$query2 = "create user geodrsetup for login geodrsetup;alter role dbmanager add member geodrsetup;"
$query3 = "select sid from sys.sql_logins where name = 'geodrsetup';"

Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $quer1 -Username $username -Password $password -Verbose

Write-Host "Login Added Successfully"
 
Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $query2 -Username $username -Password $password -Verbose

Write-Host "User Added Successfully"
 
$result=Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $query3 -Username $username -Password $password -Verbose -AS DATATABLES

Write-Host "Get SID vaule"
 
$test= $result.sid
 
Write-Host $test -ForegroundColor Green

foreach ($item in $test) {
    # Convert the item to hexadecimal and concatenate it to the existing string
    $hexString += "{0:X}" -f $item
}
 
# Output the concatenated hexadecimal string
Write-Output $hexString

}
catch {
    $_.Exception | Format-List -Force
}

Can anyone please help me here by providing their guidance

Answer 1

To transfer your byte array to a hex string:
_{(this question is in fact a duplicate with: Powershell byte array to hex)}

# $Bytes = [Bytes[]]$Result.Sid
$Bytes = [Bytes[]]@(1, 6, 0, 0, 0, 0, 0, 100, 0, 0, 0, 0, 0, 0, 0, 0, 179, 169, 17, 121, 155, 67, 192, 64, 173, 105, 210, 183, 75, 154, 133, 136)
'0x' + (($Bytes|ForEach-Object ToString X2) -join '')

0x01060000000000640000000000000000B3A911799B43C040AD69D2B74B9A8588

But in case you actually looking for a real security identifier (Sid) representation:

[System.Security.Principal.SecurityIdentifier]::new($Bytes, 0).Value

S-1-100-0-0-2031200691-1086342043-3084020141-2290457163