Convert binary SID to AD SID

Question

Hi,

in SQL database I found user`s SID in format like: 0x0105000000000005150000003043AF01871D75DDDFCC8B5254040000
I know this is domain SID, but in binary format.

How can I convert this SID in powershell?

for example 0x0105000000000005150000003043AF01871D75DDDFCC8B5254040000 = S-1-5-21-28263216-3715440007-1384893663-1108

I can convert it using SQL code:

DECLARE @varBinarySID VARBINARY(85)  
-- assign the value in below variable  
SELECT @varBinarySID = 0x0105000000000005150000003043AF01871D75DDDFCC8B5254040000  
DECLARE @StringSID VARCHAR(100)  
DECLARE @len AS INT  
SET @len = LEN(@varBinarySID)  
DECLARE @loop AS INT  
SELECT @StringSID = 'S-'  
SELECT @StringSID = @StringSID + CONVERT(VARCHAR, CONVERT(INT, CONVERT(VARBINARY, SUBSTRING(@varBinarySID, 1, 1))))  
SELECT @StringSID = @StringSID + '-'  
SELECT @StringSID = @StringSID + CONVERT(VARCHAR, CONVERT(INT, CONVERT(VARBINARY, SUBSTRING(@varBinarySID, 3, 6))))  
SET @loop = 9  
WHILE @loop < @len  
BEGIN  
    DECLARE @temp_var BINARY (4)  
    SELECT @temp_var = SUBSTRING(@varBinarySID, @loop, 4)  
    SELECT @StringSID = @StringSID + '-' + CONVERT(VARCHAR, CONVERT(BIGINT, CONVERT(VARBINARY, REVERSE(CONVERT(VARBINARY, @temp_var)))))  
    SET @loop = @loop + 4  
END  
SELECT @StringSID 'String SID'  


  

Answer 1

$Users = Invoke-Sqlcmd 'SELECT * FROM [RDS02].[rds].[User]' -ServerInstance "SRV01\SQLEXPRESS" | select id, SID, @{'n'='AD_SID';'e'={(New-Object System.Security.Principal.SecurityIdentifier($_.SID,0)).Value}}  

I found how to do this

{(New-Object System.Security.Principal.SecurityIdentifier($_.SID,0)).Value}