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
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