Skip to content

Instantly share code, notes, and snippets.

@nullbind

nullbind/Find-DbaSqlInstance.ps1

Last active February 12, 2018 07:32
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/6c528e30c88fbc0f9eea99636d11ca23 to your computer and use it in GitHub Desktop.
Save nullbind/6c528e30c88fbc0f9eea99636d11ca23 to your computer and use it in GitHub Desktop.
Download ZIP
Find-DbaSqlInstance
Raw
Find-DbaSqlInstance.ps1
# Script: Find-DbaSqlInstance
# version 0.14.5
# Author: Scott Sutherland, 2018 NetSPI
# Contributions from: Warren F. (Invoke-Parallel), https://gallery.technet.microsoft.com/scriptcenter/List-the-IP-addresses-in-a-60c5bb6b
# ------------------------
# Current feature summary
# ------------------------
# 1 - Import list of IP addresses, computer names, or SQL instances from a file via "-SqlInstanceFile" param - works
# 2 - Import SQL instance via "-SQLInstance" param - works
# 3 - Import computer name from "-ComputerName" param - works
# 4 - Import list of local running instances with "-LocalInstancesLocal" param - works
# 5 - Import list of SQL instances from results of Broadcast domain UDP ping with "-BroadCastUDP" param - works
# 6 - Import list of SQL instances from results of Active Directory LDAP query for MSSQL SPNs with "-DomainInstances" param - works
# 7 - Generate IPs from IP - Works - Uses https://gallery.technet.microsoft.com/scriptcenter/List-the-IP-addresses-in-a-60c5bb6b
# 8 - Generate IPs from subnet - Works - Uses https://gallery.technet.microsoft.com/scriptcenter/List-the-IP-addresses-in-a-60c5bb6b
# 9 - Generate IPs from CIDR - Works - Uses https://gallery.technet.microsoft.com/scriptcenter/List-the-IP-addresses-in-a-60c5bb6b
# 10 - Generate IPs from Range - Works - Uses https://gallery.technet.microsoft.com/scriptcenter/List-the-IP-addresses-in-a-60c5bb6b
# 11 - Ping scan - works - super slow, need to rewrite - Uses test-connection
# 12 - UDP scan - works - needs to be threaded though
# 13 - TCP scan - works - only validates open port, no service validation - uses Invoke-Parallel for threading
# 14 - Login scan - works - needs to be threaded
# ----------------------
# Pending Items Summary
# ----------------------
# Finish function for resolving IP addresses
# Update ScanUDP to be threaded
# Link -All parameter to options
# Centralize internal functions and wrap with pretty region
# Clean up style stuff
# Verify it's cool to use Invoke-Parallel for runspace management/threading
# Verify logic around tblScanList vs tblTargets makes sense
# - tblTargets holds all provided and verified instance and computer names, discovered list is displayed by default
# - tblscanlist holds the provided ip list based on -IPaddress input, it is used for additional discovery
# - if no scan discovery options then - add scanlist to targets, ip address in computername
# - if no scanlogin - just display discovered items
# ------------
# Know issues
# ------------
# - Error when providing ip range and items on the pipeline, pipeline items are not incl
# - Uniqing sql instances can be challenging because the computername can be an ip, hostname, or fq hostname
# Example: ipaddress\instance, computername\instance, computername.domain.com\instance are all the same
#---------------------------------
# Find-DbaSqlInstance
#---------------------------------
# Author: Scott Sutherland, 2018 NetSPI
# Contributions from: Warren F. (Invoke-Parallel), https://gallery.technet.microsoft.com/scriptcenter/List-the-IP-addresses-in-a-60c5bb6b
Function Find-DbaSqlInstance
{
<#
.SYNOPSIS
This function can be used to enumerate SQL Server instances using common methods.
.EXAMPLE
Below are common command examples.
PS C:\> Find-DbaSqlInstance -Verbose -ComputerName SQLServer1.domain.com
PS C:\> Find-DbaSqlInstance -Verbose -SQLInstance SQLServer1.domain.com\Instance1
PS C:\> Find-DbaSqlInstance -Verbose -SQLInstanceFile c:\temp\SQLServers.txt
PS C:\> Find-DbaSqlInstance -Verbose -IPAddress 192.168.1.1 -ScanUDP
PS C:\> Find-DbaSqlInstance -Verbose -IPAddress 192.168.1.1/24 -ScanUDP
PS C:\> Find-DbaSqlInstance -Verbose -IPAddress 192.168.1.1 -Mask 255.255.255.0 -ScanUDP
PS C:\> Find-DbaSqlInstance -Verbose -IPAddress 192.168.1.1-192.168.1.5 -ScanTCP -TCPPort 1433
PS C:\> Find-DbaSqlInstance -Verbose -IPAddress 192.168.1.1-192.168.1.5 -ScanUDP -ScanLogin
PS C:\> Find-DbaSqlInstance -Verbose -LocalInstances
PS C:\> Find-DbaSqlInstance -Verbose -BroadCastUDP
PS C:\> Find-DbaSqlInstance -Verbose -DomainInstances
PS C:\> Find-DbaSqlInstance -Verbose -ComputerName computer1 -DomainInstances -SQLInstance computer2\instance2 -SQLInstanceFile C:\temp\sqlinstances.txt -LocalInstances -BroadCastUDP -IpAddress 192.168.1.1-192.168.1.5
PS C:\> "192.168.1.1","ComputerName","ComputerName\Instance","ComputerNanmem,1433","ComputerName:1433" | Find-DbaSqlInstance -Verbose
PS C:\> "192.168.1.1","ComputerName","ComputerName\Instance","ComputerNanmem,1433","ComputerName:1433" | Find-DbaSqlInstance -Verbose -LocalInstances -DomainInstances -BroadCastUDP
PS C:\> "192.168.1.1","ComputerName","ComputerName\Instance","ComputerNanmem,1433","ComputerName:1433" | Find-DbaSqlInstance -Verbose -SQLInstance computer1\instance1 -ComputerName testcomputer -LocalInstances -DomainInstances -SQLInstanceFile C:\temp\sqlinstances.txt -BroadCastUDP
#>
[CmdletBinding(SupportsShouldProcess = $true)]
Param(
[Parameter(Mandatory = $false,
ValueFromPipeline = $false,
ValueFromPipelineByPropertyName = $false,
HelpMessage = 'Run all discovery options that dont require input.')]
[switch]$All,
[Parameter(Mandatory = $false,
ValueFromPipeline = $true,
ValueFromPipelineByPropertyName = $true,
HelpMessage = 'Computer name to target.')]
[string]$ComputerName,
[Parameter(Mandatory = $false,
ValueFromPipeline = $false,
ValueFromPipelineByPropertyName = $true,
HelpMessage = 'Instance to target.')]
[string]$SQLInstance,
[Parameter(Mandatory = $false,
ValueFromPipeline = $false,
ValueFromPipelineByPropertyName = $true,
HelpMessage = 'File containing list of instances or computers to target. One per line.')]
[string]$SQLInstanceFile,
[Parameter(Mandatory = $false,
ValueFromPipeline = $true,
ValueFromPipelineByPropertyName = $true,
HelpMessage = 'Target single IP address, IP Range, cidr, or IP with mask.')]
[string]$IpAddress,
[Parameter(Mandatory = $false,
ValueFromPipeline = $false,
ValueFromPipelineByPropertyName = $false,
HelpMessage = 'Set subnet mask for IP range.')]
[string]$Mask,
[Parameter(Mandatory = $false,
ValueFromPipeline = $false,
ValueFromPipelineByPropertyName = $false,
HelpMessage = 'Performs scan of Windows services related to SQL Server to identify potential SQL Server instances.')]
[switch]$LocalInstances,
[Parameter(Mandatory = $false,
ValueFromPipelineByPropertyName = $false,
HelpMessage = 'Performs UDP broadcast to identify SQL Servers instances within the currents broadcast domain.')]
[switch]$BroadCastUDP,
[Parameter(Mandatory = $false,
ValueFromPipeline = $false,
ValueFromPipelineByPropertyName = $false,
HelpMessage = 'Performs LDAP query for SQL Server related Service Principal Names to identify potential SQL Server instances.')]
[switch]$DomainInstances,
[Parameter(Mandatory = $false,
HelpMessage = 'Used to target a domain controller when perfoming SPN lookups with alternative credentials.')]
[string]$DomainController,
[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 = $false,
ValueFromPipelineByPropertyName = $false,
HelpMessage = 'Ping host to verify it is accessible.')]
[switch]$ScanPing,
[Parameter(Mandatory = $false,
ValueFromPipeline = $false,
ValueFromPipelineByPropertyName = $false,
HelpMessage = 'Performs TCP scan of port 1433 or provided port to identify potential SQL Server instances.')]
[switch]$ScanTCP,
[Parameter(Mandatory = $false,
ValueFromPipeline = $false,
ValueFromPipelineByPropertyName = $false,
HelpMessage = 'This is the port used for TCP scanning.')]
[int]$TCPPort = 1433,
[Parameter(Mandatory = $false,
ValueFromPipeline = $false,
ValueFromPipelineByPropertyName = $false,
HelpMessage = 'Performs UDP scan of port 1434 to identify potential SQL Server instances.')]
[switch]$ScanUDP,
[Parameter(Mandatory = $false,
ValueFromPipeline = $false,
ValueFromPipelineByPropertyName = $false,
HelpMessage = 'Timeout in seconds for UDP scans.')]
[int]$ScanUDPTimeout = 3,
[Parameter(Mandatory = $false,
ValueFromPipeline = $false,
ValueFromPipelineByPropertyName = $false,
HelpMessage = 'Attempt to log into the target instances with current or provided credentials.')]
[switch]$Scanlogin,
[Parameter(Mandatory = $false,
ValueFromPipeline = $false,
ValueFromPipelineByPropertyName = $false,
HelpMessage = 'Resolve IP of hostname.')]
[switch]$ResolveIP
)
DynamicParam { if ($sqlserver) { return Get-ParamSqlLogins -SqlServer $sqlserver -SqlCredential $SqlCredential } }
BEGIN
{
Write-Verbose "Creating list of targets."
# Create tblTargets data table
$tblTargets = New-Object -TypeName System.Data.DataTable
$tblTargets.Columns.Add("IPAddress") | Out-Null
$tblTargets.Columns.Add("ComputerName") | Out-Null
$tblTargets.Columns.Add("SQLInstance") | Out-Null
# Create tblScanList data table
$tblScanList = New-Object -TypeName System.Data.DataTable
$tblScanList.Columns.Add("IPAddress") | Out-Null
# Setup pipline Counters
$pipelineCountIp = 0
$pipelineCountComputer = 0
$pipelineCountInstance = 0
# ----------------------------------------------
# Get provided computername + add to tbl_targets
# ----------------------------------------------
if($ComputerName){
$tblTargets.Rows.Add("",$ComputerName,$ComputerName) | Out-Null
Write-Verbose " - 1 provided computer name added."
}
# ----------------------------------------------
# Get provided instance + add to tbl_targets
# ----------------------------------------------
if($SQLInstance){
$ComputerName = $SQLInstance.split('\')[0].split(',')[0]
$tblTargets.Rows.Add("",$ComputerName,$SQLInstance) | Out-Null
Write-Verbose " - 1 provided SQL Server instance added."
}
# ----------------------------------------------
# Get instances from provided file path + add to tbl_targets
# ----------------------------------------------
if($SQLInstanceFile){
# Test file path
if( -not (Test-Path $SQLInstanceFile))
{
Write-Verbose -InputObject 'SQLInstanceFile provided does not appear to be a valid file.'
break
}
# Grab instances from file
$fileCount = 0
Get-Content -Path $SQLInstanceFile |
ForEach-Object -Process {
$instanceFromFile = $_
$fileCount = $fileCount + 1
# Check if it's using the port with a colon
if($instanceFromFile.Split(':')[1])
{
$currentInstance = $instanceFromFile
$currentComputerName = $instanceFromFile.Split(':')[0]
}
# Check if it's using the port with a comma
if($instanceFromFile.Split(',')[1])
{
$currentInstance = $instanceFromFile
$currentComputerName = $instanceFromFile.Split(',')[0]
}
# Check if it's using the \
if($instanceFromFile.Split('\')[1])
{
$currentInstance = $instanceFromFile
$currentComputerName = $instanceFromFile.Split('\')[0]
}
# Check if it's just a computername
if(-not $currentInstance){
$currentInstance = $instanceFromFile
$currentComputerName = $instanceFromFile
}
# Add record
if($_ -ne '')
{
$tblTargets.Rows.Add("",$currentComputerName,$currentInstance) | Out-Null
}
}
Write-Verbose " - $fileCount IP addresses, computer names, or instances added from $SQLInstanceFile."
}
# ----------------------------------------------
# Get instances from local service configurations + add to tbl_targets
# ----------------------------------------------
$localCount = 0
if($LocalInstances){
# Get SQL related Windows services
$sqlServices = Get-WmiObject -Class win32_service |
Where-Object -FilterScript { $_.pathname -like '*Microsoft SQL Server*' } |
Select-Object -Property DisplayName, PathName, Name, StartName, State, SystemName, ProcessId
$SqlServices |
ForEach-Object -Process {
# Parse Instance
$displayName = [string]$_.DisplayName
$servState = [string]$_.State
# Set instance to computer name by default
$currentInstance = $env:COMPUTERNAME
# Check for named instance
$instanceCheck = ($displayName[1..$displayName.Length] | Where-Object {$_ -like '('}).count
if($instanceCheck) {
# Set name instance
$currentInstance = $env:COMPUTERNAME + '\' +$displayName.split('(')[1].split(')')[0]
# Set default instance
if($currentInstance -like '*\MSSQLSERVER')
{
$currentInstance = $env:COMPUTERNAME
}
}
# Filter out services that arent runn if needed
if($servState -notlike 'Running'){
return
}
# Add local instance to the tblTarget data table
if($_.PathName -like '*sqlservr.exe*'){
$localCount = $localCount + 1
$tblTargets.Rows.Add("",$env:COMPUTERNAME,$CurrentInstance) | Out-Null
}
}
Write-Verbose " - $localCount local running SQL Server instances added to the target list."
}
# -------------------------------------------------------
# Get IP targets from provided range + add to tbl_targets
# -------------------------------------------------------
# -------------------------------------------
# Function: Get-IPrange
# -------------------------------------------
# Author: BarryCWT
# Reference: https://gallery.technet.microsoft.com/scriptcenter/List-the-IP-addresses-in-a-60c5bb6b
function Get-IPrange
{
<#
.SYNOPSIS
Get the IP addresses in a range
.EXAMPLE
Get-IPrange -start 192.168.8.2 -end 192.168.8.20
.EXAMPLE
Get-IPrange -ip 192.168.8.2 -mask 255.255.255.0
.EXAMPLE
Get-IPrange -ip 192.168.8.3 -cidr 24
#>
param
(
[string]$start,
[string]$end,
[string]$ip,
[string]$mask,
[int]$cidr
)
function IP-toINT64 () {
param ($ip)
$octets = $ip.split(".")
return [int64]([int64]$octets[0]*16777216 +[int64]$octets[1]*65536 +[int64]$octets[2]*256 +[int64]$octets[3])
}
function INT64-toIP() {
param ([int64]$int)
return (([math]::truncate($int/16777216)).tostring()+"."+([math]::truncate(($int%16777216)/65536)).tostring()+"."+([math]::truncate(($int%65536)/256)).tostring()+"."+([math]::truncate($int%256)).tostring() )
}
if ($ip) {$ipaddr = [Net.IPAddress]::Parse($ip)}
if ($cidr) {$maskaddr = [Net.IPAddress]::Parse((INT64-toIP -int ([convert]::ToInt64(("1"*$cidr+"0"*(32-$cidr)),2)))) }
if ($mask) {$maskaddr = [Net.IPAddress]::Parse($mask)}
if ($ip) {$networkaddr = new-object net.ipaddress ($maskaddr.address -band $ipaddr.address)}
if ($ip) {$broadcastaddr = new-object net.ipaddress (([system.net.ipaddress]::parse("255.255.255.255").address -bxor $maskaddr.address -bor $networkaddr.address))}
if ($ip) {
$startaddr = IP-toINT64 -ip $networkaddr.ipaddresstostring
$endaddr = IP-toINT64 -ip $broadcastaddr.ipaddresstostring
} else {
$startaddr = IP-toINT64 -ip $start
$endaddr = IP-toINT64 -ip $end
}
for ($i = $startaddr; $i -le $endaddr; $i++)
{
INT64-toIP -int $i
}
}
# Define regex for an IP
# Reference: https://social.technet.microsoft.com/Forums/office/en-US/820fef9f-7a6c-41d9-b53e-98c96f3f7722/correct-regular-expression-for-ip-in-powershell?forum=winserverpowershell
$Pattern = "^([1-9]|[1-9][0-9]|1[0-9][0-9]|2[0-4][0-9]|25[0-5])(\.([0-9]|[1-9][0-9]|1[0-9][0-9]|2[0-4][0-9]|25[0-5])){3}$"
# Check for just IP
if($IpAddress -and -not ($Mask) -and -not $IpAddress.Split('/')[1] -and -not $IpAddress.Split('-')[1]){
# Validate IP
if($IpAddress -notmatch $Pattern){
Write-Verbose "$IpAddress is not a valid IP Address. Cancelling."
return
}
# Add IP to target list
#$tblTargets.Rows.Add($IpAddress,"","") | Out-Null
$tblScanList.Rows.Add($IpAddress) | Out-Null
Write-Verbose " - 1 provided IP address added - $IpAddress."
}
# Check for CIDR
$cidrIpCount = 0
if($IpAddress.Split('/')[1]){
Write-Verbose " o Adding IP addresses based on provided network $IpAddress."
$cidrIp = $IpAddress.Split('/')[0]
$cidrNet = $IpAddress.Split('/')[1]
# Validate IP
if($cidrIp -notmatch $Pattern){
Write-Verbose "$cidrIp is not a valid IP Address. Cancelling."
return
}
Get-IPrange -ip $cidrIp -cidr $cidrNet |
ForEach-Object {
$cidrIpCount = $cidrIpCount + 1
#$tblTargets.Rows.Add($_,"","") | Out-Null
$tblScanList.Rows.Add($_) | Out-Null
}
Write-Verbose " - $cidrIpCount IP addresses added to the scan list based on provided network $IpAddress."
}
# Check for Range
$rangeIpCount = 0
if($IpAddress.Split('-')[1]){
# Get start and stop IP addresses
$StartIP = $IpAddress.Split('-')[0].Trim()
$EndIp = $IpAddress.Split('-')[1].Trim()
# Validate Start IP
if($StartIp -notmatch $Pattern){
Write-Verbose "$StartIP is not a valid IP Address. Cancelling."
return
}
# Validate Start IP
if($EndIp -notmatch $Pattern){
Write-Verbose "$StartIP is not a valid IP Address. Cancelling."
return
}
# Add record to target list
Get-IPrange -start $StartIp -end $EndIp |
ForEach-Object {
$rangeIpCount = $rangeIpCount + 1
#$tblTargets.Rows.Add($_,"","") | Out-Null
$tblScanList.Rows.Add($_) | Out-Null
}
Write-Verbose " - $rangeIpCount IP addresses added to the scan list based on provided network range $StartIP - $EndIP."
}
# Check for Mask
$maskIpCount = 0
if($IpAddress -and $Mask){
#validate startip
Get-IPrange -ip $IpAddress -mask $Mask |
ForEach-Object {
$maskIpCount = $maskIpCount + 1
#$tblTargets.Rows.Add($_,"","") | Out-Null
$tblScanList.Rows.Add($IpAddress) | Out-Null
}
Write-Verbose " - $maskIpCount IP addresses added the the scan list based on provided IP address $IpAddress and Mask $Mask."
}
# ----------------------------------------------
# Get from broadcast ping
# ----------------------------------------------
if($BroadCastUDP){
Write-Verbose " o Attempting to identify SQL Server instances via broadcast ping."
try {
# Discover instances
$currentInstances = [System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()
# Add results to modified data table
$broadcastCount = 0
$currentInstances |
ForEach-Object {
[string]$currentInstance = $_.InstanceName
if($currentInstance){
[string]$instanceName = $_.Servername + "\" + $_.InstanceName
}else{
[string]$instanceName = $_.Servername
}
[string]$ComputerName = $_.Servername
$broadcastCount = $broadcastCount + 1
# Add to table
$tblTargets.Rows.Add("",$ComputerName, $InstanceName) | Out-Null
}
}
catch{
# Show error message
$ErrorMessage = $_.Exception.Message
Write-Output -Message " Operation Failed."
Write-Output -Message " Error: $ErrorMessage"
}
Write-Verbose " - $broadcastCount instance added based on broadcast ping responses."
}
# ----------------------------------------------
# Get instance targets from SPNs in Active Directory via LDAP
# ----------------------------------------------
if($DomainInstances){
# -------------------------------------------
# Function: Get-SQLInstanceDomain
# -------------------------------------------
function Local:Get-DomainObject
{
[CmdletBinding()]
Param(
[Parameter(Mandatory = $false,
HelpMessage = 'Domain user to authenticate with domain\user.')]
[string]$Username,
[Parameter(Mandatory = $false,
HelpMessage = 'Domain password to authenticate with domain\user.')]
[string]$Password,
[Parameter(Mandatory = $false,
HelpMessage = 'Credentials to use when connecting to a Domain Controller.')]
[System.Management.Automation.PSCredential]
[System.Management.Automation.Credential()]$Credential = [System.Management.Automation.PSCredential]::Empty,
[Parameter(Mandatory = $false,
HelpMessage = 'Domain controller for Domain and Site that you want to query against.')]
[string]$DomainController,
[Parameter(Mandatory = $false,
HelpMessage = 'LDAP Filter.')]
[string]$LdapFilter = '',
[Parameter(Mandatory = $false,
HelpMessage = 'LDAP path.')]
[string]$LdapPath,
[Parameter(Mandatory = $false,
HelpMessage = 'Maximum number of Objects to pull from AD, limit is 1,000 .')]
[int]$Limit = 1000,
[Parameter(Mandatory = $false,
HelpMessage = 'scope of a search as either a base, one-level, or subtree search, default is subtree.')]
[ValidateSet('Subtree','OneLevel','Base')]
[string]$SearchScope = 'Subtree'
)
Begin
{
# Create PS Credential object
if($Username -and $Password)
{
$secpass = ConvertTo-SecureString $Password -AsPlainText -Force
$Credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList ($Username, $secpass)
}
# Create Create the connection to LDAP
if ($DomainController)
{
# Verify credentials were provided
if(-not $Username){
Write-Output "A username and password must be provided when setting a specific domain controller."
Break
}
# Test credentials and grab domain
try {
$objDomain = (New-Object -TypeName System.DirectoryServices.DirectoryEntry -ArgumentList "LDAP://$DomainController", $Credential.UserName, $Credential.GetNetworkCredential().Password).distinguishedname
}catch{
Write-Output "Authentication failed."
}
# add ldap path
if($LdapPath)
{
$LdapPath = '/'+$LdapPath+','+$objDomain
$objDomainPath = New-Object -TypeName System.DirectoryServices.DirectoryEntry -ArgumentList "LDAP://$DomainController$LdapPath", $Credential.UserName, $Credential.GetNetworkCredential().Password
}
else
{
$objDomainPath = New-Object -TypeName System.DirectoryServices.DirectoryEntry -ArgumentList "LDAP://$DomainController", $Credential.UserName, $Credential.GetNetworkCredential().Password
}
$objSearcher = New-Object -TypeName System.DirectoryServices.DirectorySearcher -ArgumentList $objDomainPath
}
else
{
$objDomain = ([ADSI]'').distinguishedName
# add ldap path
if($LdapPath)
{
$LdapPath = $LdapPath+','+$objDomain
$objDomainPath = [ADSI]"LDAP://$LdapPath"
}
else
{
$objDomainPath = [ADSI]''
}
$objSearcher = New-Object -TypeName System.DirectoryServices.DirectorySearcher -ArgumentList $objDomainPath
}
# Setup LDAP filter
$objSearcher.PageSize = $Limit
$objSearcher.Filter = $LdapFilter
$objSearcher.SearchScope = 'Subtree'
}
Process
{
try
{
# Return object
$objSearcher.FindAll() | ForEach-Object -Process {
$_
}
}
catch
{
"Error was $_"
$line = $_.InvocationInfo.ScriptLineNumber
"Error was in Line $line"
}
}
End
{
}
}
# -------------------------------------------
# Function: Get-DomainSpn
# -------------------------------------------
function Local:Get-DomainSpn
{
[CmdletBinding()]
Param(
[Parameter(Mandatory = $false,
HelpMessage = 'Domain user to authenticate with domain\user.')]
[string]$Username,
[Parameter(Mandatory = $false,
HelpMessage = 'Domain password to authenticate with domain\user.')]
[string]$Password,
[Parameter(Mandatory = $false,
HelpMessage = 'Credentials to use when connecting to a Domain Controller.')]
[System.Management.Automation.PSCredential]
[System.Management.Automation.Credential()]$Credential = [System.Management.Automation.PSCredential]::Empty,
[Parameter(Mandatory = $false,
HelpMessage = 'Domain controller for Domain and Site that you want to query against.')]
[string]$DomainController,
[Parameter(Mandatory = $false,
ValueFromPipelineByPropertyName = $true,
HelpMessage = 'Computer name to filter for.')]
[string]$ComputerName,
[Parameter(Mandatory = $false,
ValueFromPipeline = $true,
ValueFromPipelineByPropertyName = $true,
HelpMessage = 'Domain account to filter for.')]
[string]$DomainAccount,
[Parameter(Mandatory = $false,
ValueFromPipelineByPropertyName = $true,
HelpMessage = 'SPN service code.')]
[string]$SpnService,
[Parameter(Mandatory = $false,
HelpMessage = 'Suppress verbose errors. Used when function is wrapped.')]
[switch]$SuppressVerbose
)
Begin
{
if(-not $SuppressVerbose)
{
Write-Verbose -Message ' o Getting domain SPNs...'
}
# Setup table to store results
$TableDomainSpn = New-Object -TypeName System.Data.DataTable
$null = $TableDomainSpn.Columns.Add('UserSid')
$null = $TableDomainSpn.Columns.Add('User')
$null = $TableDomainSpn.Columns.Add('UserCn')
$null = $TableDomainSpn.Columns.Add('Service')
$null = $TableDomainSpn.Columns.Add('ComputerName')
$null = $TableDomainSpn.Columns.Add('Spn')
$null = $TableDomainSpn.Columns.Add('LastLogon')
$null = $TableDomainSpn.Columns.Add('Description')
$TableDomainSpn.Clear()
}
Process
{
try
{
# Setup LDAP filter
$SpnFilter = ''
if($DomainAccount)
{
$SpnFilter = "(objectcategory=person)(SamAccountName=$DomainAccount)"
}
if($ComputerName)
{
$ComputerSearch = "$ComputerName`$"
$SpnFilter = "(objectcategory=computer)(SamAccountName=$ComputerSearch)"
}
# Get results
$SpnResults = Get-DomainObject -LdapFilter "(&(servicePrincipalName=$SpnService*)$SpnFilter)" -DomainController $DomainController -Username $Username -Password $Password -Credential $Credential
# Parse results
$SpnResults | ForEach-Object -Process {
[string]$SidBytes = [byte[]]"$($_.Properties.objectsid)".split(' ')
[string]$SidString = $SidBytes -replace ' ', ''
#$Spn = $_.properties.serviceprincipalname[0].split(',')
#foreach ($item in $Spn)
foreach ($item in $($_.properties.serviceprincipalname))
{
# Parse SPNs
$SpnServer = $item.split('/')[1].split(':')[0].split(' ')[0]
$SpnService = $item.split('/')[0]
# Parse last logon
if ($_.properties.lastlogon)
{
$LastLogon = [datetime]::FromFileTime([string]$_.properties.lastlogon).ToString('g')
}
else
{
$LastLogon = ''
}
# Add results to table
$null = $TableDomainSpn.Rows.Add(
[string]$SidString,
[string]$_.properties.samaccountname,
[string]$_.properties.cn,
[string]$SpnService,
[string]$SpnServer,
[string]$item,
$LastLogon,
[string]$_.properties.description
)
}
}
}
catch
{
"Error was $_"
$line = $_.InvocationInfo.ScriptLineNumber
"Error was in Line $line"
}
}
End
{
# Check for results
if ($TableDomainSpn.Rows.Count -gt 0)
{
$TableDomainSpnCount = $TableDomainSpn.Rows.Count
if(-not $SuppressVerbose)
{
Write-Verbose -Message " - $TableDomainSpnCount SPNs found on servers that matched search criteria."
}
Return $TableDomainSpn
}
}
}
# -------------------------------------------
# Function: Get-SQLInstanceDomain
# -------------------------------------------
Function Local:Get-SQLInstanceDomain
{
[CmdletBinding()]
Param(
[Parameter(Mandatory = $false,
HelpMessage = 'Domain user to authenticate with domain\user.')]
[string]$Username,
[Parameter(Mandatory = $false,
HelpMessage = 'Domain password to authenticate with domain\user.')]
[string]$Password,
[Parameter(Mandatory = $false,
HelpMessage = 'Credentials to use when connecting to a Domain Controller.')]
[System.Management.Automation.PSCredential]
[System.Management.Automation.Credential()]$Credential = [System.Management.Automation.PSCredential]::Empty,
[Parameter(Mandatory = $false,
HelpMessage = 'Domain controller for Domain and Site that you want to query against.')]
[string]$DomainController,
[Parameter(Mandatory = $false,
ValueFromPipeline = $true,
ValueFromPipelineByPropertyName = $true,
HelpMessage = 'Computer name to filter for.')]
[string]$ComputerName,
[Parameter(Mandatory = $false,
ValueFromPipeline = $true,
ValueFromPipelineByPropertyName = $true,
HelpMessage = 'Domain account to filter for.')]
[string]$DomainAccount,
[Parameter(Mandatory = $false,
ValueFromPipelineByPropertyName = $true,
HelpMessage = 'Performs UDP scan of servers managing SQL Server clusters.')]
[switch]$CheckMgmt,
[Parameter(Mandatory = $false,
ValueFromPipelineByPropertyName = $true,
HelpMessage = 'Timeout in seconds for UDP scans of management servers. Longer timeout = more accurate.')]
[int]$UDPTimeOut = 3
)
Begin
{
# Table for SPN output
$TblSQLServerSpns = New-Object -TypeName System.Data.DataTable
$null = $TblSQLServerSpns.Columns.Add('ComputerName')
$null = $TblSQLServerSpns.Columns.Add('Instance')
$null = $TblSQLServerSpns.Columns.Add('DomainAccountSid')
$null = $TblSQLServerSpns.Columns.Add('DomainAccount')
$null = $TblSQLServerSpns.Columns.Add('DomainAccountCn')
$null = $TblSQLServerSpns.Columns.Add('Service')
$null = $TblSQLServerSpns.Columns.Add('Spn')
$null = $TblSQLServerSpns.Columns.Add('LastLogon')
$null = $TblSQLServerSpns.Columns.Add('Description')
# Table for UDP scan results of management servers
}
Process
{
# Get list of SPNs for SQL Servers
Write-Verbose -Message ' o Grabbing SPNs from the domain for SQL Servers (MSSQL*)...'
$TblSQLServers = Get-DomainSpn -DomainController $DomainController -Username $Username -Password $Password -Credential $Credential -ComputerName $ComputerName -DomainAccount $DomainAccount -SpnService 'MSSQL*' -SuppressVerbose | Where-Object -FilterScript {
$_.service -like 'MSSQL*'
}
Write-Verbose -Message ' - Parsing SQL Server instances from SPNs...'
# Add column containing sql server instance
$TblSQLServers |
ForEach-Object -Process {
# Parse SQL Server instance
$Spn = $_.Spn
$Instance = $Spn.split('/')[1].split(':')[1]
# Check if the instance is a number and use the relevent delim
$Value = 0
if([int32]::TryParse($Instance,[ref]$Value))
{
$SpnServerInstance = $Spn -replace ':', ','
}
else
{
$SpnServerInstance = $Spn -replace ':', '\'
}
$SpnServerInstance = $SpnServerInstance -replace 'MSSQLSvc/', ''
# Add SQL Server spn to table
$null = $TblSQLServerSpns.Rows.Add(
[string]$_.ComputerName,
[string]$SpnServerInstance,
$_.UserSid,
[string]$_.User,
[string]$_.Usercn,
[string]$_.Service,
[string]$_.Spn,
$_.LastLogon,
[string]$_.Description)
}
}
End
{
$InstanceCount = $TblSQLServerSpns.rows.count
Write-Verbose -Message " - $InstanceCount instances were added based on SPNs."
$TblSQLServerSpns
}
}
# Get SPNs
Get-SQLInstanceDomain |
ForEach-Object {
# Grab variables
$currentInstance = $_.ComputerName
$currentComputerName = $_.Instance
# Add to table
$tblTargets.Rows.Add("",$currentInstance , $currentComputerName) | Out-Null
}
}
Write-Verbose " o Grabbing items from the pipeline."
}
PROCESS
{
# ---------------------------------------------------------------------
# Get IP Addresses, Computer Names, and SQL Instances from the Pipeline
# ---------------------------------------------------------------------
# Check if pipeline item is an IP address
$Pattern = "^([1-9]|[1-9][0-9]|1[0-9][0-9]|2[0-4][0-9]|25[0-5])(\.([0-9]|[1-9][0-9]|1[0-9][0-9]|2[0-4][0-9]|25[0-5])){3}$"
if($_ -match $Pattern -and $_ -notlike ""){
Write-Verbose "IP"
$currentIpAddress = $_
$currentInstance = ''
$currentComputerName = ''
$pipelineCountIp = $pipelineIp + 1
$tblScanList.Rows.Add($currentIpAddress) | Out-Null
}
# Check if pipeline item is an instance using a colon
if($_ -like "*:*"){
Write-Verbose "instance :"
$currentIpAddress = ""
$currentInstance = $_
$currentComputerName = $_.Split(':')[0]
$pipelineCountInstance = $pipelineCountInstance + 1
$tblTargets.rows.Add($currentIpAddress,$currentComputerName,$currentInstance) | Out-Null
}
# Check if pipeline item is an instance using a comma
if($_ -like "*,*"){
Write-Verbose "instance ,"
$currentIpAddress = ''
$currentInstance = $_
$currentComputerName = $_.Split(',')[0]
$pipelineCountInstance = $pipelineCountInstance + 1
$tblTargets.rows.Add($currentIpAddress,$currentComputerName,$currentInstance) | Out-Null
}
# Check if pipeline item is an instance using a slash
if($_ -like "*\*"){
Write-Verbose "isntance \"
$currentIpAddress = ''
$currentInstance = $_
$currentComputerName = $_.Split('\')[0]
$pipelineCountInstance = $pipelineCountInstance + 1
$tblTargets.rows.Add($currentIpAddress,$currentComputerName,$currentInstance) | Out-Null
}
# Check if pipeline item is computer name
if($_ -notlike "*\*" -and $_ -notlike "*,*" -and $_ -notlike "*:*" -and $_ -notlike "" -and $_ -notmatch $Pattern){
Write-Verbose "computer"
$currentIpAddress = ''
$currentInstance = $_
$currentComputerName = $_
$pipelineCountComputer = $pipelineCountComputer + 1
$tblTargets.rows.Add($currentIpAddress,$currentComputerName,$currentInstance) | Out-Null
}
}
END
{
# ----------------------------------------------
# Add items from the pipeline to the target list
# ----------------------------------------------
if ( ($pipelineCountIp ) -or ($pipelineCountComputer) -or ($pipelineCountInstance)){
Write-Verbose " - $pipelineCountIp IP addresses added to scan list from pipeline."
Write-Verbose " - $pipelineCountComputer computer names added to target list from the pipeline."
Write-Verbose " - $pipelineCountInstance instances added to target list from from the pipeline."
}else{
Write-Verbose " - No pipeline items provided."
}
# --------------------------------------------------------------------------
# Add scan tblScanList to tblTarget list if no discovery options are enabled
# --------------------------------------------------------------------------
if( -not $ScanPing -and -not $ScanUDP -and -not $ScanTCP){
$tblScanList |
ForEach-Object {
$tblTargets.Rows.Add($_.IpAddress,$_.IpAddress,$_.IpAddress) | Out-Null
}
}
# ----------------------------------------------
# IP Resolution
# ----------------------------------------------
# Pending...
# threaded version
# target : tblTargets
<#
$tblTargets | Select-Object ComputerName -Unique -ExpandProperty ComputerName |
ForEach-Object{
try {
[System.Net.Dns]::GetHostAddresses($_)
}
catch
{}
}
#>
# ----------------------------------------------
# Hostname Resolution
# ----------------------------------------------
# pending...
# threaded version
# target : tblScanList
# ----------------------------------------------
# Ping Scan - tblScanList
# ----------------------------------------------
# pending...
# Super slow and needs to be updated
# Need to determine if tblTarget, tblScanList, or both should be included in scan
# Note: Name resolutions doesn't always work quite right
if($ScanPing){
$MyScriptBlock = {
$pingIpAddress = $_.IPAddress
Test-Connection -Count 1 $pingIpAddress
}
$pingableIpAddresses = $tblScanList | Invoke-Parallel -ScriptBlock $MyScriptBlock -Throttle $Threads -RunspaceTimeout 2 -Quiet -ErrorAction SilentlyContinue | Select-Object IPv4Address -Unique
#$pingableIpAddresses
}
# ----------------------------------------------
# UDP Port Scan - tblScanList
# ----------------------------------------------
# Note: Need to add threading.
# If the server responds correctly to the UDP request the ip address is added to the target list
if($ScanUDP){
# ----------------------------------
# Author: Eric Gruber
# Note: Pipeline and timeout mods by Scott Sutherland
# ----------------------------------
function Get-SQLInstanceScanUDP
{
[CmdletBinding()]
param(
[Parameter(Mandatory = $true,
ValueFromPipeline = $true,
ValueFromPipelineByPropertyName = $true,
HelpMessage = 'Computer name or IP address to enumerate SQL Instance from.')]
[string]$ComputerName,
[Parameter(Mandatory = $false,
ValueFromPipelineByPropertyName = $true,
HelpMessage = 'Timeout in seconds. Longer timeout = more accurate.')]
[int]$UDPTimeOut = 2,
[Parameter(Mandatory = $false,
HelpMessage = 'Suppress verbose errors. Used when function is wrapped.')]
[switch]$SuppressVerbose
)
Begin
{
# Setup data table for results
$TableResults = New-Object -TypeName system.Data.DataTable -ArgumentList 'Table'
$null = $TableResults.columns.add('ComputerName')
$null = $TableResults.columns.add('SQLInstance')
$null = $TableResults.columns.add('InstanceName')
$null = $TableResults.columns.add('ServerIP')
$null = $TableResults.columns.add('TCPPort')
$null = $TableResults.columns.add('BaseVersion')
$null = $TableResults.columns.add('IsClustered')
}
Process
{
if(-not $SuppressVerbose)
{
#Write-Verbose -Message " - $ComputerName - UDP Scan Start."
}
# Verify server name isn't empty
if ($ComputerName -ne '')
{
# Try to enumerate SQL Server instances from remote system
try
{
# Resolve IP
$IPAddress = [System.Net.Dns]::GetHostAddresses($ComputerName)
# Create UDP client object
$UDPClient = New-Object -TypeName System.Net.Sockets.Udpclient
# Attempt to connect to system
$UDPTimeOutMilsec = $UDPTimeOut * 1000
$UDPClient.client.ReceiveTimeout = $UDPTimeOutMilsec
$UDPClient.Connect($ComputerName,0x59a)
$UDPPacket = 0x03
# Send request to system
$UDPEndpoint = New-Object -TypeName System.Net.Ipendpoint -ArgumentList ([System.Net.Ipaddress]::Any, 0)
$UDPClient.Client.Blocking = $true
[void]$UDPClient.Send($UDPPacket,$UDPPacket.Length)
# Process response from system
$BytesRecived = $UDPClient.Receive([ref]$UDPEndpoint)
$Response = [System.Text.Encoding]::ASCII.GetString($BytesRecived).split(';')
$values = @{}
for($i = 0; $i -le $Response.length; $i++)
{
if(![string]::IsNullOrEmpty($Response[$i]))
{
$values.Add(($Response[$i].ToLower() -replace '[\W]', ''),$Response[$i+1])
}
else
{
if(![string]::IsNullOrEmpty($values.'tcp'))
{
if(-not $SuppressVerbose)
{
$DiscoveredInstance = "$ComputerName\"+$values.'instancename'
$DiscoveredTcpPort = $values.tcp
Write-Verbose -Message " - $ComputerName - SQL instance found! - $DiscoveredInstance (Port: $DiscoveredTcpPort)"
}
# Add SQL Server instance info to results table
$null = $TableResults.rows.Add(
[string]$ComputerName,
[string]"$ComputerName\"+$values.'instancename',
[string]$values.'instancename',
[string]$IPAddress,
[string]$values.'tcp',
[string]$values.'version',
[string]$values.'isclustered')
$values = @{}
}
}
}
# Close connection
$UDPClient.Close()
}
catch
{
#"Error was $_"
#$line = $_.InvocationInfo.ScriptLineNumber
#"Error was in Line $line"
# Close connection
# $UDPClient.Close()
}
}
if(-not $SuppressVerbose)
{
#Write-Verbose -Message " - $ComputerName - UDP Scan Complete."
}
}
End
{
# Return Results
$TableResults
}
}
Write-Verbose -Message " o Performing UDP scan of provided IP addresses."
$tblScanList | Select @{Name="ComputerName";Expression={$_."IpAddress"}} | Select ComputerName -ExpandProperty ComputerName | Get-SQLInstanceScanUDP -Verbose |
ForEach-Object {
$tblTargets.Rows.Add($_.ServerIP,$_.ComputerName,$_.SQLInstance) | Out-Null
}
}
# ----------------------------------------------
# TCP Port Scan - tblScanList
# ----------------------------------------------
# Work in progress
# Note: This verifies that the port is open, but nothing else.
# If the tcp port is open then the ip address is added to the target list
if($ScanTCP){
$MyScriptBlock = {
$tcpIpAddress = $_.IPAddress
Write-Verbose " - $tcpIpAddress - TCP Scan Start."
$socket = New-Object System.Net.Sockets.TcpClient($tcpIpAddress,$TCPPort)
if($socket.Connected){
Write-Verbose " - $tcpIpAddress - Port $TCPPort is open!"
$tcpIpAddress
}
$socket.Dispose()
Write-Verbose " - $tcpIpAddress - TCP Scan Complete."
}
Write-Verbose -Message " o Performing TCP scan of port $TCPPort on provided IP addresses."
$tblScanList | Invoke-Parallel -ImportVariables -ScriptBlock $MyScriptBlock -Throttle $Threads -RunspaceTimeout 2 -Quiet -ErrorAction SilentlyContinue |
ForEach-Object {
$tblTargets.Rows.Add($_,$_,$_) | Out-Null
}
}
# ----------------------------------------------
# Login Test - tblTargets
# ----------------------------------------------
# Attempt to login as current Windows user or a provide login
# Needs to be threaded
# Error handling may be to verbose.
if($Scanlogin){
# ----------------------------------
# Get-SQLConnectionObject
# ----------------------------------
# Author: Scott Sutherland
Function Get-SQLConnectionObject
{
[CmdletBinding()]
Param(
[Parameter(Mandatory = $false,
HelpMessage = 'SQL Server or domain account to authenticate with.')]
[string]$Username,
[Parameter(Mandatory = $false,
HelpMessage = 'SQL Server or domain account password to authenticate with.')]
[string]$Password,
[Parameter(Mandatory = $false,
HelpMessage = 'Windows credentials.')]
[System.Management.Automation.PSCredential]
[System.Management.Automation.Credential()]$Credential = [System.Management.Automation.PSCredential]::Empty,
[Parameter(Mandatory = $false,
ValueFromPipelineByPropertyName = $true,
HelpMessage = 'SQL Server instance to connection to.')]
[string]$Instance,
[Parameter(Mandatory = $false,
HelpMessage = 'Dedicated Administrator Connection (DAC).')]
[Switch]$DAC,
[Parameter(Mandatory = $false,
HelpMessage = 'Default database to connect to.')]
[String]$Database,
[Parameter(Mandatory = $false,
HelpMessage = 'Connection timeout.')]
[string]$TimeOut = 1
)
Begin
{
# Setup DAC string
if($DAC)
{
$DacConn = 'ADMIN:'
}
else
{
$DacConn = ''
}
# Set database filter
if(-not $Database)
{
$Database = 'Master'
}
}
Process
{
# Check for instance
if ( -not $Instance)
{
$Instance = $env:COMPUTERNAME
}
# Create connection object
$Connection = New-Object -TypeName System.Data.SqlClient.SqlConnection
# Set authentcation type - current windows user
if(-not $Username){
# Set authentication type
$AuthenticationType = "Current Windows Credentials"
# Set connection string
$Connection.ConnectionString = "Server=$DacConn$Instance;Database=$Database;Integrated Security=SSPI;Connection Timeout=1"
}
# Set authentcation type - provided windows user
if ($username -like "*\*"){
$AuthenticationType = "Provided Windows Credentials"
# Setup connection string
$Connection.ConnectionString = "Server=$DacConn$Instance;Database=$Database;Integrated Security=SSPI;uid=$Username;pwd=$Password;Connection Timeout=$TimeOut"
}
# Set authentcation type - provided sql login
if (($username) -and ($username -notlike "*\*")){
# Set authentication type
$AuthenticationType = "Provided SQL Login"
# Setup connection string
$Connection.ConnectionString = "Server=$DacConn$Instance;Database=$Database;User ID=$Username;Password=$Password;Connection Timeout=$TimeOut"
}
# Return the connection object
return $Connection
}
End
{
}
}
# ----------------------------------
# Get-SQLConnectionTest
# ----------------------------------
# Author: Scott Sutherland
Function Get-SQLConnectionTest
{
<#
.SYNOPSIS
Tests if the current Windows account or provided SQL Server login can log into an SQL Server.
.PARAMETER Username
SQL Server or domain account to authenticate with.
.PARAMETER Password
SQL Server or domain account password to authenticate with.
.PARAMETER Credential
SQL Server credential.
.PARAMETER Instance
SQL Server instance to connection to.
.PARAMETER DAC
Connect using Dedicated Admin Connection.
.PARAMETER Database
Default database to connect to.
.PARAMETER TimeOut
Connection time out.
.PARAMETER SuppressVerbose
Suppress verbose errors. Used when function is wrapped.
.EXAMPLE
PS C:\> Get-SQLConnectionTest -Verbose -Instance "SQLSERVER1.domain.com\SQLExpress"
.EXAMPLE
PS C:\> Get-SQLConnectionTest -Verbose -Instance "SQLSERVER1.domain.com,1433"
.EXAMPLE
PS C:\> Get-SQLInstanceDomain | Get-SQLConnectionTest -Verbose
#>
[CmdletBinding()]
Param(
[Parameter(Mandatory = $false,
HelpMessage = 'SQL Server or domain account to authenticate with.')]
[string]$Username,
[Parameter(Mandatory = $false,
HelpMessage = 'SQL Server or domain account password to authenticate with.')]
[string]$Password,
[Parameter(Mandatory = $false,
HelpMessage = 'Windows credentials.')]
[System.Management.Automation.PSCredential]
[System.Management.Automation.Credential()]$Credential = [System.Management.Automation.PSCredential]::Empty,
[Parameter(Mandatory = $false,
ValueFromPipeline = $true,
ValueFromPipelineByPropertyName = $true,
HelpMessage = 'SQL Server instance to connection to.')]
[string]$Instance,
[Parameter(Mandatory = $false,
HelpMessage = 'Connect using Dedicated Admin Connection.')]
[Switch]$DAC,
[Parameter(Mandatory = $false,
HelpMessage = 'Default database to connect to.')]
[String]$Database,
[Parameter(Mandatory = $false,
HelpMessage = 'Connection timeout.')]
[string]$TimeOut,
[Parameter(Mandatory = $false,
HelpMessage = 'Suppress verbose errors. Used when function is wrapped.')]
[switch]$SuppressVerbose
)
Begin
{
# Setup data table for output
$TblResults = New-Object -TypeName System.Data.DataTable
$null = $TblResults.Columns.Add('ComputerName')
$null = $TblResults.Columns.Add('Instance')
$null = $TblResults.Columns.Add('Status')
}
Process
{
# Parse ComputerName from provided instance
If ($Instance)
{
$ComputerName = $Instance.split('\')[0].split(',')[0]
}
else
{
$ComputerName = $env:COMPUTERNAME
}
# Default connection to local default instance
if(-not $Instance)
{
$Instance = $env:COMPUTERNAME
}
# Setup DAC string
if($DAC)
{
# Create connection object
$Connection = Get-SQLConnectionObject -Instance $Instance -Username $Username -Password $Password -Credential $Credential -DAC -TimeOut $TimeOut -Database $Database
}
else
{
# Create connection object
$Connection = Get-SQLConnectionObject -Instance $Instance -Username $Username -Password $Password -Credential $Credential -TimeOut $TimeOut -Database $Database
}
# Attempt connection
try
{
# Open connection
$Connection.Open()
if(-not $SuppressVerbose)
{
Write-Verbose -Message " - $Instance : Connection Success."
}
# Add record
$null = $TblResults.Rows.Add("$ComputerName","$Instance",'Accessible')
# Close connection
$Connection.Close()
# Dispose connection
$Connection.Dispose()
}
catch
{
# Connection failed
if(-not $SuppressVerbose)
{
$ErrorMessage = $_.Exception.Message
Write-Verbose -Message " - $Instance - Connection Failed."
#Write-Verbose -Message " - Error: $ErrorMessage"
}
# Add record
$null = $TblResults.Rows.Add("$ComputerName","$Instance",'Not Accessible')
}
}
End
{
# Return Results
$TblResults
}
}
# Perform login attempts
if($Username){
Write-Verbose -Message " o Attempting to login into discovered SQL instances as $Username."
}else{
Write-Verbose -Message " o Attempting to login into discovered SQL instances as $env:USERNAME."
}
$tblTargets | Select-Object SQLInstance -ExpandProperty SQLInstance | Get-SQLConnectionTest -Verbose -Username $Username -Password $Password
}
# ----------------------------------------------
# Display Targets - tblTargets
# ----------------------------------------------
if( -not $Scanlogin){
$tblTargets | Sort-Object IPAddress,ComputerName,SQLInstance -Unique
}
}
}
function Invoke-Parallel
{
<#
.SYNOPSIS
Function to control parallel processing using runspaces
.DESCRIPTION
Function to control parallel processing using runspaces
Note that each runspace will not have access to variables and commands loaded in your session or in other runspaces by default.
This behaviour can be changed with parameters.
.PARAMETER ScriptFile
File to run against all input objects. Must include parameter to take in the input object, or use $args. Optionally, include parameter to take in parameter. Example: C:\script.ps1
.PARAMETER ScriptBlock
Scriptblock to run against all computers.
You may use $Using:<Variable> language in PowerShell 3 and later.
The parameter block is added for you, allowing behaviour similar to foreach-object:
Refer to the input object as $_.
Refer to the parameter parameter as $parameter
.PARAMETER InputObject
Run script against these specified objects.
.PARAMETER Parameter
This object is passed to every script block. You can use it to pass information to the script block; for example, the path to a logging folder
Reference this object as $parameter if using the scriptblock parameterset.
.PARAMETER ImportVariables
If specified, get user session variables and add them to the initial session state
.PARAMETER ImportModules
If specified, get loaded modules and pssnapins, add them to the initial session state
.PARAMETER Throttle
Maximum number of threads to run at a single time.
.PARAMETER SleepTimer
Milliseconds to sleep after checking for completed runspaces and in a few other spots. I would not recommend dropping below 200 or increasing above 500
.PARAMETER RunspaceTimeout
Maximum time in seconds a single thread can run. If execution of your code takes longer than this, it is disposed. Default: 0 (seconds)
WARNING: Using this parameter requires that maxQueue be set to throttle (it will be by default) for accurate timing. Details here:
http://gallery.technet.microsoft.com/Run-Parallel-Parallel-377fd430
.PARAMETER NoCloseOnTimeout
Do not dispose of timed out tasks or attempt to close the runspace if threads have timed out. This will prevent the script from hanging in certain situations where threads become non-responsive, at the expense of leaking memory within the PowerShell host.
.PARAMETER MaxQueue
Maximum number of powershell instances to add to runspace pool. If this is higher than $throttle, $timeout will be inaccurate
If this is equal or less than throttle, there will be a performance impact
The default value is $throttle times 3, if $runspaceTimeout is not specified
The default value is $throttle, if $runspaceTimeout is specified
.PARAMETER LogFile
Path to a file where we can log results, including run time for each thread, whether it completes, completes with errors, or times out.
.PARAMETER Quiet
Disable progress bar.
.EXAMPLE
Each example uses Test-ForPacs.ps1 which includes the following code:
param($computer)
if(test-connection $computer -count 1 -quiet -BufferSize 16){
$object = [pscustomobject] @{
Computer=$computer;
Available=1;
Kodak=$(
if((test-path "\\$computer\c$\users\public\desktop\Kodak Direct View Pacs.url") -or (test-path "\\$computer\c$\documents and settings\all users
\desktop\Kodak Direct View Pacs.url") ){"1"}else{"0"}
)
}
}
else{
$object = [pscustomobject] @{
Computer=$computer;
Available=0;
Kodak="NA"
}
}
$object
.EXAMPLE
Invoke-Parallel -scriptfile C:\public\Test-ForPacs.ps1 -inputobject $(get-content C:\pcs.txt) -runspaceTimeout 10 -throttle 10
Pulls list of PCs from C:\pcs.txt,
Runs Test-ForPacs against each
If any query takes longer than 10 seconds, it is disposed
Only run 10 threads at a time
.EXAMPLE
Invoke-Parallel -scriptfile C:\public\Test-ForPacs.ps1 -inputobject c-is-ts-91, c-is-ts-95
Runs against c-is-ts-91, c-is-ts-95 (-computername)
Runs Test-ForPacs against each
.EXAMPLE
$stuff = [pscustomobject] @{
ContentFile = "windows\system32\drivers\etc\hosts"
Logfile = "C:\temp\log.txt"
}
$computers | Invoke-Parallel -parameter $stuff {
$contentFile = join-path "\\$_\c$" $parameter.contentfile
Get-Content $contentFile |
set-content $parameter.logfile
}
This example uses the parameter argument. This parameter is a single object. To pass multiple items into the script block, we create a custom object (using a PowerShell v3 language) with properties we want to pass in.
Inside the script block, $parameter is used to reference this parameter object. This example sets a content file, gets content from that file, and sets it to a predefined log file.
.EXAMPLE
$test = 5
1..2 | Invoke-Parallel -ImportVariables {$_ * $test}
Add variables from the current session to the session state. Without -ImportVariables $Test would not be accessible
.EXAMPLE
$test = 5
1..2 | Invoke-Parallel {$_ * $Using:test}
Reference a variable from the current session with the $Using:<Variable> syntax. Requires PowerShell 3 or later. Note that -ImportVariables parameter is no longer necessary.
.FUNCTIONALITY
PowerShell Language
.NOTES
Credit to Boe Prox for the base runspace code and $Using implementation
http://learn-powershell.net/2012/05/10/speedy-network-information-query-using-powershell/
http://gallery.technet.microsoft.com/scriptcenter/Speedy-Network-Information-5b1406fb#content
https://github.com/proxb/PoshRSJob/
Credit to T Bryce Yehl for the Quiet and NoCloseOnTimeout implementations
Credit to Sergei Vorobev for the many ideas and contributions that have improved functionality, reliability, and ease of use
.LINK
https://github.com/RamblingCookieMonster/Invoke-Parallel
#>
[cmdletbinding(DefaultParameterSetName = 'ScriptBlock')]
Param (
[Parameter(Mandatory = $false,position = 0,ParameterSetName = 'ScriptBlock')]
[System.Management.Automation.ScriptBlock]$ScriptBlock,
[Parameter(Mandatory = $false,ParameterSetName = 'ScriptFile')]
[ValidateScript({
Test-Path $_ -PathType leaf
})]
$ScriptFile,
[Parameter(Mandatory = $true,ValueFromPipeline = $true)]
[Alias('CN','__Server','IPAddress','Server','ComputerName')]
[PSObject]$InputObject,
[PSObject]$Parameter,
[switch]$ImportSessionFunctions,
[switch]$ImportVariables,
[switch]$ImportModules,
[int]$Throttle = 20,
[int]$SleepTimer = 200,
[int]$RunspaceTimeout = 0,
[switch]$NoCloseOnTimeout = $false,
[int]$MaxQueue,
[validatescript({
Test-Path (Split-Path -Path $_ -Parent)
})]
[string]$LogFile = 'C:\temp\log.log',
[switch] $Quiet = $false
)
Begin {
#No max queue specified? Estimate one.
#We use the script scope to resolve an odd PowerShell 2 issue where MaxQueue isn't seen later in the function
if( -not $PSBoundParameters.ContainsKey('MaxQueue') )
{
if($RunspaceTimeout -ne 0)
{
$script:MaxQueue = $Throttle
}
else
{
$script:MaxQueue = $Throttle * 3
}
}
else
{
$script:MaxQueue = $MaxQueue
}
#Write-Verbose "Throttle: '$throttle' SleepTimer '$sleepTimer' runSpaceTimeout '$runspaceTimeout' maxQueue '$maxQueue' logFile '$logFile'"
#If they want to import variables or modules, create a clean runspace, get loaded items, use those to exclude items
if ($ImportVariables -or $ImportModules)
{
$StandardUserEnv = [powershell]::Create().addscript({
#Get modules and snapins in this clean runspace
$Modules = Get-Module | Select-Object -ExpandProperty Name
$Snapins = Get-PSSnapin | Select-Object -ExpandProperty Name
#Get variables in this clean runspace
#Called last to get vars like $? into session
$Variables = Get-Variable | Select-Object -ExpandProperty Name
#Return a hashtable where we can access each.
@{
Variables = $Variables
Modules = $Modules
Snapins = $Snapins
}
}).invoke()[0]
if ($ImportVariables)
{
#Exclude common parameters, bound parameters, and automatic variables
Function _temp
{
[cmdletbinding()] param()
}
$VariablesToExclude = @( (Get-Command _temp | Select-Object -ExpandProperty parameters).Keys + $PSBoundParameters.Keys + $StandardUserEnv.Variables )
#Write-Verbose "Excluding variables $( ($VariablesToExclude | sort ) -join ", ")"
# we don't use 'Get-Variable -Exclude', because it uses regexps.
# One of the veriables that we pass is '$?'.
# There could be other variables with such problems.
# Scope 2 required if we move to a real module
$UserVariables = @( Get-Variable | Where-Object -FilterScript {
-not ($VariablesToExclude -contains $_.Name)
} )
#Write-Verbose "Found variables to import: $( ($UserVariables | Select -expandproperty Name | Sort ) -join ", " | Out-String).`n"
}
if ($ImportModules)
{
$UserModules = @( Get-Module |
Where-Object -FilterScript {
$StandardUserEnv.Modules -notcontains $_.Name -and (Test-Path -Path $_.Path -ErrorAction SilentlyContinue)
} |
Select-Object -ExpandProperty Path )
$UserSnapins = @( Get-PSSnapin |
Select-Object -ExpandProperty Name |
Where-Object -FilterScript {
$StandardUserEnv.Snapins -notcontains $_
} )
}
}
#region functions
Function Get-RunspaceData
{
[cmdletbinding()]
param( [switch]$Wait )
#loop through runspaces
#if $wait is specified, keep looping until all complete
Do
{
#set more to false for tracking completion
$more = $false
#Progress bar if we have inputobject count (bound parameter)
if (-not $Quiet)
{
Write-Progress -Activity 'Running Query' -Status 'Starting threads'`
-CurrentOperation "$startedCount threads defined - $totalCount input objects - $script:completedCount input objects processed"`
-PercentComplete $( Try
{
$script:completedCount / $totalCount * 100
}
Catch
{
0
}
)
}
#run through each runspace.
Foreach($runspace in $runspaces)
{
#get the duration - inaccurate
$currentdate = Get-Date
$runtime = $currentdate - $runspace.startTime
$runMin = [math]::Round( $runtime.totalminutes ,2 )
#set up log object
$log = '' | Select-Object -Property Date, Action, Runtime, Status, Details
$log.Action = "Removing:'$($runspace.object)'"
$log.Date = $currentdate
$log.Runtime = "$runMin minutes"
#If runspace completed, end invoke, dispose, recycle, counter++
If ($runspace.Runspace.isCompleted)
{
$script:completedCount++
#check if there were errors
if($runspace.powershell.Streams.Error.Count -gt 0)
{
#set the logging info and move the file to completed
$log.status = 'CompletedWithErrors'
#Write-Verbose ($log | ConvertTo-Csv -Delimiter ";" -NoTypeInformation)[1]
foreach($ErrorRecord in $runspace.powershell.Streams.Error)
{
Write-Error -ErrorRecord $ErrorRecord
}
}
else
{
#add logging details and cleanup
$log.status = 'Completed'
#Write-Verbose ($log | ConvertTo-Csv -Delimiter ";" -NoTypeInformation)[1]
}
#everything is logged, clean up the runspace
$runspace.powershell.EndInvoke($runspace.Runspace)
$runspace.powershell.dispose()
$runspace.Runspace = $null
$runspace.powershell = $null
}
#If runtime exceeds max, dispose the runspace
ElseIf ( $RunspaceTimeout -ne 0 -and $runtime.totalseconds -gt $RunspaceTimeout)
{
$script:completedCount++
$timedOutTasks = $true
#add logging details and cleanup
$log.status = 'TimedOut'
#Write-Verbose ($log | ConvertTo-Csv -Delimiter ";" -NoTypeInformation)[1]
Write-Error -Message "Runspace timed out at $($runtime.totalseconds) seconds for the object:`n$($runspace.object | Out-String)"
#Depending on how it hangs, we could still get stuck here as dispose calls a synchronous method on the powershell instance
if (!$NoCloseOnTimeout)
{
$runspace.powershell.dispose()
}
$runspace.Runspace = $null
$runspace.powershell = $null
$completedCount++
}
#If runspace isn't null set more to true
ElseIf ($runspace.Runspace -ne $null )
{
$log = $null
$more = $true
}
#log the results if a log file was indicated
<#
if($logFile -and $log){
($log | ConvertTo-Csv -Delimiter ";" -NoTypeInformation)[1] | out-file $LogFile -append
}
#>
}
#Clean out unused runspace jobs
$temphash = $runspaces.clone()
$temphash |
Where-Object -FilterScript {
$_.runspace -eq $null
} |
ForEach-Object -Process {
$runspaces.remove($_)
}
#sleep for a bit if we will loop again
if($PSBoundParameters['Wait'])
{
Start-Sleep -Milliseconds $SleepTimer
}
#Loop again only if -wait parameter and there are more runspaces to process
}
while ($more -and $PSBoundParameters['Wait'])
#End of runspace function
}
#endregion functions
#region Init
if($PSCmdlet.ParameterSetName -eq 'ScriptFile')
{
$ScriptBlock = [scriptblock]::Create( $(Get-Content $ScriptFile | Out-String) )
}
elseif($PSCmdlet.ParameterSetName -eq 'ScriptBlock')
{
#Start building parameter names for the param block
[string[]]$ParamsToAdd = '$_'
if( $PSBoundParameters.ContainsKey('Parameter') )
{
$ParamsToAdd += '$Parameter'
}
$UsingVariableData = $null
# This code enables $Using support through the AST.
# This is entirely from Boe Prox, and his https://github.com/proxb/PoshRSJob module; all credit to Boe!
if($PSVersionTable.PSVersion.Major -gt 2)
{
#Extract using references
$UsingVariables = $ScriptBlock.ast.FindAll({
$args[0] -is [System.Management.Automation.Language.UsingExpressionAst]
},$true)
If ($UsingVariables)
{
$List = New-Object -TypeName 'System.Collections.Generic.List`1[System.Management.Automation.Language.VariableExpressionAst]'
ForEach ($Ast in $UsingVariables)
{
[void]$List.Add($Ast.SubExpression)
}
$UsingVar = $UsingVariables |
Group-Object -Property SubExpression |
ForEach-Object -Process {
$_.Group |
Select-Object -First 1
}
#Extract the name, value, and create replacements for each
$UsingVariableData = ForEach ($Var in $UsingVar)
{
Try
{
$Value = Get-Variable -Name $Var.SubExpression.VariablePath.UserPath -ErrorAction Stop
[pscustomobject]@{
Name = $Var.SubExpression.Extent.Text
Value = $Value.Value
NewName = ('$__using_{0}' -f $Var.SubExpression.VariablePath.UserPath)
NewVarName = ('__using_{0}' -f $Var.SubExpression.VariablePath.UserPath)
}
}
Catch
{
Write-Error -Message "$($Var.SubExpression.Extent.Text) is not a valid Using: variable!"
}
}
$ParamsToAdd += $UsingVariableData | Select-Object -ExpandProperty NewName -Unique
$NewParams = $UsingVariableData.NewName -join ', '
$Tuple = [Tuple]::Create($List, $NewParams)
$bindingFlags = [Reflection.BindingFlags]'Default,NonPublic,Instance'
$GetWithInputHandlingForInvokeCommandImpl = ($ScriptBlock.ast.gettype().GetMethod('GetWithInputHandlingForInvokeCommandImpl',$bindingFlags))
$StringScriptBlock = $GetWithInputHandlingForInvokeCommandImpl.Invoke($ScriptBlock.ast,@($Tuple))
$ScriptBlock = [scriptblock]::Create($StringScriptBlock)
#Write-Verbose $StringScriptBlock
}
}
$ScriptBlock = $ExecutionContext.InvokeCommand.NewScriptBlock("param($($ParamsToAdd -Join ', '))`r`n" + $ScriptBlock.ToString())
}
else
{
Throw 'Must provide ScriptBlock or ScriptFile'
Break
}
Write-Debug -Message "`$ScriptBlock: $($ScriptBlock | Out-String)"
If (-not($SuppressVerbose)){
Write-Verbose -Message ' - Creating runspace pool and session states'
}
#If specified, add variables and modules/snapins to session state
$sessionstate = [System.Management.Automation.Runspaces.InitialSessionState]::CreateDefault()
if ($ImportVariables)
{
if($UserVariables.count -gt 0)
{
foreach($Variable in $UserVariables)
{
$sessionstate.Variables.Add( (New-Object -TypeName System.Management.Automation.Runspaces.SessionStateVariableEntry -ArgumentList $Variable.Name, $Variable.Value, $null) )
}
}
}
if ($ImportModules)
{
if($UserModules.count -gt 0)
{
foreach($ModulePath in $UserModules)
{
$sessionstate.ImportPSModule($ModulePath)
}
}
if($UserSnapins.count -gt 0)
{
foreach($PSSnapin in $UserSnapins)
{
[void]$sessionstate.ImportPSSnapIn($PSSnapin, [ref]$null)
}
}
}
# --------------------------------------------------
#region - Import Session Functions
# --------------------------------------------------
# Import functions from the current session into the RunspacePool sessionstate
if($ImportSessionFunctions)
{
# Import all session functions into the runspace session state from the current one
Get-ChildItem -Path Function:\ |
Where-Object -FilterScript {
$_.name -notlike '*:*'
} |
Select-Object -Property name -ExpandProperty name |
ForEach-Object -Process {
# Get the function code
$Definition = Get-Content -Path "function:\$_" -ErrorAction Stop
# Create a sessionstate function with the same name and code
$SessionStateFunction = New-Object -TypeName System.Management.Automation.Runspaces.SessionStateFunctionEntry -ArgumentList "$_", $Definition
# Add the function to the session state
$sessionstate.Commands.Add($SessionStateFunction)
}
}
#endregion
#Create runspace pool
$runspacepool = [runspacefactory]::CreateRunspacePool(1, $Throttle, $sessionstate, $Host)
$runspacepool.Open()
#Write-Verbose "Creating empty collection to hold runspace jobs"
$Script:runspaces = New-Object -TypeName System.Collections.ArrayList
#If inputObject is bound get a total count and set bound to true
$bound = $PSBoundParameters.keys -contains 'InputObject'
if(-not $bound)
{
[System.Collections.ArrayList]$allObjects = @()
}
<#
#Set up log file if specified
if( $LogFile ){
New-Item -ItemType file -path $logFile -force | Out-Null
("" | Select Date, Action, Runtime, Status, Details | ConvertTo-Csv -NoTypeInformation -Delimiter ";")[0] | Out-File $LogFile
}
#write initial log entry
$log = "" | Select Date, Action, Runtime, Status, Details
$log.Date = Get-Date
$log.Action = "Batch processing started"
$log.Runtime = $null
$log.Status = "Started"
$log.Details = $null
if($logFile) {
($log | convertto-csv -Delimiter ";" -NoTypeInformation)[1] | Out-File $LogFile -Append
}
#>
$timedOutTasks = $false
#endregion INIT
}
Process {
#add piped objects to all objects or set all objects to bound input object parameter
if($bound)
{
$allObjects = $InputObject
}
Else
{
[void]$allObjects.add( $InputObject )
}
}
End {
#Use Try/Finally to catch Ctrl+C and clean up.
Try
{
#counts for progress
$totalCount = $allObjects.count
$script:completedCount = 0
$startedCount = 0
foreach($object in $allObjects)
{
#region add scripts to runspace pool
#Create the powershell instance, set verbose if needed, supply the scriptblock and parameters
$powershell = [powershell]::Create()
if ($VerbosePreference -eq 'Continue')
{
[void]$powershell.AddScript({
$VerbosePreference = 'Continue'
})
}
[void]$powershell.AddScript($ScriptBlock).AddArgument($object)
if ($Parameter)
{
[void]$powershell.AddArgument($Parameter)
}
# $Using support from Boe Prox
if ($UsingVariableData)
{
Foreach($UsingVariable in $UsingVariableData)
{
#Write-Verbose "Adding $($UsingVariable.Name) with value: $($UsingVariable.Value)"
[void]$powershell.AddArgument($UsingVariable.Value)
}
}
#Add the runspace into the powershell instance
$powershell.RunspacePool = $runspacepool
#Create a temporary collection for each runspace
$temp = '' | Select-Object -Property PowerShell, StartTime, object, Runspace
$temp.PowerShell = $powershell
$temp.StartTime = Get-Date
$temp.object = $object
#Save the handle output when calling BeginInvoke() that will be used later to end the runspace
$temp.Runspace = $powershell.BeginInvoke()
$startedCount++
#Add the temp tracking info to $runspaces collection
#Write-Verbose ( "Adding {0} to collection at {1}" -f $temp.object, $temp.starttime.tostring() )
$null = $runspaces.Add($temp)
#loop through existing runspaces one time
Get-RunspaceData
#If we have more running than max queue (used to control timeout accuracy)
#Script scope resolves odd PowerShell 2 issue
$firstRun = $true
while ($runspaces.count -ge $script:MaxQueue)
{
#give verbose output
if($firstRun)
{
#Write-Verbose "$($runspaces.count) items running - exceeded $Script:MaxQueue limit."
}
$firstRun = $false
#run get-runspace data and sleep for a short while
Get-RunspaceData
Start-Sleep -Milliseconds $SleepTimer
}
#endregion add scripts to runspace pool
}
#Write-Verbose ( "Finish processing the remaining runspace jobs: {0}" -f ( @($runspaces | Where {$_.Runspace -ne $Null}).Count) )
Get-RunspaceData -wait
if (-not $Quiet)
{
Write-Progress -Activity 'Running Query' -Status 'Starting threads' -Completed
}
}
Finally
{
#Close the runspace pool, unless we specified no close on timeout and something timed out
if ( ($timedOutTasks -eq $false) -or ( ($timedOutTasks -eq $true) -and ($NoCloseOnTimeout -eq $false) ) )
{
If (-not($SuppressVerbose)){
Write-Verbose -Message 'Closing the runspace pool'
}
$runspacepool.close()
}
#collect garbage
[gc]::Collect()
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment