Back

Dumping Active Directory Domain Info – with PowerUpSQL!

This blog walks through how to use the OLE DB ADSI provider in SQL Server to query Active Directory for information.  I’ll also share a number of new PowerUpSQL functions that can be used for automating common AD recon activities through SQL Server. Hopefully this will be useful to red teamers, pentesters, and database enthusiasts. Thanks to Scott Sutherland (@_nullbind) for his work on both the AD recon functions and PowerUpSQL!

The T-SQL

The T-SQL below shows how the ADSI provider is used with OPENQUERY and OPENROWSET to query for Active Directory information. First, a SQL Server link needs to be created for the ADSI provider. A link is created with the name “ADSI”.

-- Create SQL Server link to ADSI
IF (SELECT count(*) FROM master..sysservers WHERE srvname = 'ADSI') = 0
EXEC master.dbo.sp_addlinkedserver @server = N'ADSI',
@srvproduct=N'Active Directory Service Interfaces',
@provider=N'ADSDSOObject',
@datasrc=N'adsdatasource'
ELSE
SELECT 'The target SQL Server link already exists.'
Img Afdf C E

If using OPENQUERY, associate the link with the current authentication context. A username and password can also be specified here. Then run the example query.

Note: The LDAP “path” should be set to the target domain.

-- Define authentication context - OpenQuery
EXEC sp_addlinkedsrvlogin
@rmtsrvname=N'ADSI',
@useself=N'True',
@locallogin=NULL,
@rmtuser=NULL,
@rmtpassword=NULL
GO
-- Use openquery
SELECT *
FROM OPENQUERY([ADSI],'<LDAP://path>;(&(objectCategory=Person)(objectClass=user));name, adspath;subtree')
Img Afdf E D

If using OPENROWSET, enable ad hoc queries. Then run the example query with a specified username and password or default authentication.

Note: The LDAP “path” should be set to the target domain.

-- Enable 'Show Advanced Options'
EXEC sp_configure 'Show Advanced Options', 1
RECONFIGURE
GO

-- Enable 'Ad Hoc Distributed Queries'
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE
GO

-- Run with openrowset
SELECT *
FROM OPENROWSET('ADSDSOOBJECT','adsdatasource',
'<LDAP://path>;(&(objectCategory=Person)(objectClass=user));name, adspath;subtree')
Img Afdf Cc C

Loading PowerUpSQL

PowerUpSQL can be loaded quite a few different ways in PowerShell. Below is a basic example showing how to download and import the module from GitHub.

IEX(New-Object System.Net.WebClient).DownloadString("https://raw.githubusercontent.com/NetSPI/PowerUpSQL/master/PowerUpSQL.ps1")

Newly Added Active Directory Recon Functions

Now that you have PowerUpSQL loaded, you can use the new commands to execute queries against the domain.  However, please be aware that all commands require sysadmin privileges.

Function NameDescription
Get-SQLDomainAccountPolicyProvides the domain account policy for the SQL Server’s domain.
Get-SQLDomainComputerProvides a list of the domain computers on the SQL Server’s domain.
Get-SQLDomainControllerProvides a list of the domain controllers on the SQL Server’s domain.
Get-SQLDomainExploitableSystemProvides a list of the potential exploitable computers on the SQL Server’s domain based on Operating System version information.
Get-SQLDomainGroupProvides a list of the domain groups on the SQL Server’s domain.
Get-SQLDomainGroupMemberProvides a list of the domain group members on the SQL Server’s domain.
Get-SQLDomainObjectCan be used to execute arbitrary LDAP queries on the SQL Server’s domain.
Get-SQLDomainOuProvides a list of the organization units on the SQL Server’s domain.
Get-SQLDomainPasswordsLAPSProvides a list of the local administrator password on the SQL Server’s domain. This typically required Domain Admin privileges.
Get-SQLDomainSiteProvides a list of sites.
Get-SQLDomainSubnetProvides a list of subnets.
Get-SQLDomainTrustProvides a list of domain trusts.
Get-SQLDomainUserProvides a list of the domain users on the SQL Server’s domain.
Get-SQLDomainUser -UserState DisabledProvides a list of the disabled domain users on the SQL Server’s domain.
Get-SQLDomainUser -UserState EnabledProvides a list of the enabled domain users on the SQL Server’s domain.
Get-SQLDomainUser -UserState LockedProvides a list of the locked domain users on the SQL Server’s domain.
Get-SQLDomainUser -UserState PreAuthNotRequiredProvides a list of the domain users that do not require Kerberos preauthentication on the SQL Server’s domain.
Get-SQLDomainUser -UserState PwLastSet 90This parameter can be used to list users that have not changed their password in the last 90 days. Any number can be provided though.
Get-SQLDomainUser -UserState PwNeverExpiresProvides a list of the domain users that never expire on the SQL Server’s domain.
Get-SQLDomainUser -UserState PwNotRequiredProvides a list of the domain users with the PASSWD_NOTREQD flag set on the SQL Server’s domain.
Get-SQLDomainUser -UserState PwStoredRevEncProvides a list of the domain users storing their password using reversible encryption on the SQL Server’s domain.
Get-SQLDomainUser -UserState SmartCardRequiredProvides a list of the domain users that require smart card for interactive login on the SQL Server’s domain.
Get-SQLDomainUser -UserState TrustedForDelegationProvides a list of the domain users trusted for delegation on the SQL Server’s domain.
Get-SQLDomainUser -UserState TrustedToAuthForDelegationProvides a list of the domain users trusted to authenticate for delegation on the SQL Server’s domain.

Dumping Domain Users Examples

This example shows how to gather a list of enabled domain users using a Linked Server via OPENQUERY.

Get-SQLDomainUser -Instance MSSQLSRV04SQLSERVER2014 -Verbose -UserState Enabled
Img Afdfb A Ffee

Alternatively, the command can be run using ad hoc queries via OPENROWSET as shown below.  Its nothing crazy, but it does provide a few options for avoiding detection if the DBAs are auditing for linked server creation, but not ad hoc queries in the target environment.

Get-SQLDomainUser -Instance MSSQLSRV04SQLSERVER2014 -Verbose -UserState Enabled -UseAdHoc
Img Afdfb D Caec

The functions also support providing an alternative SQL Server login for authenticating to the SQL Server and an alternative Windows credential for configuring server links.  More command examples can be found here.

The Authentication and Authorization Matrix

Depending on the current user’s security context or the provided credentials, the user may not have access to query AD for information. The tables below illustrate privileges and the corresponding access.

OPENQUERY (Linked server) auth table by Scott Sutherland (@_nullbind)

Current User (Domain User – Public)Current User (Domain User – Sysadmin)Current User (SQL Login – Public)Current User (SQL Login – Sysadmin)Provided Domain UserAccess
XNo
XYes
XNo
XNo
XXNo
XXYes
XXNo
XXYes

OPENROWSET (Ad Hoc query) auth table by Scott Sutherland (@_nullbind)

Current User (Domain User – Public)Current User (Domain User – Sysadmin)Current User (SQL Login – Public)Current User (SQL Login – Sysadmin)Provided Domain UserAccess
XNo
XYes
XNo
XYes
XXNo
XXYes
XXNo
XXYes

Conclusion

Recon is an essential first step in assessing the security of an Active Directory environment. Thanks to some great work by Will Schroeder (@harmj0y) and others on Powerview. Hopefully these AD recon functions will provide another medium to accomplish the same end.  For more information on the newly added AD recon functions, check out the PowerUpSQL wiki!

Discover how the NetSPI BAS solution helps organizations validate the efficacy of existing security controls and understand their Security Posture and Readiness.

X