Roadrecon SQL Queries

This post discusses an alternative way of analyzing data collected with Roadtools’ Roadrecon module and provides example queries that may assist in further analysis. Roadtools (short for Rogue Office 365 and Azure (active) Directory tools) is a famous framework to interact with Microsoft Entra ID. The framework was introduced in [1] and since then many cybersecurity professionals use it for various purposes. Roadrecon [2] is a tool from the framework that collects information from a provided Entra ID tenant leveraging the Azure Active Directory Graph API (graph.windows.net).

The Roadrecon output is stored by default in a database file named roadrecon.db on disk. The database can then be presented in a nice graphical interface on a browser using the command roadrecon gui. However, opening the database using a tool such as SQLiteBrowser [3] can further increase the efficiency of the analysis and allow an operator to perform custom and to some extent intelligent SQL queries to extract certain information.

The post is divided in the following sections:

• SQL Queries List
  • Return groups that contain a specific string in their description
  • Return enabled applications that do not require role assignment
  • Return users that are members of a specific group
  • Return the groups a specific device is member of
  • Return groups linked to SharePoint resources available to authenticated users
  • Return groups assignable to roles
  • Return a list of users that have an Entra role
  • Return a list of Service Principals that have an assigned role
  • Return the assigned roles of a specific Service Principal
• References

SQL Queries List

This section provides sample SQL queries that can be performed on a Roadrecon database.

Return groups that contain a specific string in their description

SELECT displayName, objectId FROM Groups WHERE LOWER(description) LIKE LOWER('%sample%'); 

This query returns the names and the object ID of user groups that contain the case-insensitive string sample in their description. Same logic can be applied to fetch results from other tables.

Return enabled applications that do not require role assignment

SELECT 
    displayName, 
    replyUrls 
FROM ServicePrincipals 
WHERE accountEnabled = 1 AND servicePrincipalType = 'Application' AND appRoleAssignmentRequired = 0 AND LOWER(replyUrls) LIKE LOWER('%https://%');

This query returns the name and the redirect URL(s) of active application service principals that do not require role assignment. It is particularly helpful in identifying SSO applications tenant users have access to.

Return users that are members of a specific group

SELECT 
    u.displayName 
FROM Users u 
INNER JOIN lnk_group_member_user gu ON u.objectId = gu."User" 
WHERE gu."Group" = 'groupID' 
ORDER BY u.displayName ASC;

List users that are members of the provided group.

Return the groups a specific device is member of

select 
    d.displayName, 
    g.displayName, 
    g.dirSyncEnabled, 
    g.description, 
    g.isAssignableToRole 
from lnk_group_member_device gd 
inner join Devices d on d.objectId = gd."Devices" on d.objectId = gd."Device" 
inner join Groups g on g.objectId = gd."Group" 
where d.displayName like 'DEVICE_NAME' 
order by g.displayName asc;

List the groups an Entra device is member of.

Return groups linked to SharePoint resources available to authenticated users

select 
    displayName, 
    description, 
    securityEnabled, 
    isPublic 
from Groups 
where (securityEnabled = 0 and isPublic = 1 and sharepointResources not like '[]') 
order by displayName asc;

List public groups that are not security-enabled and are linked to SharePoint resources.

Return groups assignable to roles

select 
    displayName, 
    description 
from Groups 
where isAssignableToRole = 1 
order by displayName asc;

List all groups (and their descriptions) that can be assigned to Entra roles.

Return a list of users that have an Entra role

select 
    u.displayName as 'UserDisplayName', 
    r.displayName as 'RoleDisplayName', 
    u.objectId as 'UserObjectID', 
    r.objectId as 'DirectoryRoleObjectId' 
from lnk_role_member_user rg 
left join Users u on u.objectId = rg."User" 
left join DirectoryRoles r on rg."DirectoryRole" = r.objectId 
where u.accountEnabled = 1 and rg.DirectoryRole is not null 
order by u.DisplayName asc;

List users along with their Entra role.

Return a list of Service Principals that have an assigned role

select distinct
    a.principalDisplayName
from AppRoleAssignments a
join ServicePrincipals s on a.resourceId = s.objectId
where a.principalType = 'ServicePrincipal';

Return the assigned roles of a specific Service Principal

Show the assigned roles of a specific Service Principal providing the client ID.

select
    sp1.displayName as 'Service Principal',
    sp2.appId as 'Application ID',
    a.resourceDisplayName as 'Resource',
    json_extract(role.value, '$.value') as 'Assigned Role'
from AppRoleAssignments a
join ServicePrincipals sp1 on a.principalId = sp1.objectId
join ServicePrincipals sp2 on a.resourceId = sp2.objectId
join json_each(sp2.appRoles) on json_extract(role.value, '$.id') = a.id
where s.appId = '<APPLICATION_ID>';

References

[1] https://dirkjanm.io/introducing-roadtools-and-roadrecon-azure-ad-exploration-framework/

[2] https://github.com/dirkjanm/ROADtools

[3] https://sqlitebrowser.org/