Skip to content

Instantly share code, notes, and snippets.

@nullbind

nullbind/Find-DbaSqlInstance Mock Up

Last active August 15, 2017 14:17
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save nullbind/010a2fc444a89c5c6f6c950299bf24df to your computer and use it in GitHub Desktop.
Save nullbind/010a2fc444a89c5c6f6c950299bf24df to your computer and use it in GitHub Desktop.
Download ZIP
Find-DbaSqlInstance Mock Up
Raw
Find-DbaSqlInstance Mock Up
Function Find-DbaSqlInstance
{
<#
.SYNOPSIS
This function can be used to enumerate SQL Server instances using common methods.
.EXAMPLE
Check if the provided computer(s) or instance(s) respond to ping.
PS C:\> Find-DbaSqlInstance -Verbose -ScanPing -ComputerName SQLServer1.domain.com
PS C:\> Find-DbaSqlInstance -Verbose -ScanPing -Instance SQLServer1.domain.com\Instance1
PS C:\> Find-DbaSqlInstance -Verbose -ScanPing -IPRange 10.1.2.1-23
PS C:\> Find-DbaSqlInstance -Verbose -ScanPing -InstanceFile c:\temp\SQLServers.txt
VERBOSE: PING SCAN DISCOVERY START
VERBOSE: Scanning 50 systems
VERBOSE: - SQLServer1.domain.com - Ping scan start.
VERBOSE: - SQLServer1.domain.com - Ping scan complete.
VERBOSE: Found 10 live systems
VERBOSE: PING SCAN DISCOVERY COMPLETE
IP Address : 10.10.10.30
ComputerName : SQLServer1.domain.com
Domain : domain.com
Instance : SQLServer1.domain.com\Instance1
InstanceName : Instance1
TCPPort : Unknown
TCPPortOpen : Unknown
UDPPort : Unknown
UDPPortOpen : Unknown
Version : Unknown
IsClustered : Unknown
Pingable : Yes
LoggedIn : Unknown
SPN : Unknown
SvcAccount : Unknown
SvcLastLogon : Unknown
SvcState : Unknown
SvcPath : Unknown
.EXAMPLE
Performs UDP scan of port 1434 to identify potential SQL Server instances.
PS C:\> Find-DbaSqlInstance -Verbose -ScanUDP -ComputerName SQLServer1.domain.com
PS C:\> Find-DbaSqlInstance -Verbose -ScanUDP -Instance SQLServer1.domain.com\Instance1
PS C:\> Find-DbaSqlInstance -Verbose -ScanUDP -IPRange 10.1.2.1-23
PS C:\> Find-DbaSqlInstance -Verbose -ScanUDP -InstanceFile c:\temp\SQLServers.txt
VERBOSE: UDP SCAN INSTANCE DISCOVERY START
VERBOSE: Scanning 50 systems
VERBOSE: - SQLServer1.domain.com - UDP Scan Start.
VERBOSE: - SQLServer1.domain.com - UDP Scan Complete.
VERBOSE: Found 10 instances
VERBOSE: UDP SCAN INSTANCE DISCOVERY COMPLETE
IP Address : 10.10.10.30
ComputerName : SQLServer1.domain.com
Domain : domain.com
Instance : SQLServer1.domain.com\Instance1
InstanceName : Instance1
TCPPort : 51663
TCPPortOpen : Yes
UDPPort : 1434
UDPPPortOpen : Yes
Version : 11.0.2100.60
IsClustered : No
Pingable : Unknown
LoggedIn : Unknown
SPN : Unknown
SvcAccount : Unknown
SvcLastLogon : Unknown
SvcState : Running
SvcPath : Unknown
...
.EXAMPLE
Performs TCP scan of port 1433 to identify potential SQL Server instances.
PS C:\> Find-DbaSqlInstance -Verbose -ScanTCP -ComputerName SQLServer1.domain.com
PS C:\> Find-DbaSqlInstance -Verbose -ScanTCP -Instance SQLServer1.domain.com\Instance1
PS C:\> Find-DbaSqlInstance -Verbose -ScanTCP -InstanceFile c:\temp\SQLServers.txt
PS C:\> Find-DbaSqlInstance -Verbose -ScanTCP -TCPPort 1433 -IPRange 10.1.2.1-23
PS C:\> $Instances | Find-DbaSqlInstance -Verbose -ScanTCP -TCPPort 51663
VERBOSE: TCP PORT DISCOVERY START
VERBOSE: Scanning 1 Systems
VERBOSE: - SQLServer1.domain.com - TCP Scan Start.
VERBOSE: - SQLServer1.domain.com - TCP Scan Complete.
VERBOSE: Found 1 open port
VERBOSE: TCP PORT DISCOVERY COMPLETE
IP Address : 10.10.10.30
ComputerName : SQLServer1.domain.com
Domain : domain.com
Instance : SQLServer1.domain.com\Instance1
InstanceName : Instance1
TCPPort : 51663
TCPPortOpen : Yes
UDPPort : Unknown
UDPPPortOpen : Unknown
Version : Unknown
IsClustered : Unknown
Pingable : Unknown
LoggedIn : Unknown
SPN : Unknown
SvcAccount : Unknown
SvcLastLogon : Unknown
SvcState : Unknown
SvcPath : Unknown
.EXAMPLE
Performs LDAP query for SQL Server related Service Principal Names to identify potential SQL Server instances.
PS C:\> Find-DbaSqlInstance -Verbose -DomainInstances
PS C:\> Find-DbaSqlInstance -Verbose -DomainInstances -DomainController 192.168.1.1 -Username domain\user1 -Password "Password123!"
PS C:\> Find-DbaSqlInstance -Verbose -DomainInstances -DomainController 192.168.1.1 -Username domain\user1 -Password "Password123!" -DomainAccountFilter MSSQLSvc
VERBOSE: DOMAIN INSTANCE DISCOVERY START
VERBOSE: Found 10 instances
VERBOSE: DOMAIN INSTANCE DISCOVERY COMPLETE
IP Address : Unknown
ComputerName : SQLServer1.domain.com
Domain : domain.com
Instance : SQLServer1.domain.com\Instance1
InstanceName : Instance1
TCPPort : Unknown
TCPPortOpen : Unknown
UDPPort : Unknown
Version : Unknown
IsClustered : Unknown
Pingable : Unknown
LoggedIn : Unknown
SPN : MSSQLSvc/SQLServer1.domain.com
SvcAccount : MSSQLSVC
SvcLastLogon : 6/22/2016 9:00 AM
SvcState : Unknown
SvcPath : Unknown
...
.EXAMPLE
Perform scan of Windows services related to SQL Server to identify potential SQL Server instances.
PS C:\> Find-DbaSqlInstance -Verbose -LocalInstances
VERBOSE: LOCAL INSTANCE DISCOVERY START
VERBOSE: Found 3 instances
VERBOSE: LOCAL INSTANCE DISCOVERY COMPLETEE
IP Address : 10.10.10.30
ComputerName : SQLServer1.domain.com
Domain : domain.com
Instance : SQLServer1.domain.com\Instance1
InstanceName : Instance1
TCPPort : Unknown
TCPPortOpen : Unknown
UDPPort : Unknown
Version : Unknown
IsClustered : Unknown
Pingable : Unknown
LoggedIn : Unknown
SPN : Unknown
SvcAccount : NT Service\MSSQL$Instance1
SvcLastLogon : Unknown
SvcState : Running
SvcPath : "C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\Binn\sqlservr.exe" -sInstance1
...
.EXAMPLE
Perform UDP broadcast to identify SQL Servers instances within the currents broadcast domain.
PS C:\> Find-DbaSqlInstance -Verbose -BroadCastUDP
VERBOSE: BROADCAST SCAN START
VERBOSE: - Sending request to broadcast adress 192.168.1.255
VERBOSE: - Waiting 5 seconds for responses
VERBOSE: - SQLServer1.domain.com responded
VERBOSE: - Received 1 response
VERBOSE: - Parsing responses
VERBOSE: BROADCAST SCAN COMPLETE
IP Address : 10.10.10.30
ComputerName : SQLServer1.domain.com
Domain : domain.com
Instance : SQLServer1.domain.com\Instance1
InstanceName : Instance1
TCPPort : 51663
TCPPortOpen : Yes
UDPPort : 1434
UDPPPortOpen : Yes
Version : 11.0.2100.60
IsClustered : No
Pingable : Unknown
LoggedIn : Unknown
SPN : Unknown
SvcAccount : Unknown
SvcLastLogon : Unknown
SvcState : Running
SvcPath : Unknown
.EXAMPLE
Perform LDAP query for Service Principal Names to identify domain accounts used to run multiple SQL Server instances in an Active Directory environment.
PS C:\> Find-DbaSqlInstance -Verbose -SharedDomainAccounts
PS C:\> Find-DbaSqlInstance -Verbose -SharedDomainAccounts -DomainController 192.168.1.1 -Username domain\user1 -Password "Password123!"
Domain Account : MSSQLSvc
Count : 42
Domain Account : DbSvcAcct1
Count : 23
Domain Account : DbSvcAcct2
Count : 2
.EXAMPLE
Attempt to log into SQL Server instances to verify access.
PS C:\> Find-DbaSqlInstance -Verbose -DomainInstances -AttemptLogin
PS C:\> Find-DbaSqlInstance -Verbose -ComputerName SQLServer1.domain.com -AttemptLogin -Username sa -Password "Pasword123!"
PS C:\> Find-DbaSqlInstance -Verbose -Instance SQLServer1.domain.com\Instance1 -AttemptLogin -Username sa -Password "Pasword123!"
PS C:\> Find-DbaSqlInstance -Verbose -InstanceFile c:\temp\SQLServers.txt -AttemptLogin -Username sa -Password "Pasword123!"
PS C:\> Find-DbaSqlInstance -Verbose -ScanUDP -IPRange 10.1.2.1-23 -AttemptLogin
PS C:\> Find-DbaSqlInstance -Verbose -ScanUDPBroadcast -ScanPing -AttemptLogin
VERBOSE: BROADCAST SCAN START
VERBOSE: - Sending request to broadcast adress 192.168.1.255
VERBOSE: - Waiting 5 seconds for responses
VERBOSE: - SQLServer1.domain.com responded
VERBOSE: - Received 1 response
VERBOSE: - Parsing responses
VERBOSE: BROADCAST SCAN COMPLETE
VERBOSE: PING SCAN DISCOVERY START
VERBOSE: Scanning 1 systems
VERBOSE: - SQLServer1.domain.com - Ping scan start.
VERBOSE: - SQLServer1.domain.com - Ping scan complete.
VERBOSE: Found 1 live systems
VERBOSE: PING SCAN DISCOVERY COMPLETE
VERBOSE: LOGIN SCAN START
VERBOSE: Attempting to log into 1 instances as CURRENT USER
VERBOSE: - SQLServer1.domain.com - Attempting login
VERBOSE: - SQLServer1.domain.com - Login succeess!
VERBOSE: 1 instance was logged into successfully
VERBOSE: LOGIN SCAN COMPLETE
IP Address : 10.10.10.30
ComputerName : SQLServer1.domain.com
Domain : domain.com
Instance : SQLServer1.domain.com\Instance1
InstanceName : Instance1
TCPPort : 51663
TCPPortOpen : Yes
UDPPort : 1434
UDPPPortOpen : Yes
Version : 11.0.2100.60
IsClustered : No
Pingable : Yes
LoggedIn : Yes
SPN : Unknown
SvcAccount : Unknown
SvcLastLogon : Unknown
SvcState : Running
SvcPath : Unknown
#>
[CmdletBinding(SupportsShouldProcess = $true)]
Param(
[Parameter(Mandatory = $false, HelpMessage = 'Domain user or SQL Server login.')]
[string]$Username,
[Parameter(Mandatory = $false, HelpMessage = 'Domain user or SQL Server login password.')]
[string]$Password,
[Parameter(Mandatory = $false,
HelpMessage = 'Number of threads to use for internal functions. Threads apply to hosts not queries.')]
[int]$Threads = 1,
[Parameter(Mandatory = $false,
ValueFromPipeline = $true,
ValueFromPipelineByPropertyName = $true,
HelpMessage = 'Ping host to verify it is accessible.')]
[switch]$ScanPing,
[Parameter(Mandatory = $false,
ValueFromPipeline = $true,
ValueFromPipelineByPropertyName = $true,
HelpMessage = 'Attempt to log into the target instances with current or provided credentials.')]
[switch]$AttemptLogin,
[Parameter(Mandatory = $false,
HelpMessage = 'Used to target a domain controller when perfoming SPN lookups with alternative credentials.')]
[string]$DomainController,
[Parameter(Mandatory = $false,
ValueFromPipeline = $true,
ValueFromPipelineByPropertyName = $true,
HelpMessage = 'Domain account to filter on when using Get-DomainInstance.')]
[string]$DomainAccountFilter,
[Parameter(Mandatory = $false,
ValueFromPipeline = $true,
ValueFromPipelineByPropertyName = $true,
HelpMessage = 'Performs LDAP query for SQL Server related Service Principal Names to identify shared domain service accounts.')]
[switch]$SharedDomainAccounts,
[Parameter(Mandatory = $false,
ValueFromPipeline = $true,
ValueFromPipelineByPropertyName = $true,
HelpMessage = 'Instance to target.')]
[string]$Instance,
[Parameter(Mandatory = $false,
ValueFromPipeline = $true,
ValueFromPipelineByPropertyName = $true,
HelpMessage = 'Computer name to target.')]
[string]$ComputerName,
[Parameter(Mandatory = $false,
ValueFromPipeline = $true,
ValueFromPipelineByPropertyName = $true,
HelpMessage = 'File containing list of instances or computers to target. One per line.')]
[string]$InstanceFile,
[Parameter(Mandatory = $false,
ValueFromPipeline = $true,
ValueFromPipelineByPropertyName = $true,
HelpMessage = 'IP range to target.')]
[string]$IPRange,
[Parameter(Mandatory = $false,
ValueFromPipeline = $true,
ValueFromPipelineByPropertyName = $true,
HelpMessage = 'Performs scan of Windows services related to SQL Server to identify potential SQL Server instances.')]
[switch]$LocalInstances,
[Parameter(Mandatory = $false,
ValueFromPipeline = $true,
ValueFromPipelineByPropertyName = $true,
HelpMessage = 'Performs LDAP query for SQL Server related Service Principal Names to identify potential SQL Server instances.')]
[switch]$DomainInstances,
[Parameter(Mandatory = $false,
ValueFromPipeline = $true,
ValueFromPipelineByPropertyName = $true,
HelpMessage = 'Performs TCP scan of port 1433 or provided port to identify potential SQL Server instances.')]
[switch]$ScanTCP,
[Parameter(Mandatory = $false,
ValueFromPipelineByPropertyName = $true,
HelpMessage = 'This is the port used for TCP scanning.')]
[int]$TCPPort = 1433,
[Parameter(Mandatory = $false,
ValueFromPipelineByPropertyName = $true,
HelpMessage = 'Performs UDP scan of port 1434 to identify potential SQL Server instances.')]
[switch]$ScanUDP,
[Parameter(Mandatory = $false,
ValueFromPipelineByPropertyName = $true,
HelpMessage = 'This is the port used for UDP scanning.')]
[int]$UDPPort,
[Parameter(Mandatory = $false,
ValueFromPipelineByPropertyName = $true,
HelpMessage = 'Timeout in seconds for UDP scans.')]
[int]$ScanUDPTimeout = 3,
[Parameter(Mandatory = $false,
ValueFromPipelineByPropertyName = $true,
HelpMessage = 'Performs UDP broadcast to identify SQL Servers instances within the currents broadcast domain.')]
[switch]$BroadCastUDP
)
BEGIN
{
# Create tbl_targets data table
# Get provided computername + add to tbl_targets
# Get provided instance + add to tbl_targets
# Get instances from provided file path + add to tbl_targets
# Get instances from local service configurations + add to tbl_targets
# [System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources() place holder
# Get IP targets from provided range + add to tbl_targets
}
PROCESS
{
# Get instances from pipeline + add to tbl_targets
}
END
{
# Create tbl_results table
# Perform UDP scan + add to results table
# Perform UDP broadcast + add to results table
# Perform TCP scan + add to results table
# Perform SPN lookup + add to results table
# Perform ping scan + update results table (ping status)
# Attempt to log into discovered SQL Servers from results table + update results table (login status)
# Verbose output: Display number of total targets (tbl_targets)
# Verbose output: Display number of discovered instances (tbl_results)
# Verbose output: Display number of computers associated with instances (tbl_results)
# Verbose output: Display number of pingable computers (tbl_results)
# Verbose output: Display number of instances that can be logged into with provided credentials (tbl_results)
# Return tbl_results data table
# Get shared domain accounts (can't be run during normal discovery operations)
}
# -------------------------------------------
# Define private functions
# -------------------------------------------
# Get-SQLConnectionObject
# Get-SQLQuery
# Invoke-SQLLoginTest
# Get-DomainObject
# Get-DomainSpn
# Get-SQLInstanceDomain
# Get-SQLInstanceLocal
# Invoke-ScanPing
# Invoke-ScanTCP
# Invoke-ScanUDP
# Invoke-ScanUDPBroadcast
# Invoke-Parallel
# Invoke-ValidateIP
# Get-IPRange
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment