6 Finding SQL Server instances on your network
Have you ever started a new job and, after asking for the list of SQL Servers you’ll be managing, were given an incomplete list of IPs and backup locations? This happens to us all the time! This is one of the primary reasons we often start each new job by scanning the network for undocumented SQL Server instances. It’s important to be aware of all SQL Server instances within a network so that they can all be managed, backed up, and secured. Years ago, when Chrissy started working at a security operations center, she was given an Excel spreadsheet with three IP addresses. “Here are the servers you’ll be maintaining,” her manager told her. Three SQL Server instances just seemed too small of a number for the size of the network. Sure enough, after downloading five different SQL Server discovery tools, she found about 40 more instances, 20 of which were not embedded and needed to be actively maintained.
Finding rogue SQL Servers can be challenging because of unmanaged server sprawl, inconsistent network configurations, firewall settings, and more. Further, it can be challenging because the database engine may use different ports, and SQL Server as a whole comprises many components, such as Reporting Services and Integration Services.
When DBAs are given an incomplete view of their estate, there’s often a natural progression of tasks that look like the following, depicted in figure 6.1. First, you have to find your undocumented servers. Then, you can go to the next step of inventorying your SQL Servers and centralizing your inventory for easier management and standardization.
This chapter addresses that base step: finding all of your SQL Server instances. In this chapter, we’ll teach you how to find most, if not all, SQL Server instances on your network, and we’ll accomplish this using a single command written by three well-known security professionals.
The command, Find-DbaInstance, was originally created by Eric Gruber of the well-known security firm, NetSPI. Scott Sutherland, also of NetSPI, then enhanced the command and added his SQL Server security module, PowerUpSQL. We asked Scott if he’d be willing to share it with dbatools, and he was kind enough to submit a pull request on GitHub. Microsoft Security PFE and dbatools architect Friedrich Weinmann then ported Scott’s code to C# to increase performance.
Having security researchers and professionals write these types of commands is useful because it means that you will have the same abilities and techniques to find rogue instances as attackers who may be looking to exploit SQL Server vulnerabilities.
Finding SQL Server weaknesses PowerUpSQL is a toolkit used for internal penetration tests and red team engagements. Not only does PowerUpSQL help find rogue SQL Server instances, it helps discover vulnerabilities and misconfigurations. If you’d like to experiment with your SQL Server estate, the module is free and open source. You can install it from the PowerShell Gallery using Install-Module -Name PowerUpSQL.
Find-DbaInstance uses a variety of approaches to dig for SQL Servers. It even performs UDP scans! And in addition to the database engine, it also tries to find other components as well. Just like the database engine, other aspects like Analysis Services and Integration Services should be backed up, patched, and managed as well.
Once you find all of your SQL Server instances, you’ll want to know more about them. In the next two chapters, we’ll show you how to do exactly this. Not only will we help you inventory those instances, we’ll also show you how to add your complete collection of SQL Server instances to a centralized system for long-term management.
6.1 Background
The first SQL Server scanner we’re aware of is the Microsoft tool, SQL Scan, which was used to help remediate the SQL Slammer worm. Other command-line and GUI-based scanners such as Microsoft Assessment and Planning Toolkit (http://dbatools.io/msassessplantoolkit) and ApexSQL Discover (http://dbatools.io/apexsqldiscover) entered the market in later years.
In the past, we’ve had to use a mix of methods (see http://dbatools.io/sqlinstinventory) and applications to ensure we’ve found all SQL Server instances on the network. Our goal with Find-DbaInstance was to ensure that only one tool would be required.
6.1.1 Finding an instance
Before we start, we want to note that Find-DbaInstance should be used only to find undocumented and rogue instances; it should not be used as your daily inventory source. This is because Find-DbaInstance performs deep, time-consuming probes. Instead, consider relying on a prebuilt inventory source such as Registered Servers, which we will cover in chapter 8.
To find undocumented SQL Servers on your network on an ongoing basis, consider setting up scheduled scans using Task Scheduler or even SQL Server Agent (dbatools.io/agent).
Warning about scanning Always obtain explicit permission from the owner of the network you plan to scan. This is true not only for using Find-DbaInstance, but for any tool that scans a network, including Microsoft Assessment and Planning Toolkit (MAPS).
Find-DbaInstance is a powerful command that performs two primary tasks: discovery and scan. Discovery finds the computers that the command will scan, and scan actually probes for the SQL Server instance.
Discovery types are different ways to create the collection of servers that will be probed. You can then scan this collection of computers using various scan types. Each scan type has a different approach to determining whether a SQL Server component has been installed on a server.
Tip As mentioned earlier, the scans can be invasive and time consuming. The first few examples we will give you were chosen because they are not intrusive, yet they still are demonstrative of the capabilities of the command and will give you results relatively quickly. We expect that you will be running the examples in a lab environment; we’ll be running against the dbatoolslab machine we built in chapter 3.
6.1.2 Finding instances using a list of targets
Let’s jump right in and find all of the SQL Server instances installed on our lab computer using the following code. If you followed along with chapter 3, we’ll expect at least one instance to show up.
Listing 6.1 Finding SQL Server instances on dbatoolslab
When specifying -ComputerName, no discovery is required because the computer to probe, dbatoolslab, has been explicitly given. No -ScanType was specified, so default scans will be performed. So, the code we just ran will attempt to do the following:
That means we essentially ran -ScanType Default, which runs all available scans except for SqlConnect. This is because, by default, we want to avoid potentially failed logins created by SqlConnect. If you choose to use SqlConnect like we do, we think you’ll appreciate the results, but we don’t want to make that decision for you, especially as a default.
When it comes to which computers you’ll scan, you can also search other computers by name as well. You can use a variety of sources to build your collection, including text files or even Get-ADComputer, as shown in the next code sample. This method can be especially useful when you’ve inherited a new batch of computers, know their names, and want to get a quick inventory.
Listing 6.2 Finding SQL Server instances from lists of computers
# Pipe in computers from a text file PS> Get-Content -Path C: empserverlist.txt | Find-DbaInstance # Pipe in computers from Active Directory PS> Get-ADComputer -Filter "*" | Find-DbaInstance
When using the Get-Content example in listing 6.2, make sure serverlist.txt lists just one computer name per line.
Finding instances using the SQL Server Browser
Now that you’ve found all of the SQL Servers on your lab computer, let’s search the network. We’ll start with a simple example that uses the SQL Server Browser service. For instances to be reported when searching using the SQL Server Browser, the firewall on the server must allow UDP port 1434, the Browser service must be running, and the SQL Server instance cannot be hidden (this is a setting in the Configuration Manager).
For technical reasons, the Browser discovery type is called DataSourceEnumeration. This data source enumeration essentially asks the network for information about instances that are advertised by the SQL Server Browser Windows service. It’s basically the same as clicking the Browse for Servers tab in SQL Server Management Studio’s Connect dialog box (figure 6.2).
Remember that -DiscoveryType collects the computers to scan, whereas -ScanType performs the actual scan. Once the collection of servers is received from the DataSourceEnumeration discovery, the Browser scan then probes for more information, as shown here.
Listing 6.3 Finding SQL Server instances on several computers
# Running this command took about 20 seconds in our lab PS> Find-DbaInstance -DiscoveryType DataSourceEnumeration ➥ -ScanType Browser ComputerName InstanceName Port Availability Confidence ScanTypes ------------ ------------ ---- ------------ ---------- --------- SQLDEV01 SQL2008R2SP2 50348 Unknown Medium Browser SQLDEV01 MSSQLSERVER 1433 Unknown Medium Browser SQLDEV01 MIXER 50285 Unknown Medium Browser SQL2017 MSSQLSERVER 1433 Unknown Medium Browser SQL2017 MIXER 49805 Unknown Medium Browser SQL2016 MSSQLSERVER 1433 Unknown Medium Browser SQL2016 VNEXT 49837 Unknown Medium Browser SQL2016 SQLEXPRESS 49903 Unknown Medium Browser SQL2016 STANDARDRTM 49950 Unknown Medium Browser SQL2014 MSSQLSERVER 1433 Unknown Medium Browser SQL2008 MSSQLSERVER 1433 Unknown Medium Browser SQL2008 SQL2K8 49271 Unknown Medium Browser SQL2000 1433 Unknown Medium Browser SQLCLUSTER MSSQLSERVER 1433 Unknown Medium Browser SQL2005 MSSQLSERVER 1433 Unknown Medium Browser
We can see that a number of SQL Server instances were found on the network. The network told us that these SQL Server instances exist, but dbatools did not establish a connection with the SQL Server. So, because we can’t say with 100% certainty that a SQL Server exists at the endpoint, we decided as a team to rate the confidence level of Browser results at Medium.
We try our best to accurately discover SQL Server instances but can’t always guarantee the accuracy of the results. Because of this, we thought it would be beneficial to add a confidence level to the results. Confidence levels are important when you want to filter out results that have a higher likelihood of being inaccurate. In the event that you want to return results with a maximum level of confidence, use the -MinimumConfidence High parameter. Table 6.1 highlights each of the scenarios that increase our confidence levels.
By default, the minimum confidence level returned is Low. To change this value to a higher (Medium, High) or lower (None) setting, use the -MinimumConfidence parameter. Our confidence with Browser results is Medium not because the list of servers returned can change with each execution based on network saturation, server performance, and other constraints (see http://dbatools.io/enumsqlserver), but because we don’t explicitly test to confirm the results reported by the network.
Browser -ScanType performs scans using native calls available in the .NET Framework. This means that any computer with .NET installed can find at least some SQL Server instances. Behind the scenes, we just use a method you can run right from PowerShell, without dbatools or SQL Server Management Studio installed.
This code is not a dbatools command but rather a .NET method, and this is the exact code we are executing under the hood.
Note that security organizations, like DISA (Defense Information Systems Agency) in the United States, explicitly recommend disabling the SQL Server Browser service for systems that do not need it, such as those that are not running named instances. If your organization complies with DISA’s Security Technical Implementation Guides, the browser scan may return fewer SQL Servers than expected.
Searching by browser is good enough when you’ve got nothing else, but if possible, DataSourceEnumeration shouldn’t be your only discovery source. Nevertheless, we think it’s good to have as part of an overall, holistic approach.
6.1.3 Finding SQL Servers in an Active Directory domain
You can also find SQL Servers in an Active Directory domain using data stored in Active Directory itself. The Domain discovery type uses service principal names (SPN) to find SQL Servers that are registered with Active Directory.
SQL Server can automatically register itself on startup, and DBAs can also register SQL services using the setspn.exe command or Set-DbaSpn. Nevertheless, not all instances may be found because there are a number of reasons why a SQL Server might not have an SPN, including permissions issues or policies. Because of this, the discovery type is not 100% reliable. Like DataSourceEnumeration, however, we think it’s good to have as part of a multipronged discovery approach.
When running this code, the nearest Domain controller is contacted and queried. Once results are received, Find-DbaInstance will then check the Windows services to confirm that SQL Server is running, as shown in the next code listing. This requires permission to the Windows server running each of the SQL Server instances.
ComputerName InstanceName Port Availability Confidence ScanTypes ------------ ------------ ---- ------------ ---------- --------- sqlserver.ad.local MSSQLSERVER 1433 Unknown Medium Default sql01.ad.local SQLEXPRESS 49752 Available High Default sql01.ad.local MSSQLSERVER 1433 Available High Default sql2008.ad.local SQL2K8 49271 Available High Default sql2008.ad.local MSSQLSERVER 1433 Available High Default sql2014.ad.local MSSQLSERVER 1433 Available High Default sql2017.ad.local SSRS 0 Unknown High Default sql2017.ad.local MSSQLSERVER 1433 Available High Default sql2017.ad.local MIXER 49805 Available High Default win10.ad.local 1433 Unknown Medium Default
If you want to search other Active Directory domains (such as Dev/Test) and your current credentials are not trusted, you can use -DomainController and -Credential as shown in the next code sample.
Listing 6.5 Using an alternative username and password
PS> $splatFindInstance = @{ DiscoveryType = "Domain" DomainController = "dc.devad.local" Credential = "devadadmin" } PS> Find-DbaInstance @splatFindInstance
Running this command will prompt you to enter the password for devadadmin, then conduct the discovery and perform the service scans as devadadmin. This means you will connect to both the domain controller and the resulting computers as devadadmin. Note that Credential is not your SQL Server login but rather an account with access to the Windows server running SQL.
6.1.4 Finding SQL Servers in your surrounding network
You can also search segments of your network or even your entire network. This is an especially useful method for finding rogue SQL Server instances that are not joined to your Active Directory domain. To be found, the server does not have to be a part of the domain; it just has to exist on the network.
By default, dbatools can figure out a range of IP addresses to scan by using information from each of your network adapters. For example, if your computer has an IP address of 192.168.0.77 and a subnet of 255.255.255.0, Find-DbaCommand will search 192.168.0.0 through 192.168.0.255. Depending on the size of your network, using the IpRange default may take hours or even days.
Instead of defaulting to a presumably large range of IP addresses to scan, consider providing Find-DbaInstance with explicit, more manageable ranges by using the -IpAddress parameter. This parameter accepts a CIDR notation (http://dbatools.io/cidrnotation) for both IPv4 and IPv6.
Note that this option can still take hours if you’re scanning a large range of computers, especially if many are offline or unresolvable. You can expect to wait three to 30 seconds per offline computer, depending on your scan type. All that said, if you have the time, using the default IP range will give you the most complete result set possible.
Getting the most accurate results
So far, we’ve run most of our commands with default scan types (you may recall the Browser section used -ScanType Browser). The full list of scan type values include All, Browser, Default, DNSResolve, SPN, Ping, SqlConnect, SqlService, and TCPPort.
The -All parameter is great because it is extremely thorough, and it will give the most accurate results because it uses all scans possible. The -All parameter can also be not so great if your account does not have access to the discovered SQL Server instances, and the security team sends alerts for failed logins. If you’d prefer running fewer scans, tables 6.2 and 6.3 can help you decide which scans are appropriate for your environment.
Table 6.2 Scan types and descriptions
|
Connect to the default TCP port (1433); use | |
|
Find all SQL Services using Windows CIM/WMI; use | |
Each scan type relies on specific ports not being blocked at the firewall. Here are the ports that, when enabled, will help return the most accurate results.
Table 6.3 Authentication usage and ports
Note that Find-DbaInstance is intended to find instances that are actually installed. We do not search for indiscriminate SQL binaries that may be part of an instance that was partially uninstalled.
6.2 Working with detailed results
Something very cool about PowerShell is that, oftentimes, you can find a bunch of properties if you dig a little deeper. Returning fewer properties by default can speed up commands and make output visually appealing. The same is true for Find-DbaInstance. By default, we return six columns, but nearly 20 are available! Let’s take a closer look at the next listing.
Listing 6.7 Expanding all properties
PS> Find-DbaInstance -ComputerName SQLDEV01 | Select * MachineName : SQLDEV01 ComputerName : SQLDEV01 InstanceName : MSSQLSERVER FullName : SQLDEV01 SqlInstance : SQLDEV01 Port : 1433 TcpConnected : True SqlConnected : False DnsResolution : System.Net.IPHostEntry Ping : True BrowseReply : SQLDEV01MSSQLSERVER Services : {SqlService (ServiceName = "MSSQLSERVER", SQLServiceTyp... SystemServices : {SqlService (ServiceName = "SQLBrowser", SQLServiceType... SPNs : PortsScanned : {SQLDEV01:1433 - True} Availability : Available Confidence : High ScanTypes : Default Timestamp : 7/16/2019 2:35:54 AM
Some properties, like ComputerName, are simple strings, but others like DnsResolution, SystemServices, and even Timestamp can be further expanded using Select -ExpandProperty, as shown next.
Listing 6.8 Expanding the specific property, DnsResolution
PS> Find-DbaInstance -ComputerName SQLDEV01 | Select -ExpandProperty DnsResolution HostName Aliases AddressList -------- ------- ----------- SQLDEV01.ad.local {} {::1, 192.168.0.10} SQLDEV01.ad.local {} {::1, 192.168.0.10} SQLDEV01.ad.local {} {::1, 192.168.0.10}
In our opinion, the most useful when it comes to newly discovered instances is Services, shown here, which gives detailed information about each of the SQL services that were found.
Listing 6.9 Expanding the specific property, Services
PS> Find-DbaInstance -ComputerName SQLDEV01 | Select -ExpandProperty Services ComputerName : SQLDEV01 ServiceName : MSSQL$SQL2008R2SP2 ServiceType : Engine InstanceName : SQL2008R2SP2 DisplayName : SQL Server (SQL2008R2SP2) StartName : LocalSystem State : Running StartMode : Automatic ComputerName : SQLDEV01 ServiceName : SQLAgent$SQL2008R2SP2 ServiceType : Agent InstanceName : SQL2008R2SP2 DisplayName : SQL Server Agent (SQL2008R2SP2) StartName : adsqlserver State : Stopped StartMode : Disabled
This information can be used to find specific services, such as Agent or Reporting Services.
6.3 OS support
Find-DbaInstance runs on Windows, macOS, and Linux, but not all scan types are universally supported. Table 6.4 provides an easy reference.
Table 6.4 Operating system support
Note that any “Unsupported” is a limitation on both the client and the host. So, if you’re running dbatools on Windows and attempting to connect to a Linux host, if the third row says “Unsupported,” the SQL Server will not be discovered. For instance, you won’t be able to use the Browser scan type from a Linux client even if you’re scanning a Windows host, because of the “Unsupported” under macOS/Linux.
Found a SQL Server but can't log in? If you find a SQL Server instance but do not have a login, you can use Reset-DbaAdmin to create a new sysadmin login if the server is running on Windows. Reset-DbaAdmin requires Windows administrator access and uses Microsoft’s recommended approach (http://dbatools.io/msregainaccess) to regain access. This command takes about 20 seconds to execute and restarts the SQL Service.
Now that you’ve learned how to find SQL Servers on your network, it’s time to begin building your estate inventory.
6.4 Hands-on lab
Let’s practice what you just read in this chapter. See if you can complete the following tasks:
Remember, you can find answers at dbatools.io/answers. And now that you’ve found your instances, it’s time to inventory your SQL Server estate.