none
Powwershell - SQL varbinary output showing System.Object[] in CSV RRS feed

  • Question

  • Fellow Powershellers.

    I am runnin a sql command inwith Invoke_SQLcmd.  it dumps the users SID and name for the SQL login table.

    That sid, of course, is varbinary.  I have also sent the output from the Select by turning that value into a varchar(1000) field - SIDReadable. 

    CONVERT(VARCHAR(1000), [SID], 2) as SIDReadable

    The output in SQL looks fine.

    When I try to send SIDReadable out in Powershell, I get System.Object[].

    In powershell, I am loading the output from the Invoke_SQLCmd into an array. For example,

    $outputList=@()

    --- invoke sql cmd is loading $result

    -- then I load as PSObject

    $myoutputObject=New-ObjectPSObject-property@{

    Loginname =$result.Loginname

    SIDReadable = $result.SIDReadable

    }

    -- Now append the psobject to an array.

    $outputList+=$myoutputObject

    select output list to csv.

    $outputList  |Sort-Object-PropertyLoginname -Descending|Select-property  Loginname,  SIDReadable

     |Export-CSV-Path$FilePath  -NoTypeInformation

    I have tried conversions and expressions but cannot get this to show up as expected. What am I missing?

    Thanks MG

     

    Monday, February 25, 2019 6:46 PM

All replies

  • I think you'll have to use something like this:

    $SIDReadable = (New-Object System.Security.Principal.SecurityIdentifier($SIDarray,0)).value
    The $SIDarray variable is what holds that System.Object[], and I'm assuming that it's an array of bytes.

    Monday, February 25, 2019 7:28 PM
  • I think you'll have to use something like this:

    $SIDReadable = (New-Object System.Security.Principal.SecurityIdentifier($SIDarray,0)).value
    The $SIDarray variable is what holds that System.Object[], and I'm assuming that it's an array of bytes.



    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    • Proposed as answer by jrvModerator Monday, February 25, 2019 8:18 PM
    Monday, February 25, 2019 7:49 PM
  • You can not correctly convert a SID byte array toa character array and have it be readable.

    This would be more direct:

    $result | Select LoginName, @{n='SIDString';e={([Security.Principal.SecurityIdentifier]::New($_.SID,0)).Value}}


    \_(ツ)_/



    Monday, February 25, 2019 8:17 PM
    Moderator
  • What Rich is pointing to is that you can only convert a binary SID to a string SID using the Net Framework classes.  It cannot be done using SQL.  A SID is a binary structure and not a binary character array.

    My post above is an alternate method that does not create extra objects but converts the objects in the pipeline.  This is more efficient and faster but your method is ok if you are not retrieving a huge amount of data,


    \_(ツ)_/


    Monday, February 25, 2019 8:23 PM
    Moderator
  • Hi Jrv and Rich, First, thanks so much for the input. I do appreciate it. I tried your options but could not get them to output properly.

    Let me rephrase my issue. I am reading from SQLServer syslogins table. That shows the SID which I believe is a varbinary(85). I decided to just send back the SID formatted from SQL like I want. The command I used was

    select name, SID, Upper(sys.fn_varbintohexstr(SID)) as LoginSIDReadable

    from sys.syslogins order by name 

    Powershell was able to output the LoginSIDReadable field without any issues nor any other conversions. All I wanted is to show the EXACT sid value just like you see in SSMs. Yes, the name loginstdreadable is not the perfect name but it works;)

    Yes, I am aware that the function is undocumented and not supported. 

    But, now the powershell cannot handle the output from sys.fn_varbintohexstr(SID))  correctly. Everying for that is now coming back as we started System Object []

      So, could you check the commands you posted earlier?

    MG





    • Edited by mg101 Tuesday, February 26, 2019 2:35 AM correction
    Tuesday, February 26, 2019 1:28 AM
  • The examples work as advertised. They convert an array of bytes (each byte holding a single hexadecimal value) into a human-readable SID format (e.g., S-1-5-21-776720702-1. . . ).

    The reason you're seeing the "System.Object[]" in your CSV is that Powershell doesn't know how to represent that structure in your file. So, the goal is to turn that array into a string of some sort. That will either be the "S-1-5-..." style of SID, or a hex string (e.g. 0x0105 . . ., or 0105...).

    What we don't know is what's in the array of System.Objects that's being returned.

    This example starts out with an array of "Object" types (each of which is an Int32 value representing one byte of a SID), creates a byte array by casting the array elements to "byte", and produces a readable SID from the SecurityIdentifier. It then takes the SecurityIdentifier and "stringifies" the binary form of the SID back to the same values that were used to create the SecurityIdentifier.

    # Create an array of Object types and load it with a SID value
    [System.Object[]]$s = @(0x01,0x05,0x00,0x00,0x00,0x00,0x00,0x05,0x15,0x00,0x00,0x00,0x3E,0xD1,0x4B,0x2E,0x8F,0x12,0xB8,0x09,0xB7,0x46,0x9F,0xD3,0xED,0x03)
    # Create an array of Bytes and  load it by casting each "Object" to a typw of "Byte"
    $b = [byte[]]::new(($s.Count)+2)
    0..($s.Count - 1)|foreach {$b[$_] = [byte]$s[$_]}
    # create a Security Identifier from the byte array
    $SID = [System.Security.Principal.SecurityIdentifier]::New($b,0)
    $SIDReadable = $SID.value                        # this is the "human-readable" SID format
    $SIDBinaryForm = [byte[]]::new(($s.count)+2)     # create a byte array to hold the SID
    $Sid.GetBinaryForm($SIDBinaryForm,0)             # load the array with the bytes of the SID
    # 
    $SIDBinaryForm | foreach {$y += "0x" + ('{0:X2}' -f $_) + ","}
    $y = $y.TrimEnd(",")
    What you need to do is to figure out what's in the $result.SIDReadable. Then you can manipulate it into the string type you expected.


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)


    Wednesday, February 27, 2019 2:44 AM