Active Directory Recon Functions
The PowerUpSQL functions listed below use the OLE DB ADSI provider to query Active Directory for domain users, computers, and other configuration information through SQL Server queries. Each function can execute it's LDAP queries through a Linked Server (OPENQUERY) or ad hoc query (OPENROWSET). Additional information can be found here.
Important Notes:
- All functions require the sysadmin role.
- The OLE DB ADSI provider limitation: Multi-valued attributes cannot be retrieved ex. "description" or "memberof". Will cause "Could not convert the data value due to reasons other than sign mismatch or overflow."
- The OLE DB ADSI provider limitation: Paging is not supported. The max results returned is limited to the value set by AD ex. default usually 1000 in Server 2012.
Raw TSQL query examples can be found here, but below is a list of PowerUpSQL functions designed to streamline common use cases.
| 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 change 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. |
If you're interested in a cheatsheet with additional LDAP queries check out out this Microsoft article on ldap filters. Also, this Microsoft article provides a basic overview of the Active Directory object model.
Connect to the SQL Server with the current Windows/Domain user. Create a linked server for LDAP queries. Authenticate to LDAP as the current Windows/Domain user. The current Windows/Domain user must be a member of the sysadmin role.
Get-SQLDomainUser -Verbose -Instance MSSQLSRV04\SQLSERVER2014
Connect to the SQL Server with any sysadmin login. Create a linked server for LDAP queries. Authenticate to LDAP using the Windows/Domain credentials provided in the LinkUsername and LinkPassword parameters.
Get-SQLDomainUser -Verbose -Instance MSSQLSRV04\SQLSERVER2014 -Username sa -Password 'Pass123!' -LinkUsername 'demo\administrator' -LinkPassword 'BestPasswordEver!'
Connect to the SQL Server with the current Windows/Domain user. Create an ad hoc query to connect to LDAP. Authenticate to LDAP as the current Windows/Domain user. The current Windows/Domain user must be a member of the sysadmin role.
Get-SQLDomainUser -Verbose -Instance MSSQLSRV04\SQLSERVER2014 -UseAdHoc
Connect to the SQL Server with the sa login. Create an ad hoc query to connect to LDAP. Authenticate to LDAP using the Windows/Domain credentials provided in the LinkUsername and LinkPassword parameters.
Get-SQLDomainUser -Verbose -Instance MSSQLSRV04\SQLSERVER2014 -UseAdHoc -Username sa -Password 'Pass123!' -LinkUsername 'demo\administrator' -LinkPassword 'BestPasswordEver!'
Connect to the SQL Server with the sa login. Create an ad hoc query to connect to LDAP. Authenticate to LDAP as SQL Server service account.
Get-SQLDomainUser -Verbose -Instance MSSQLSRV04\SQLSERVER2014 -UseAdHoc -Username sa -Password 'Pass123!'
Huge thanks to Thomas Elling (thomaselling) for getting these functions off the ground!