Skip to content

Commit fe45ab4

Browse files
thomasellingthomaselling
committed
Modified user enumeration and added site/subnets
1 parent 4999674 commit fe45ab4

2 files changed

Lines changed: 273 additions & 9 deletions

File tree

‎PowerUpSQL.ps1‎

Lines changed: 269 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -7278,7 +7278,9 @@ Function Get-SQLDomainUser
72787278
Using the OLE DB ADSI provider, query Active Directory for a list of domain users
72797279
via the domain logon server associated with the SQL Server. This can be
72807280
done using a SQL Server link (OpenQuery) or AdHoc query (OpenRowset). Use the -UseAdHoc
7281-
flag to switch between modes.
7281+
flag to switch between modes. The userstate parameter can also be used to filter users
7282+
by state such as disabled/locked, and property setting such as not requiring a password
7283+
or kerberos preauthentication.
72827284
.PARAMETER Username
72837285
SQL Server or domain account to authenticate with.
72847286
.PARAMETER Password
@@ -7295,8 +7297,24 @@ Function Get-SQLDomainUser
72957297
SQL Server instance to connection to.
72967298
.PARAMETER FilterUser
72977299
Domain user to filter for.
7300+
.PARAMETER UserState
7301+
Filter for users of specific state such as disabled, enabled, and locked.
72987302
.EXAMPLE
72997303
PS C:\> Get-SQLDomainUser -Instance SQLServer1\STANDARDDEV2014 -Verbose -UseAdHoc
7304+
Only grab enabled users.
7305+
.EXAMPLE
7306+
PS C:\> Get-SQLDomainUser -Instance SQLServer1\STANDARDDEV2014 -Verbose -UseAdHoc -UserState All
7307+
Only grab enabled users.
7308+
.EXAMPLE
7309+
PS C:\> Get-SQLDomainUser -Instance SQLServer1\STANDARDDEV2014 -Verbose -UseAdHoc -UserState Enabled
7310+
Only grab disabled users.
7311+
.EXAMPLE
7312+
PS C:\> Get-SQLDomainUser -Instance SQLServer1\STANDARDDEV2014 -Verbose -UseAdHoc -UserState Disabled
7313+
Only grab that don't require kerberos preauthentication.
7314+
PS C:\> Get-SQLDomainUser -Instance SQLServer1\STANDARDDEV2014 -Verbose -UseAdHoc -UserState PreAuthNotRequired
7315+
Only grab locked users.
7316+
.EXAMPLE
7317+
PS C:\> Get-SQLDomainUser -Instance SQLServer1\STANDARDDEV2014 -Verbose -UseAdHoc -UserState Locked
73007318
.EXAMPLE
73017319
PS C:\> Get-SQLDomainUser -Instance SQLServer1\STANDARDDEV2014 -Verbose -UseAdHoc -LinkUsername 'domain\user' -LinkPassword 'Password123!'
73027320
.EXAMPLE
@@ -7330,7 +7348,7 @@ Function Get-SQLDomainUser
73307348
[System.Management.Automation.Credential()]$Credential = [System.Management.Automation.PSCredential]::Empty,
73317349

73327350
[Parameter(Mandatory = $false,
7333-
ValueFromPipelineByPropertyName = $true,
7351+
ValueFromPipelineByPropertyName = $true,
73347352
HelpMessage = 'SQL Server instance to connection to.')]
73357353
[string]$Instance,
73367354

@@ -7339,18 +7357,28 @@ Function Get-SQLDomainUser
73397357
[Switch]$UseAdHoc,
73407358

73417359
[Parameter(Mandatory = $false,
7342-
ValueFromPipelineByPropertyName = $true,
7360+
HelpMessage = 'Filter users based on state or property settings.')]
7361+
[ValidateSet("All","Enabled","Disabled","Locked","PwNeverExpires","PwNotRequired","PreAuthNotRequired","SmartCardRequired","TrustedForDelegation","TrustedToAuthForDelegation","PwStoredRevEnc")]
7362+
[String]$UserState,
7363+
7364+
[Parameter(Mandatory = $false,
7365+
ValueFromPipelineByPropertyName = $true,
73437366
HelpMessage = 'Domain user to filter for.')]
73447367
[string]$FilterUser,
73457368

7369+
[Parameter(Mandatory = $false,
7370+
ValueFromPipelineByPropertyName = $true,
7371+
HelpMessage = 'Only list the users who have not changed their password in the number of days provided.')]
7372+
[Int]$PwLastSet,
7373+
73467374
[Parameter(Mandatory = $false,
73477375
HelpMessage = 'Suppress verbose errors. Used when function is wrapped.')]
73487376
[switch]$SuppressVerbose
73497377
)
73507378

73517379
Begin
73527380
{
7353-
# set instance to local host by default
7381+
# Set instance to local host by default
73547382
if(-not $Instance)
73557383
{
73567384
$Instance = $env:COMPUTERNAME
@@ -7360,15 +7388,43 @@ Function Get-SQLDomainUser
73607388
if((-not $FilterUser)){
73617389
$FilterUser = '*'
73627390
}
7391+
7392+
# Setup user state / property filter
7393+
if((-not $PwLastSet)){
7394+
$PwLastSetFilter = ""
7395+
}else{
7396+
7397+
# Get number of days from user and convert to timestamp
7398+
$DesiredTimeStamp = (Get-Date).AddDays(-$PwLastSet).ToFileTime()
7399+
7400+
# Use timestamp to create filter to only list the users who have not changed their password in the number of days provided.
7401+
$PwLastSetFilter = "(!pwdLastSet>=$DesiredTimeStamp)"
7402+
}
7403+
7404+
# Setup user state filter
7405+
switch ($UserState)
7406+
{
7407+
"All" {$UserStateFilter = ""}
7408+
"Enabled" {$UserStateFilter = "(!userAccountControl:1.2.840.113556.1.4.803:=2)"}
7409+
"Disabled" {$UserStateFilter = "(userAccountControl:1.2.840.113556.1.4.803:=2)"}
7410+
"Locked" {$UserStateFilter = "(sAMAccountType=805306368)(lockoutTime>0)"}
7411+
"PwNeverExpires" {$UserStateFilter = "(userAccountControl:1.2.840.113556.1.4.803:=65536)"}
7412+
"PwNotRequired" {$UserStateFilter = "(userAccountControl:1.2.840.113556.1.4.803:=32)"}
7413+
"PwStoredRevEnc" {$UserStateFilter = "(userAccountControl:1.2.840.113556.1.4.803:=128)"}
7414+
"PreAuthNotRequired" {$UserStateFilter = "(userAccountControl:1.2.840.113556.1.4.803:=4194304)"}
7415+
"SmartCardRequired" {$UserStateFilter = "(userAccountControl:1.2.840.113556.1.4.803:=262144)"}
7416+
"TrustedForDelegation" {$UserStateFilter = "(userAccountControl:1.2.840.113556.1.4.803:=524288)"}
7417+
"TrustedToAuthForDelegation" {$UserStateFilter = "(userAccountControl:1.2.840.113556.1.4.803:=16777216)"}
7418+
}
73637419
}
73647420

73657421
Process
73667422
{
73677423
# Call Get-SQLDomainObject
73687424
if($UseAdHoc){
7369-
Get-SQLDomainObject -Verbose -Instance $Instance -Username $Username -Password $Password -LinkUsername $LinkUsername -LinkPassword $LinkPassword -LdapFilter "(&(objectCategory=Person)(objectClass=user)(SamAccountName=$FilterUser))" -LdapFields "samaccountname,name,admincount,whencreated,whenchanged,adspath" -UseAdHoc
7425+
Get-SQLDomainObject -Verbose -Instance $Instance -Username $Username -Password $Password -LinkUsername $LinkUsername -LinkPassword $LinkPassword -LdapFilter "(&(objectCategory=Person)(objectClass=user)$PwLastSetFilter(SamAccountName=$FilterUser)$UserStateFilter)" -LdapFields "samaccountname,name,admincount,whencreated,whenchanged,adspath" -UseAdHoc
73707426
}else{
7371-
Get-SQLDomainObject -Verbose -Instance $Instance -Username $Username -Password $Password -LinkUsername $LinkUsername -LinkPassword $LinkPassword -LdapFilter "(&(objectCategory=Person)(objectClass=user)(SamAccountName=$FilterUser))" -LdapFields "samaccountname,name,admincount,whencreated,whenchanged,adspath"
7427+
Get-SQLDomainObject -Verbose -Instance $Instance -Username $Username -Password $Password -LinkUsername $LinkUsername -LinkPassword $LinkPassword -LdapFilter "(&(objectCategory=Person)(objectClass=user)$PwLastSetFilter(SamAccountName=$FilterUser)$UserStateFilter)" -LdapFields "samaccountname,name,admincount,whencreated,whenchanged,adspath"
73727428
}
73737429
}
73747430

@@ -7378,6 +7434,212 @@ Function Get-SQLDomainUser
73787434
}
73797435

73807436

7437+
# ----------------------------------
7438+
# Get-SQLDomainSubnet
7439+
# ----------------------------------
7440+
# Author: Scott Sutherland
7441+
Function Get-SQLDomainSubnet
7442+
{
7443+
<#
7444+
.SYNOPSIS
7445+
Using the OLE DB ADSI provider, query Active Directory for a list of domain subnets
7446+
via the domain logon server associated with the SQL Server. This can be
7447+
done using a SQL Server link (OpenQuery) or AdHoc query (OpenRowset). Use the -UseAdHoc
7448+
flag to switch between modes.
7449+
.PARAMETER Username
7450+
SQL Server or domain account to authenticate with.
7451+
.PARAMETER Password
7452+
SQL Server or domain account password to authenticate with.
7453+
.PARAMETER LinkUsername
7454+
Domain account used to authenticate to LDAP through SQL Server ADSI link.
7455+
.PARAMETER LinkPassword
7456+
Domain account password used to authenticate to LDAP through SQL Server ADSI link.
7457+
.PARAMETER UseAdHoc
7458+
Use adhoc connection for executing the query instead of a server link. The link option (default) will create an ADSI server link and use OpenQuery. The AdHoc option will enable adhoc queries, and use OpenRowSet.
7459+
.PARAMETER Credential
7460+
SQL Server credential.
7461+
.PARAMETER Instance
7462+
SQL Server instance to connection to.
7463+
.EXAMPLE
7464+
PS C:\> Get-SQLDomainComputer -Instance SQLServer1\STANDARDDEV2014 -Verbose -UseAdHoc
7465+
.EXAMPLE
7466+
PS C:\> Get-SQLDomainComputer -Instance SQLServer1\STANDARDDEV2014 -Verbose -UseAdHoc -LinkUsername 'domain\user' -LinkPassword 'Password123!'
7467+
.EXAMPLE
7468+
PS C:\> Get-SQLDomainComputer -Instance SQLServer1\STANDARDDEV2014 -Verbose
7469+
.EXAMPLE
7470+
PS C:\> Get-SQLDomainComputer -Instance SQLServer1\STANDARDDEV2014 -Verbose -LinkUsername 'domain\user' -LinkPassword 'Password123!'
7471+
.EXAMPLE
7472+
PS C:\> Get-SQLInstanceLocal | Get-SQLDomainComputer -Verbose
7473+
#>
7474+
[CmdletBinding()]
7475+
Param(
7476+
[Parameter(Mandatory = $false,
7477+
HelpMessage = 'SQL Server or domain account to authenticate to SQL Server.')]
7478+
[string]$Username,
7479+
7480+
[Parameter(Mandatory = $false,
7481+
HelpMessage = 'SQL Server or domain account password to authenticate to SQL Server.')]
7482+
[string]$Password,
7483+
7484+
[Parameter(Mandatory = $false,
7485+
HelpMessage = 'Domain account used to authenticate to LDAP through SQL Server ADSI link.')]
7486+
[string]$LinkUsername,
7487+
7488+
[Parameter(Mandatory = $false,
7489+
HelpMessage = 'Domain account password used to authenticate to LDAP through SQL Server ADSI link.')]
7490+
[string]$LinkPassword,
7491+
7492+
[Parameter(Mandatory = $false,
7493+
HelpMessage = 'Windows credentials.')]
7494+
[System.Management.Automation.PSCredential]
7495+
[System.Management.Automation.Credential()]$Credential = [System.Management.Automation.PSCredential]::Empty,
7496+
7497+
[Parameter(Mandatory = $false,
7498+
ValueFromPipelineByPropertyName = $true,
7499+
HelpMessage = 'SQL Server instance to connection to.')]
7500+
[string]$Instance,
7501+
7502+
[Parameter(Mandatory = $false,
7503+
HelpMessage = 'Use adhoc connection for executing the query instead of a server link. The link option (default) will create an ADSI server link and use OpenQuery. The AdHoc option will enable adhoc queries, and use OpenRowSet.')]
7504+
[Switch]$UseAdHoc,
7505+
7506+
[Parameter(Mandatory = $false,
7507+
HelpMessage = 'Suppress verbose errors. Used when function is wrapped.')]
7508+
[switch]$SuppressVerbose
7509+
)
7510+
7511+
Begin
7512+
{
7513+
# set instance to local host by default
7514+
if(-not $Instance)
7515+
{
7516+
$Instance = $env:COMPUTERNAME
7517+
}
7518+
}
7519+
7520+
Process
7521+
{
7522+
# Get the domain of the server
7523+
$Domain = Get-SQLServerInfo -SuppressVerbose -Instance $Instance -Username $Username -Password $Password | Select-Object DomainName -ExpandProperty DomainName
7524+
$DomainDistinguishedName = Get-SQLDomainObject -SuppressVerbose -Instance $Instance -Username $Username -Password $Password -LinkUsername $LinkUsername -LinkPassword $LinkPassword -LdapPath "$Domain" -LdapFilter "(name=$Domain)" -LdapFields 'distinguishedname' -UseAdHoc | Select-Object distinguishedname -ExpandProperty distinguishedname
7525+
7526+
# Call Get-SQLDomainObject
7527+
if($UseAdHoc){
7528+
Get-SQLDomainObject -Verbose -Instance $Instance -Username $Username -Password $Password -LinkUsername $LinkUsername -LinkPassword $LinkPassword -LdapFilter "(objectCategory=subnet)" -LdapPath "$Domain/CN=Sites,CN=Configuration,$DomainDistinguishedName" -LdapFields 'name,distinguishedname,siteobject,whencreated,whenchanged,location' -UseAdHoc
7529+
}else{
7530+
Get-SQLDomainObject -Verbose -Instance $Instance -Username $Username -Password $Password -LinkUsername $LinkUsername -LinkPassword $LinkPassword -LdapFilter "(objectCategory=subnet)" -LdapPath "$Domain/CN=Sites,CN=Configuration,$DomainDistinguishedName" -LdapFields 'name,distinguishedname,siteobject,whencreated,whenchanged,location'
7531+
}
7532+
}
7533+
7534+
End
7535+
{
7536+
}
7537+
}
7538+
7539+
7540+
# ----------------------------------
7541+
# Get-SQLDomainSite
7542+
# ----------------------------------
7543+
# Author: Scott Sutherland
7544+
Function Get-SQLDomainSite
7545+
{
7546+
<#
7547+
.SYNOPSIS
7548+
Using the OLE DB ADSI provider, query Active Directory for a list of domain sites
7549+
via the domain logon server associated with the SQL Server. This can be
7550+
done using a SQL Server link (OpenQuery) or AdHoc query (OpenRowset). Use the -UseAdHoc
7551+
flag to switch between modes.
7552+
.PARAMETER Username
7553+
SQL Server or domain account to authenticate with.
7554+
.PARAMETER Password
7555+
SQL Server or domain account password to authenticate with.
7556+
.PARAMETER LinkUsername
7557+
Domain account used to authenticate to LDAP through SQL Server ADSI link.
7558+
.PARAMETER LinkPassword
7559+
Domain account password used to authenticate to LDAP through SQL Server ADSI link.
7560+
.PARAMETER UseAdHoc
7561+
Use adhoc connection for executing the query instead of a server link. The link option (default) will create an ADSI server link and use OpenQuery. The AdHoc option will enable adhoc queries, and use OpenRowSet.
7562+
.PARAMETER Credential
7563+
SQL Server credential.
7564+
.PARAMETER Instance
7565+
SQL Server instance to connection to.
7566+
.EXAMPLE
7567+
PS C:\> Get-SQLDomainComputer -Instance SQLServer1\STANDARDDEV2014 -Verbose -UseAdHoc
7568+
.EXAMPLE
7569+
PS C:\> Get-SQLDomainComputer -Instance SQLServer1\STANDARDDEV2014 -Verbose -UseAdHoc -LinkUsername 'domain\user' -LinkPassword 'Password123!'
7570+
.EXAMPLE
7571+
PS C:\> Get-SQLDomainComputer -Instance SQLServer1\STANDARDDEV2014 -Verbose
7572+
.EXAMPLE
7573+
PS C:\> Get-SQLDomainComputer -Instance SQLServer1\STANDARDDEV2014 -Verbose -LinkUsername 'domain\user' -LinkPassword 'Password123!'
7574+
.EXAMPLE
7575+
PS C:\> Get-SQLInstanceLocal | Get-SQLDomainComputer -Verbose
7576+
#>
7577+
[CmdletBinding()]
7578+
Param(
7579+
[Parameter(Mandatory = $false,
7580+
HelpMessage = 'SQL Server or domain account to authenticate to SQL Server.')]
7581+
[string]$Username,
7582+
7583+
[Parameter(Mandatory = $false,
7584+
HelpMessage = 'SQL Server or domain account password to authenticate to SQL Server.')]
7585+
[string]$Password,
7586+
7587+
[Parameter(Mandatory = $false,
7588+
HelpMessage = 'Domain account used to authenticate to LDAP through SQL Server ADSI link.')]
7589+
[string]$LinkUsername,
7590+
7591+
[Parameter(Mandatory = $false,
7592+
HelpMessage = 'Domain account password used to authenticate to LDAP through SQL Server ADSI link.')]
7593+
[string]$LinkPassword,
7594+
7595+
[Parameter(Mandatory = $false,
7596+
HelpMessage = 'Windows credentials.')]
7597+
[System.Management.Automation.PSCredential]
7598+
[System.Management.Automation.Credential()]$Credential = [System.Management.Automation.PSCredential]::Empty,
7599+
7600+
[Parameter(Mandatory = $false,
7601+
ValueFromPipelineByPropertyName = $true,
7602+
HelpMessage = 'SQL Server instance to connection to.')]
7603+
[string]$Instance,
7604+
7605+
[Parameter(Mandatory = $false,
7606+
HelpMessage = 'Use adhoc connection for executing the query instead of a server link. The link option (default) will create an ADSI server link and use OpenQuery. The AdHoc option will enable adhoc queries, and use OpenRowSet.')]
7607+
[Switch]$UseAdHoc,
7608+
7609+
[Parameter(Mandatory = $false,
7610+
HelpMessage = 'Suppress verbose errors. Used when function is wrapped.')]
7611+
[switch]$SuppressVerbose
7612+
)
7613+
7614+
Begin
7615+
{
7616+
# set instance to local host by default
7617+
if(-not $Instance)
7618+
{
7619+
$Instance = $env:COMPUTERNAME
7620+
}
7621+
}
7622+
7623+
Process
7624+
{
7625+
# Get the domain of the server
7626+
$Domain = Get-SQLServerInfo -SuppressVerbose -Instance $Instance -Username $Username -Password $Password | Select-Object DomainName -ExpandProperty DomainName
7627+
$DomainDistinguishedName = Get-SQLDomainObject -SuppressVerbose -Instance $Instance -Username $Username -Password $Password -LinkUsername $LinkUsername -LinkPassword $LinkPassword -LdapPath "$Domain" -LdapFilter "(name=$Domain)" -LdapFields 'distinguishedname' -UseAdHoc | Select-Object distinguishedname -ExpandProperty distinguishedname
7628+
7629+
# Call Get-SQLDomainObject
7630+
if($UseAdHoc){
7631+
Get-SQLDomainObject -Verbose -Instance $Instance -Username $Username -Password $Password -LinkUsername $LinkUsername -LinkPassword $LinkPassword -LdapFilter "(objectCategory=site)" -LdapPath "$Domain/CN=Sites,CN=Configuration,$DomainDistinguishedName" -LdapFields 'name,distinguishedname,whencreated,whenchanged' -UseAdHoc
7632+
}else{
7633+
Get-SQLDomainObject -Verbose -Instance $Instance -Username $Username -Password $Password -LinkUsername $LinkUsername -LinkPassword $LinkPassword -LdapFilter "(objectCategory=site)" -LdapPath "$Domain/CN=Sites,CN=Configuration,$DomainDistinguishedName" -LdapFields 'name,distinguishedname,whencreated,whenchanged'
7634+
}
7635+
}
7636+
7637+
End
7638+
{
7639+
}
7640+
}
7641+
7642+
73817643
# ----------------------------------
73827644
# Get-SQLDomainComputer
73837645
# ----------------------------------
@@ -8400,7 +8662,7 @@ Function Get-SQLDomainGroupMember
84008662
{
84018663
<#
84028664
.SYNOPSIS
8403-
Using the OLE DB ADSI provider, query Active Directory for a list of domain groups
8665+
Using the OLE DB ADSI provider, query Active Directory for a list of domain group members
84048666
via the domain logon server associated with the SQL Server. This can be
84058667
done using a SQL Server link (OpenQuery) or AdHoc query (OpenRowset). Use the -UseAdHoc
84068668
flag to switch between modes.

‎PowerUpSQL.psd1‎

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -25,10 +25,12 @@
2525
'Get-SQLDatabaseRoleMember',
2626
'Get-SQLDatabaseSchema',
2727
'Get-SQLDatabaseThreaded',
28-
'Get-SQLDatabaseUser',
28+
'Get-SQLDatabaseUser',
2929
'Get-SQLDomainObject',
3030
'Get-SQLDomainComputer',
31-
'Get-SQLDomainUser',
31+
'Get-SQLDomainUser',
32+
'Get-SQLDomainSubnet',
33+
'Get-SQLDomainSite',
3234
'Get-SQLDomainGroup',
3335
'Get-SQLDomainOu',
3436
'Get-SQLDomainAccountPolicy',

0 commit comments

Comments
 (0)