Reporting on privileged Active Directory accounts with SQL Server

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

One of a very common requirements when it comes to assessing Active Directory security is reporting on the user accounts which are part of the high privilege groups: Enterprise Admins, Domain Admins, Administrators. This post will outline an approach of using SQL Server to accomplish this task.

Conceptually, the solution could be subdivided into 2 sections: ETL (Extract, Transform and Load) and Query Definition. Of course there is a third, equally important section, on Report definition, but I will not be covering it here, since there are plenty of other resources on this subject.

ETL

In order to extract data from Active Directory via SSIS we will leverage Active Directory Domain source component, which could be downloaded from here. Instructions on loading the component into your SSIS environment are here.

LDAP Query

Before we dive into the details of the SSIS job configuration I would like explain the structure of the LDAP query which we will be using in order to get the required data. As it turns out Active Directory already marks all such privileged object (user, group, computer) via an adminCount attribute.

Each object which currently is or ever was part of those 3 privileged groups, will be marked with the value of adminCount of 1. This applies to both groups and users, and also applies to the objects which acquired the membership in the privileged groups via nesting. For example, the “nested user”, in my example, is part of the NestedDomainAdminsLevel2 group. The group NestedDomainAdminsLevel2 is nested inside yet another group, which is in turn was placed into the Domain Admins group. The important point here is that, even despite the multiple levels of nesting, Active Directory was able to determine that the nested user is part of the Domain Admins group and marked it via adminCount = 1.

The only caveat with the adminCount attribute is that it is not reset if a user or a group is removed from the privileged groups. Hence the following LDAP query will return all users and groups which currently are or ever were part of the privileged groups.

(&(|(objectCategory=person)(objectCategory=group))(adminCount=1))

Certainly, we are interested only in the current state, so we will have to trim the output of the query to get the desired result, but this query is a good starting point.

SSIS Package

The SSIS package is fairly straightforward.

The ADSource component executes the LDAP query,

which we discussed in the previous section and flattens the result into tabular format for further processing. More information on the transformation from the AD hierarchical format into relational format by the ADSource component can be found here.

The Conditional Split transformation directs the Users, Groups and Members records into their respective tables.

If you are operating in a multi-domain environment you may want to setup a loop structure to go through all the domains in your forest. This can be accomplished by using a “For Each Container”. More information on looping through the domains can be found here.

Here you will find the SQL script to create the required tables and views along with the SSIS project.

After running the ADReportingDBGenerationScript.sql script open the domains table and add a row per domain, you would like to report on. Please, note that since group membership may be comprised from users residing in multiple domains, to get the full picture, you will have to enumerate through all of the domains in the forest.

After opening the ADRecursiveGroupMembershipExport SSIS project, the ADSource component will not validate since it will not be configured for your environment, this is by design. You will need to adjust the settings of the component in accordance with your environment.

Query Definition

The database creation script also created the required views which build the relationships between users, groups and members. If you are interested in the details of how the recursive query for nested group membership is implemented, take a look at the query definition for the vwRecursiveGroupMembership. Good background information on writing recursive queries in SQL Server can be found here.

The nested group membership is comprised of the membership acquired via direct membership and via membership in groups which in turn are members of the group in question. As you can imagine, this is a recursive problem, since the layering of groups could be infinite. Likely, with SQL Recursive Common Table Expressions it is fairly simple to build such a query.

The first block in the query (in green) defines the starting point of the query and returns the direct membership (users who were explicitly added to the groups). The the result of this initial query are recursively joined into another query which returns nested groups and their members which contain users returned in the initial query. Sorry, if this does not make sense. Recursion is kind of hard to explain. Bottom line, the view will contain the data we need.

The view vwRecursivePriviligedGroupMembership trims the output of the recursive query to only display the privileged groups: Administrators, Domain Admins, and Enterprise Admins. For a good measure, I also included some information on the users (whether smart card required for logon and whether an account could be delegated)