Find-DbaInstance

Want to see the source code for this command? Check out Find-DbaInstance on GitHub.
Want to see the Bill Of Health for this command? Check out Find-DbaInstance.

Synopsis

Search for SQL Server Instances.

Description

This function searches for SQL Server Instances.

It supports a variety of scans for this purpose which can be separated in two categories:

• Discovery
• Scan

Discovery: This is where it compiles a list of computers / addresses to check. It supports several methods of generating such lists (including Active Directory lookup or IP Ranges), but also supports specifying a list of computers to check.

• For details on discovery, see the documentation on the '-DiscoveryType' parameter
• For details on explicitly providing a list, see the documentation on the '-ComputerName' parameter

Scan: Once a list of computers has been provided, this command will execute a variety of actions to determine any instances present for each of them. This is described in more detail in the documentation on the '-ScanType' parameter. Additional parameters allow more granular control over individual scans (e.g. Credentials to use).

Note on logging and auditing: The Discovery phase is un-problematic since it is non-intrusive, however during the scan phase, all targeted computers may be accessed repeatedly. This may cause issues with security teams, due to many logon events and possibly failed authentication. This action constitutes a network scan, which may be illegal depending on the nation you are in and whether you own the network you scan. If you are unsure whether you may use this command in your environment, check the detailed description on the '-ScanType' parameter and contact your IT security team for advice.

Syntax

Find-DbaInstance
    [-Credential <PSCredential>]
    [-SqlCredential <PSCredential>]
    [-ScanType {TCPPort | SqlConnect | SqlService | DNSResolve | SPN | Browser | Ping | Default | All}]
    [-DomainController <String>]
    [-TCPPort <Int32[]>]
    [-MinimumConfidence {None | Low | Medium | High}]
    [-EnableException]
    [<CommonParameters>]

Find-DbaInstance -ComputerName <DbaInstanceParameter[]>
    [-Credential <PSCredential>]
    [-SqlCredential <PSCredential>]
    [-ScanType {TCPPort | SqlConnect | SqlService | DNSResolve | SPN | Browser | Ping | Default | All}]
    [-DomainController <String>]
    [-TCPPort <Int32[]>]
    [-MinimumConfidence {None | Low | Medium | High}]
    [-EnableException]
    [<CommonParameters>]

Find-DbaInstance -DiscoveryType {IPRange | DomainSPN | Domain | DataSourceEnumeration | DomainServer | All}
    [-Credential <PSCredential>]
    [-SqlCredential <PSCredential>]
    [-ScanType {TCPPort | SqlConnect | SqlService | DNSResolve | SPN | Browser | Ping | Default | All}]
    [-IpAddress <String[]>]
    [-DomainController <String>]
    [-TCPPort <Int32[]>]
    [-MinimumConfidence {None | Low | Medium | High}]
    [-EnableException]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> Find-DbaInstance -DiscoveryType Domain, DataSourceEnumeration

Performs a network search for SQL Instances by:

• Looking up the Service Principal Names of computers in Active Directory
  
• Using the UDP broadcast based auto-discovery of SSMS
  After that it will extensively scan all hosts thus discovered for instances.
  

Example: 2

PS C:\> Find-DbaInstance -DiscoveryType All

Performs a network search for SQL Instances, using all discovery protocols:

• Active directory search for Service Principal Names
  
• SQL Instance Enumeration (same as SSMS does)
  
• All IPAddresses in the current computer's subnets of all connected network interfaces
  Note: This scan will take a long time, due to including the IP Scan
  

Example: 3

PS C:\> Get-ADComputer -Filter "*" | Find-DbaInstance

Scans all computers in the domain for SQL Instances, using a deep probe:

• Tries resolving the name in DNS
  
• Tries pinging the computer
  
• Tries listing all SQL Services using CIM/WMI
  
• Tries discovering all instances via the browser service
  
• Tries connecting to the default TCP Port (1433)
  
• Tries connecting to the TCP port of each discovered instance
  
• Tries to establish a SQL connection to the server using default windows credentials
  
• Tries looking up the Service Principal Names for each instance
  

Example: 4

PS C:\> Get-Content .\servers.txt | Find-DbaInstance -SqlCredential $cred -ScanType Browser, SqlConnect

Reads all servers from the servers.txt file (one server per line),
then scans each of them for instances using the browser service
and finally attempts to connect to each instance found using the specified credentials.
then scans each of them for instances using the browser service and SqlService

Example: 5

PS C:\> Find-DbaInstance -ComputerName localhost | Get-DbaDatabase | Format-Table -Wrap

Scans localhost for instances using the browser service, traverses all instances for all databases and displays all information in a formatted table.

Example: 6

PS C:\> $databases = Find-DbaInstance -ComputerName localhost | Get-DbaDatabase
PS C:\> $results = $databases | Select-Object SqlInstance, Name, Status, RecoveryModel, SizeMB, Compatibility, Owner, LastFullBackup, LastDiffBackup, LastLogBackup
PS C:\> $results | Format-Table -Wrap

Scans localhost for instances using the browser service, traverses all instances for all databases and displays a subset of the important information in a formatted table.
Using this method regularly is not recommended. Use Get-DbaService or Get-DbaRegServer instead.

Required Parameters

-ComputerName

The computer to scan. Can be a variety of input types, including text or the output of Get-ADComputer. Any extra instance information (such as connection strings or live sql server connections) beyond the computername will be discarded.

-DiscoveryType

The mechanisms to be used to discover instances. Supports any combination of:

• Service Principal Name lookup ('DomainSPN'; from Active Directory)
• SQL Instance Enumeration ('DataSourceEnumeration'; same as SSMS uses)
• IP Address range ('IPRange'; all IP Addresses will be scanned)
• Domain Server lookup ('DomainServer'; from Active Directory)

• SPN Lookup

  The function tries to connect active directory to look up all computers with registered SQL Instances. Not all instances need to be registered properly, making this not 100% reliable. By default, your nearest Domain Controller is contacted for this scan. However it is possible to explicitly state the DC to contact using its DistinguishedName and the '-DomainController' parameter. If credentials were specified using the '-Credential' parameter, those same credentials are used to perform this lookup, allowing the scan of other domains.

• SQL Instance Enumeration

  This uses the default UDP Broadcast based instance enumeration used by SSMS to detect instances. Note that the result from this is not used in the actual scan, but only to compile a list of computers to scan. To enable the same results for the scan, ensure that the 'Browser' scan is enabled.

• IP Address range:

  This 'Discovery' uses a range of IPAddresses and simply passes them on to be tested. See the 'Description' part of help on security issues of network scanning. By default, it will enumerate all ethernet network adapters on the local computer and scan the entire subnet they are on. By using the '-IpAddress' parameter, custom network ranges can be specified.

• Domain Server:

  This will discover every single computer in Active Directory that is a Windows Server and enabled. By default, your nearest Domain Controller is contacted for this scan. However it is possible to explicitly state the DC to contact using its DistinguishedName and the '-DomainController' parameter. If credentials were specified using the '-Credential' parameter, those same credentials are used to perform this lookup, allowing the scan of other domains.
  

Optional Parameters

-Credential

The credentials to use on windows network connection. These credentials are used for:

• Contact to domain controllers for SPN lookups (only if explicit Domain Controller is specified)
• CIM/WMI contact to the scanned computers during the scan phase (see the '-ScanType' parameter documentation on affected scans).
  

-SqlCredential

The credentials used to connect to SqlInstances to during the scan phase. See the '-ScanType' parameter documentation on affected scans.

-ScanType

The scans are the individual methods used to retrieve information about the scanned computer and any potentially installed instances. This parameter is optional, by default all scans except for establishing an actual SQL connection are performed. Scans can be specified in any arbitrary combination, however at least one instance detecting scan needs to be specified in order for data to be returned. Scans:

• Browser
  • Tries discovering all instances via the browser service
  • This scan detects instances.
• SQLService
  • Tries listing all SQL Services using CIM/WMI
  • This scan uses credentials specified in the '-Credential' parameter if any.
  • This scan detects instances.
  • Success in this scan guarantees high confidence (See parameter '-MinimumConfidence' for details).
• SPN
  • Tries looking up the Service Principal Names for each instance
  • Will use the nearest Domain Controller by default
  • Target a specific domain controller using the '-DomainController' parameter
  • If using the '-DomainController' parameter, use the '-Credential' parameter to specify the credentials used to connect
• TCPPort
  • Tries connecting to the TCP Ports.
  • By default, port 1433 is connected to.
  • The parameter '-TCPPort' can be used to provide a list of port numbers to scan.
  • This scan detects possible instances. Since other services might bind to a given port, this is not the most reliable test.
  • This scan is also used to validate found SPNs if both scans are used in combination
• DNSResolve
  • Tries resolving the computername in DNS
• Ping
  • Tries pinging the computer. Failure will NOT terminate scans.
• SqlConnect
  • Tries to establish a SQL connection to the server
  • Uses windows credentials by default
  • Specify custom credentials using the '-SqlCredential' parameter
  • This scan is not used by default
  • Success in this scan guarantees high confidence (See parameter '-MinimumConfidence' for details).
• All
  • All of the above
    

-IpAddress

This parameter can be used to override the defaults for the IPRange discovery. This parameter accepts a list of strings supporting any combination of:

• Plain IP Addresses (e.g.: "10.1.1.1")
• IP Address Ranges (e.g.: "10.1.1.1-10.1.1.5")
• IP Address & Subnet Mask (e.g.: "10.1.1.1/255.255.255.0")
• IP Address & Subnet Length: (e.g.: "10.1.1.1/24) Overlapping addresses will not result in duplicate scans.
  

-DomainController

The domain controller to contact for SPN lookups / searches. Uses the credentials from the '-Credential' parameter if specified.

-TCPPort

The ports to scan in the TCP Port Scan method. Defaults to 1433.

-MinimumConfidence

This command tries to discover instances, which isn't always a sure thing. Depending on the number and type of scans completed, we have different levels of confidence in our results. By default, we will return anything that we have at least a low confidence of being an instance. These are the confidence levels we support and how they are determined:

• High: Established SQL Connection (including rejection for bad credentials) or service scan.
• Medium: Browser reply or a combination of TCPConnect and SPN test.
• Low: Either TCPConnect or SPN
• None: Computer existence could be verified, but no sign of an SQL Instance
  

-EnableException

By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message. This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting. Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.