none
Unable to get user SID (objectSid) from ActiveDIrectory using PowerQuery RRS feed

  • Question

  • Hi,

    I'm trying to get objectSid value form ActiveDirectory (Windows 2003 functional level) using Power Query for Excel Version: 1.5.3296.2082

    1.

    = #"Expand securityPrincipal"{[distinguishedName="CN=xx,CN=xx,DC=xx,DC=xx"]}[securityPrincipal.objectSid]

    2.

    = Table.ExpandRecordColumn(user, "securityPrincipal", {"objectSid"}, {"securityPrincipal.objectSid"})

    3.

    = Binary.Combine(#"Expand securityPrincipal"[securityPrincipal.objectSid])

    Expression.Error: Cannot convert the value null to type Binary. Details: Value=null, Type=Type

    What I need to do to ectract user SID value in form of S-1-5-21-1180699209-877415012-3182924384-1004?

    If I import example above to Excel table I get just value '[Binary]'...

    Thank you for the answers :) In advance

    Friday, November 29, 2013 8:23 PM

Answers

  • This requires a little programming. Fortunately, we have the M programming language to fall back on. Here, I've defined a function ConvertSidToStringSid to perform this operation, and then used it to do the work.

    let
        ConvertSidToStringSid = (sid) => let
            parts = BinaryFormat.Record([
                version = BinaryFormat.Byte,
                count = BinaryFormat.Byte,
                first = BinaryFormat.List(BinaryFormat.Byte, 6),
                rest = BinaryFormat.List(BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian))])(sid),
            result = "S-" & Number.ToText(parts[version]) & "-" & Number.ToText(BinaryFormat.UnsignedInteger64(#binary({0, 0} & parts[first]))) & List.Accumulate(parts[rest], "", (a, b) => a & "-" & Number.ToText(b))
        in
            result,
        Source = ActiveDirectory.Domains("redmond.corp.microsoft.com"),
        redmond.corp.microsoft.com = Source{[Domain="redmond.corp.microsoft.com"]}[#"Object Categories"],
        user1 = redmond.corp.microsoft.com{[Category="user"]}[Objects],
        FilteredRows = Table.SelectRows(user1, each [displayName] = "Curt Hagenlocher"),
        #"CN=Curt Hagenlocher,OU=UserAccounts,DC=redmond,DC=corp,DC=microsoft,DC=com" = FilteredRows{[distinguishedName="CN=Curt Hagenlocher,OU=UserAccounts,DC=redmond,DC=corp,DC=microsoft,DC=com"]}[securityPrincipal],
        objectSid = #"CN=Curt Hagenlocher,OU=UserAccounts,DC=redmond,DC=corp,DC=microsoft,DC=com"[objectSid],
        stringSid = ConvertSidToStringSid(objectSid)
    in
        stringSid

    The same function could just as easily be used when adding a custom column e.g.

    = Table.AddColumn(KeptFirstRows, "Sid", each ConvertSidToStringSid([securityPrincipal][objectSid]))

    Sunday, December 1, 2013 2:10 PM