Offline SQL Querying of Active Directory

Successful internal penetration tests, simulated attacks and red team exercises are usually predicated by a detailed understanding of the target network. As the majority of large corporations use Windows, this equates to an understanding of the structure of Active Directory. This could include high value users based on their group membership, specific hosts based on usage or operating system or useful information stored within AD itself. There are a number of very good tools and techniques that can extract information from Active Directory which include the various powershell scripts that make up PowerSploit, nishang and some of the post-exploitation modules offered by Metasploit. However, these all require a direct and continued connection to the target domain which could increase the likelihood of discovery and can suffer from a speed penalty, particularly if the connection to the target's network is not direct or latency free. I previously wrote a tool called ADEGrab, which facilitated the extraction of search results from Sysinternals' AD Explorer; the benefit of this tool being that it allowed offline replication of an active directory domain. Although AD Explorer was a huge improvement in terms of offline reconnaissance on a simulated attack, it still lacks some of the flexibility of traditional SQL databases. This post relates to a solution to this problem which can, if normal user credentials and a TCP connection to port 389 on a domain controller is available, copy key parts of Active Directory to an offline SQLite database, effectively allowing normal SQL queries to be executed against it. It allows enumeration of users, computers (including hostname, OS etc.) and groups, parses some LDAP attributes (e.g. sAMAccountType and userAccountControl) to enable you to intuitively search for accounts that, for example, have been locked out or are disabled and calculates nested group membership offline. This tool is an alternative to (and improves on) my metasploit post exploitation module that, at the time of writing, exists as an open pull request; it uses ADSI queries against the domain controller to calculate nested group membership.

The tool is available at https://github.com/stufus/ADOffline; alternatively the repository can be cloned from Github.

It assumes that you have low privilege (e.g. standard user), access to a domain and that you are able to connect to TCP/389 (LDAP) on a domain controller. On an internal penetration test, you can access a domain controller directly; but, on a simulated attack or red team engagement, use a port forward or SOCKS proxy or equivalent. The basic stages are:

1. Use ldapsearch to download the Active Directory structure. At the current time of writing, this script only parses the 'user' object class; this will have the effect of parsing all users, groups and computers on the domain. Ldapsearch will generate an LDIF file which is a text file containing a representation of the AD structure.
2. Import this into a SQLite database using adoffline.py.
3. Query the SQLite database using a command line tool or a front end.

A user, group and computer is, in essence, the same thing as far as LDAP is concerned. There are some attributes that do not make sense if you are not a computer (e.g. operatingSystem) but at a high level, each of these records is considered as a user at heart. However, there are a number of SQL views that can be used to offer easy identification of computers, groups and users in a more intuitive manner.

The table below shows the LDAP attributes that ADOffline currently identifies and parses. The actual fields in the database are discussed later; this is because some of the attributes below are parsed and interpreted in order to make their meaning clearer.

The database is created automatically and, internally, comprises two tables and a number of views.

The two tables are created using the code below:

The first table (raw_users) holds the basic information retrieved from LDAP as discussed in the table above. The second table stores any DNs referenced by a member or memberOf attribute. For example, if UserA and UserB are members of GroupX, raw_memberof will contain:

(In reality, it will contain DNs rather than usernames, but this illustrates the point). The idea is that the raw_memberof table can be joined with the raw_users table to be able to determine who is a member of what.

In order to make this easier to interact with, a number of views; a view is essentially a table which is generated at runtime from a SQL query.

This probably looks quite confusing and inefficient, and both are true. However, this design also offers a lot of flexibility; it enables almost any sensible search to be performed offline which is of particular use during simulated attacks. I would encourage you to take the time to get used to this. I have provided a description of each of the fields available in the database below.

Show active members of the Domain Admins group:

Show the effective groups that 'Stufus' is a member of:

Write a list of computer hostnames and operating system information (sorted by OS) to a CSV file:

Display a list of users who have 'pass' somewhere in their description or info fields:

Display a list of users who have something in their description, comment, title or info fields:

Display a list of computers which have something in their description or info fields:

Display the number of computers running each of the operating systems used in the target's estate:

Display all of the Windows XP or Windows 2000 hosts, along with their description:

Generate a CSV file suitable for importing into Visio to generate an organisational chart (equivalent to make_csv_orgchart):

The easiest way to acquire the LDAP schema is using the 'ldapsearch' command line tool which is part of OpenLDAP. For example:

When the LDAP file has been acquired, its filename is provided as the only parameter to ADOffline.

The main reason why the database is so large is because of the indexes and the replication of data. This database is not designed to be optimised or efficient at the expense of intuitive use, mainly because disk space is cheap.

From the command line, the SQLite3 client can be used to query the data. $ sqlite3 /tmp/tmpBQXkFH.20160131233152.ad-ldap.db However, for those who like their GUIs, DB Browser for SQLite works very well. It is an open source, cross platform SQLite query browser.