Overview

This capability performs a one-off search for SQL Servers on a target domain. It uses Active Directory both to obtain a list of devices (as opposed to IP range scanning) in the network and to obtain Service Principal Names that indicate SQL Server has been installed. For each returned device, presence of SQL Server is then optionally confirmed by attempting to connect to a standard SQL Server port (by default, 1433) and SQL Browser. We believe this is a ‘safe’ way to discover servers that has minimal network impact. 

Requirements

Redgate Monitor is installed and configured. It's not required that Redgate Monitor has any registered monitored servers.

Redgate Monitor can connect with and authenticate against Active Directory for the target domain using either the account it runs under or a specified account.

Redgate Monitor is able to contact the devices Active Directory might supply, i.e. no network settings prevent it from accessing the devices on the target domain.

How it works

A Redgate Monitor administrator provides a target domain and (optionally) a user account and/or a more specific LDAP path.

Redgate Monitor then queries Active Directory for devices in the domain that are listed as servers (i.e. Computer objects registered in Active Directory as running a 'Server' version of Windows), and queries them for the presence of a SQL Server Service Principal Name (SPN).

It then queries each device for further evidence of a SQL Server installation by optionally

• checking a single port on the device. By default this is 1433, but can be customized, and

• querying SQL Browser on the device for installed instances. This uses UDP port 1434.

As it progresses it reports a list of SQL Servers with a confidence indicator based on the response of each device.

Note: If used in a multi base monitor set-up, the Primary Base monitor is responsible for querying Active Directory and contacting the resulting devices. This may be enhanced in coming versions of Redgate Monitor.

How long it takes

Time taken to complete is a factor of the number of devices Active Directory reports as a server. In normal circumstances we'd expect it to take minutes, with only extremely large domains taking longer.  

Technical details

Network profile

• Maximum concurrent connections: 20
• Network requests per second: unlimited; dependent on response time of network requests
• Account: Redgate Monitor service account or as specified by user
• Times-out connecting to each machine after: 5 seconds

Active directory

Limits to computers using objectClass=computer

Limits to Windows servers using operatingSystem=windows*server*

Checks the presence of a SQL Server Service Principal Name by finding any servicePrincipalName containing mssqlsvc

Network

For every machine discovered from Active Directory:

• Optionally connects to a single TCP port (default 1433, can be set to any single port)
• Optionally queries SQL Browser (UDP port 1434)

All network requests have a timeout of 5 seconds

A maximum of 20 concurrent networks requests will be run at once