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.'
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')
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')
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 Name | Description |
|---|---|
| Get-SQLDomainAccountPolicy | Provides the domain account policy for the SQL Server’s domain. |
| Get-SQLDomainComputer | Provides a list of the domain computers on the SQL Server’s domain. |
| Get-SQLDomainController | Provides a list of the domain controllers on the SQL Server’s domain. |
| Get-SQLDomainExploitableSystem | Provides a list of the potential exploitable computers on the SQL Server’s domain based on Operating System version information. |
| Get-SQLDomainGroup | Provides a list of the domain groups on the SQL Server’s domain. |
| Get-SQLDomainGroupMember | Provides a list of the domain group members on the SQL Server’s domain. |
| Get-SQLDomainObject | Can be used to execute arbitrary LDAP queries on the SQL Server’s domain. |
| Get-SQLDomainOu | Provides a list of the organization units on the SQL Server’s domain. |
| Get-SQLDomainPasswordsLAPS | Provides a list of the local administrator password on the SQL Server’s domain. This typically required Domain Admin privileges. |
| Get-SQLDomainSite | Provides a list of sites. |
| Get-SQLDomainSubnet | Provides a list of subnets. |
| Get-SQLDomainTrust | Provides a list of domain trusts. |
| Get-SQLDomainUser | Provides a list of the domain users on the SQL Server’s domain. |
| Get-SQLDomainUser -UserState Disabled | Provides a list of the disabled domain users on the SQL Server’s domain. |
| Get-SQLDomainUser -UserState Enabled | Provides a list of the enabled domain users on the SQL Server’s domain. |
| Get-SQLDomainUser -UserState Locked | Provides a list of the locked domain users on the SQL Server’s domain. |
| Get-SQLDomainUser -UserState PreAuthNotRequired | Provides a list of the domain users that do not require Kerberos preauthentication on the SQL Server’s domain. |
| Get-SQLDomainUser -UserState PwLastSet 90 | This 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 PwNeverExpires | Provides a list of the domain users that never expire on the SQL Server’s domain. |
| Get-SQLDomainUser -UserState PwNotRequired | Provides a list of the domain users with the PASSWD_NOTREQD flag set on the SQL Server’s domain. |
| Get-SQLDomainUser -UserState PwStoredRevEnc | Provides a list of the domain users storing their password using reversible encryption on the SQL Server’s domain. |
| Get-SQLDomainUser -UserState SmartCardRequired | Provides a list of the domain users that require smart card for interactive login on the SQL Server’s domain. |
| Get-SQLDomainUser -UserState TrustedForDelegation | Provides a list of the domain users trusted for delegation on the SQL Server’s domain. |
| Get-SQLDomainUser -UserState TrustedToAuthForDelegation | Provides 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
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
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 User | Access |
|---|---|---|---|---|---|
| X | No | ||||
| X | Yes | ||||
| X | No | ||||
| X | No | ||||
| X | X | No | |||
| X | X | Yes | |||
| X | X | No | |||
| X | X | Yes |
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 User | Access |
|---|---|---|---|---|---|
| X | No | ||||
| X | Yes | ||||
| X | No | ||||
| X | Yes | ||||
| X | X | No | |||
| X | X | Yes | |||
| X | X | No | |||
| X | X | Yes |
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!
