Most penetration testers, or those engaged in simulated attacks or red team activities, will understand the value of reconnisance. On large corporate networks, this will involve a fair amount of Active Directory querying. There are some exceptionally powerful tools built into Empire, PowerTools and Metasploit, but these all rely on an active connection to a domain controller.

This tool combines the flexibility of SQL with the raw detail in Active Directory by parsing the raw LDAP output from Active Directory and inserting it into a SQLite database. This can then be used to explore user and group membership and download all computer information.

There is a huge amount of information stored in LDAP; this tool does not seek to recover it all, but instead should help with planning attacks or identifying high value target users without needing to constantly query AD.

It is work in progress; all commits/PRs/support welcome.

AT THE MOMENT, THIS IS BETA CODE. It has not been fully tested but has been used successfully on various targeted attacks and engagements.

• Allows enumeration of users, computers (including hostname, OS etc) and groups; these are all derived from users.
• Parses flags (e.g. sAMAccountType, userAccountControl) for intuitive searching.
• Easy to enumerate basic and nested group membership.

• Will take a while to parse a large LDIF file. During testing, it took 30 minutes to parse a large domain containing roughly 100,000 users, groups and computers.
• Reqires a large data exchange with the domain controller which may be noticed. For example, the domain above generated a 400Mb LDAP file, although this was generated in approximately 4 minutes.
• Does not currently consider foreign groups (i.e. from trusts).

This 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, use a port forward or SOCKS proxy or equivalent.

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

The current version is not complete; there are several efficiency improvements to be made and features to be added. It currently:

• Stores users, groups and computers
• Calculates nested groups for users only

This was tested on a real client domain with approximately 100,000 individual users, groups and computers. It took approximately 30 minutes to parse the 7 million lines in the original LDIF file to generate the database and another half an hour to work out the nested groups on a laptop (single threaded).

$ ldapsearch -h <ip> -x -D <username> -w <password> -b <base DN> -E pr=1000/noprompt -o ldif-wrap=no > client.ldif
...

$ ls -lah client.ldif
-rw-r--r--  1 stuart  users   391M Jan 21 08:18 client.ldif

$ python adoffline.py client.ldif
AD LDAP to SQLite Offline Parser
Stuart Morgan (@ukstufus) <[email protected]>

[31/Jan/16 23:31:52] Creating database: /tmp/tmpBQXkFH.20160131233152.ad-ldap.db
[31/Jan/16 23:31:52] Reading LDIF...done
[31/Jan/16 23:31:53] Parsing LDIF...
  Reading line 7001808/7001808 (100%)
[31/Jan/16 23:59:24] Calculating chain of ancestry (nested groups)...
  Processed user 27385/27385 (100%)
[01/Feb/16 00:27:35] Completed

        Users: 27385
       Groups: 38334
    Computers: 29142
 Associations: 2102885
 
$ ls -lah /tmp/tmpBQXkFH.20160131233152.ad-ldap.db
-rw-r--r--  1 stuart  wheel   1.0G Feb  1 00:27 /tmp/tmpBQXkFH.20160131233152.ad-ldap.db

The database size for the above example was 1GB which is perfectly manageable.

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. Note that in some cases, the description is based on the believed general usage of the attribute; there may be circumstances where an organisation uses this field for a different reason.

The actual fields in the database are covered later; this is because the fields below are sometimes parsed and interpreted in order to make their meaning clearer.

Internally, the database has two tables which are created automatically using the statements below.

CREATE TABLE raw_users ('objectClass','dn','title', 'cn','sn','description','instanceType','displayName','name','dNSHostName','userAccountControl','badPwdCount','primaryGroupID','adminCount','objectSid','sid','rid','sAMAccountName','sAMAccountType', 'objectCategory','operatingSystem','operatingSystemServicePack','operatingSystemVersion','managedBy','givenName','info','department','company','homeDirectory','userPrincipalName','manager','mail','groupType');

CREATE TABLE raw_memberof ('dn_group' TEXT NOT NULL,'dn_member' TEXT NOT NULL, PRIMARY KEY('dn_group','dn_member'));

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, designing it this way does make it very powerful; it enables almost any sensible search to be performed offline which is of particular use during simulated attacks because you can identify high value accounts completely offline, and get a good idea of the internals of the target domain without running repeated queries.

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 and, below that, some examples of common queries.

The fields available from the views are shown below.

Show active members of the Domain Admins group:

sqlite> select member_cn from view_activegroupusers where group_cn = "Domain Admins";

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

sqlite> select group_cn from view_activegroupusers where member_cn = "Stufus";

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

sqlite> .mode csv
sqlite> .once hosts.csv
sqlite> select cn,dNSHostName,operatingSystem,operatingSystemVersion,operatingSystemServicePack from view_computers order by operatingSystem,operatingSystemVersion,operatingSystemServicePack;

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

sqlite> select cn,description,info FROM view_users WHERE (description LIKE '%pass%' OR info LIKE '%pass%');

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

sqlite> select cn,description,info FROM view_users WHERE (description IS NOT NULL or info IS NOT NULL);

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

sqlite> select cn,description,info FROM view_computers WHERE (description IS NOT NULL or info IS NOT NULL);

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

sqlite> select count(dn),operatingSystem FROM view_computers where ADS_UF_ACCOUNTDISABLE=0 and ADS_UF_LOCKOUT=0 GROUP BY operatingSystem;

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

sqlite> select dnsHostName,description,info,operatingSystem from view_computers where operatingSystem LIKE '%Windows%2000%' OR operatingSystem LIKE '%Windows%XP%';

https://labs.mwrinfosecurity.com/blog/offline-querying-of-active-directory/