Nmap is a popular and powerful port scanning tool that is available for multiple Operating Systems, including Windows. Although it looks like a simple port scanning utility, it has a lot of potential to help a understaffed data expert find SQL Server instances on your network.
Nmap does a few things very well, if you know how to use the tool:
- Detecting which systems are listening on the network via pings (ICMP protocol).
- Checking an IP Address even if it’s not responding to pings (great for appliances that might be configured to not respond to pings).
- Scanning ports on those discovered hosts to see which ports are open.
- Identifying which operating system the discovered host is running.
- Identify what program or service is listening on each detected port.
All of the abilities make Nmap a great tool for detecting Microsoft SQL Server installations, even if you can’t find them from within the SQL Server Management Studio (SSMS).
Note: Many network people view Nmap as a “hacking” tool, so if you’re going to use this tool within your organization make sure you have alerted your management and networking team to verify they understand what you are doing and to settle any authorization questions. Some people will really freak out, so just know that going into this demo and understand the risk involved.
When detecting SQL Servers on your network, we will use Nmap two different ways:
- By looking for SQL Servers listening via the TCP protocol on port 1433.
- By looking for SQL Servers responding to requests via the UDP protocol on port 1434.
The first one tells us that there is a SQL Server, usually a default instance, and the second lists any named instances present on the network. Nmap is often able to also determine the version of SQL Server. It may also determine name of the named instance and the TCP port used.
- -? – This is the nearly universal “help” switch and it is used to gain access to the comprehensive help documentation. Running “Nmap -?” will give you all the details on all the available switches.
- -p – This switch allows you to tell nmap what ports to scan. We only want it to scan one port – 1433. Therefore, we’ll want to use the -p switch with T:1433 to restrict the scan to that one port.
- -sV – This switch tells nmap to investigate any open ports it detects to determine if it can find out exactly what service and version is using that port. This is what lets us fingerprint a SQL Server when possible.
- -oG <file> – This switch isn’t required, but this will put the results from a single IP all on one line. This is great if you want to use the results of the scan to easily report your findings.
- -p – Again, we want to scan a port. In our demo -p U:1434 will find named instances.
- -sU – This tells Nmap we’re doing a UDP scan.
- -sV – This performs the same function as with the TCP scan.
- -oG <file> -This is great if you want to use the results of the scan to easily report your findings.
TCP scan to detect SQL Server:
This is the scan used to detect all available SQL Server instances. In this example, we will be looking for instances in the 10.1.1.2 – 10.1.1.254 range. This assumes we know the network gateway is 10.1.1.1 and the network broadcast address is 10.1.1.255. If you are not sure about the proper range to use, this is another discussion with your network team.
nmap -p T:1433 -sV 10.1.1.2-254 -oG tcp_SQL_Server_results.txt
UDP scan to detect SQL Server:
This is the scan used to detect all available SQL Server named instances. In this example, we will be looking for named instances in the 10.1.1.2 – 10.1.1.254 range. This assumes we know the network gateway is 10.1.1.1 and the network broadcast address is 10.1.1.255. If you are not sure about the proper range to use, this is another discussion with your network team.
nmap -p U:1434 -sU -sV 10.1.1.2-254 -oG udp_SQL_Server_results.txt
Note: The file will be output to the same path that is shown in the command-line when you execute the NMap command, unless you include a path with the filename when you execute the command.
TCP Port Scan Example Output
Nmap scan report for sampleserver.domain.local (10.1.1.35) Host is up (0.0020s latency). PORT STATE SERVICE VERSION 1433/tcp open ms-sql-s Microsoft SQL Server 2008 R2 10.50.2500; SP1 Service Info: OS: Windows; CPE: cpe:/o:microsoft:windows
UDP Port Scan Example Output
Nmap scan report for sampleserver.domain.local (10.1.1.35) Host is up (0.00s latency). PORT STATE SERVICE VERSION 1434/udp closed ms-sql-m
The output from these scans should list all discovered instances. Even if you don’t find anything unexpected, this is a valuable tool that you should know how to use. In the event a rogue database instance is suspected, you can quickly pull out these scripts and verify the results in a few minutes.