none
Trying to get a list of Active Directory group members

    Question

  • Hi,

    I'm trying to use Power Query to get a list of members from an Active Directory group. I can successfully get a list of members from some groups, by doing the following:

    let
        Source = ActiveDirectory.Domains("mydomain.priv"),
        mydomain.priv = Source{[Domain="mydomain.priv"]}[#"Object Categories"],
        group = mydomain.priv{[Category="group"]}[Objects],
        FilteredRows = Table.SelectRows(group, each ([distinguishedName] = "CN=mygroup,OU=Groups,OU=mydomain,DC=mydomain,DC=priv")),
        #"CN=mygroup,OU=Groups,OU=mydomain,DC=mydomain,DC=priv" = FilteredRows{[distinguishedName="CN=mygroup,OU=Groups,OU=mydomain,DC=mydomain,DC=priv"]}[group],
        member = #"CN=mygroup,OU=Groups,OU=mydomain,DC=mydomain,DC=priv"[member],
        TableFromList = Table.FromList(member, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Expand Column1" = Table.ExpandRecordColumn(TableFromList, "Column1", {"displayName"}, {"Column1.displayName"})
    in
        #"Expand Column1"

    However, when I try with a different group I get the error:

    "Formula.Firewall: Query is accessing data sources which can not be used together. Details: null"

    Does anyone have any ideas what might be causing this error?

    Wednesday, October 30, 2013 12:33 PM

Answers

  • Hi Kzhen,

    It's not possible to tell what might be wrong just by looking at the formulas. We're making some changes to the Firewall feature in our next update which may fix this issue.

    In the meantime, you might want to try enabling "Fast Combine" in the Power Query ribbon tab, which will turn off the firewall feature. Please only do this if you feel confident that the privacy of your data is not at risk. You can read more about Fast Combine in this page: http://office.microsoft.com/en-us/excel-help/privacy-levels-HA104009800.aspx

    Regards,
    M.

    • Marked as answer by kzhen Thursday, October 31, 2013 10:13 PM
    Thursday, October 31, 2013 3:35 PM

All replies

  • Hi Kzhen,

    It's not possible to tell what might be wrong just by looking at the formulas. We're making some changes to the Firewall feature in our next update which may fix this issue.

    In the meantime, you might want to try enabling "Fast Combine" in the Power Query ribbon tab, which will turn off the firewall feature. Please only do this if you feel confident that the privacy of your data is not at risk. You can read more about Fast Combine in this page: http://office.microsoft.com/en-us/excel-help/privacy-levels-HA104009800.aspx

    Regards,
    M.

    • Marked as answer by kzhen Thursday, October 31, 2013 10:13 PM
    Thursday, October 31, 2013 3:35 PM
  • Hi Kzhen,

    You're likely seeing this error because you've classified the Active Directory domains involved in your query under the Private or mixed privacy levels. Private data sources or data sources under different privacy level can only be combined with other data sources in very specific ways so that Power Query can guarantee that data is not transferred from one data source to another. At this time accessing multiple domains with mixed or Private privacy levels in the same query is not supported, without enabling "Fast Combine". We're aware that this can cause confusion and we're planning on improving this experience in the future.

    Usually classifying Active Directory domains under the "Organizational" privacy level offers sufficient security. Organizational data sources can only communicate with other Organizational data sources. You can fix this by doing the following:

    1. In the Power Query ribbon click on "Data Source Settings".
    2. A dialog will appear showing data sources for which you've already stored credential settings and configured privacy levels.
    3. For each of the Active Directory domains, make sure that they're classified under the "Organizational" privacy level using the dropdown menu on the right.

    Tristan

    Thursday, October 31, 2013 9:56 PM
  • Thank you for your reply, turning on Fast Combine made it work.

    It is still strange that for some groups it was able to get the list of members but for others it wasn't. I also changed the privacy level settings, but that didn't seem to make any difference.

    Thursday, October 31, 2013 10:15 PM