Detecting cross-nested groups in Active Directory with SSIS and SQL
This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at https://www.microsoft.com/info/cpyright.htm
Cross-nested groups, in my definition, constitute a scenario where GroupA is nested into GroupB, and GroupB is nested into GroupA (GroupA <-> GroupB).
Why would you consider detecting this condition:
- Since Active Directory group structure is intended to be hierarchical, cross-nesting is typically an oversight of an administrator, and should be corrected
- Cross-nested groups should, in most cases, be converted into a single group or nested into a mutual parent (of course removing the cross-nesting at the same time).
Overall, elimination and/or reduction of cross-nesting may help in the following areas:
- simplification of group management
- dealing with token bloat
- reduce attack surface
Reporting environment setup
The instructions for setting up the environment will be very similar to what I described in my previous post Reporting on privileged Active Directory accounts with SQL Server. The SSIS package and the SQL schema are essentially the same for both scenarios. I suggest you follow that walkthrough first.
LDAP Query
Below is the LDAP query which SSIS Active Directory Source component will use to get the data we need.
(|(objectCategory=Person)(objectCategory=group))
Technically speaking, we don’t need the user objects for this specific query, but since we may also want to run some other reports, I included user objects as well.
SQL Query
The logic behind the query in vwCrossNestedSecurityGroups view is to self-join the Members table based on the reversed combination of groupDN and memberDN. For example, record groupA –> groupB will try to find a record groupB->groupA (crossing).
To simplify the query I created an underlying view, which filters-out user members from the Members table (vwDirectGroupMembers), hence focusing only on the group nesting.
Notice that in the query I filter-out distribution lists (groupType < 0). Security groups the groupType of –214748364X (X varies based on the scope of the group). If you are interested in distribution lists as well, simply remove the WHERE clause.
Note on hardware
When I ran this query against the Members table with 9 millions rows, the memory utilization on my lab machine went from 2G to 9.5G, which I guess was to be expected.
Just something to keep in mind if you are working with a large data set.