Leveraging Storage Spaces Direct for SQL Server High Availability
Thursday, July 19, 2018 - click here to learn more
I've recently been asked to identify and report on any SQL Servers listening on my network. I need to try and find all SQL Servers, not just the ones in my domain. We know there are a couple of appliances that are potentially running SQL Server and we want to see them, too. What can I use to do this?
While there are several good tools out there, the one that has been used by network and security professionals for years is a free tool called nmap. It does a few things very well:
- It detects what systems are listening on the network via pings (ICMP protocol).
- It can check an IP even if it's not responding to pings (great for appliances that might not respond).
- It can scan ports on those hosts to see what's open.
In addition, with every version released the product does the following even better, if you tell it to attempt these things:
- Identify what operating system the host is running.
- Identify what is listening on each port detected.
That makes it a great tool for detecting anything, especially Microsoft SQL Server installations.
The Requisite Warning
The nmap tool is often considered a hacking tool by organizations. If you're going to use this tool within your organization, make sure you have permission to do so by someone who is actually authorized to give you that permission. In alot of organizations, the DBA manager does not have this permission. It usually falls to a security or networking manager to authorize this. When you do get said permission, make sure it is in "writing." Email is fine. You don't want to go on verbal permission. If something goes wrong (you're in IT, so you always plan for something to go wrong, or at least you should be) you want to be able to show you had permission to do what you were doing. Failure to get written permission from the right person can lead to what we call a "career altering event." Avoid this by doing the legwork to get the permission.
Detecting SQL Servers
When it comes to detecting SQL Servers on the network, we can use nmap to do this two 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 tells us that there is a named instance (at least one) present on the system. In both cases, nmap is usually able to fingerprint the version of SQL Server as well. In the second case it's also usually able to get back the name of the named instance and the exact TCP port it's listening on.
What about scanning all possible TCP ports? There's two problems with this. One, it takes a lot longer to scan a network if we're scanning a whole bunch of ports per host instead of just one. Second, nmap doesn't do such a great job fingerprinting SQL Server listening on an alternate port. The nmap tool is smart and as quick as it can be. There are so many possible combinations that when it checks a port, it's looking for what is likely to be there. SQL Server is likely to be on 1433, so nmap does a good job fingerprinting it. SQL Server is not likely to be on port 21675, so it doesn't make sense to try and fingerprint SQL Server on that port.
nmap - the Switches
As nmap started off on non-Windows systems, it's designed to be run from the command line. There is a GUI interface for Windows users, however, my recommendation is to get familiar with the switches. The main reason is simple: once you know them, building the scans is easy and then you can write batch files/command scripts which allow you to re-run them. If you're being asked to audit this once, it is very likely you'll be asked to repeat these scans on a recurring basis. Therefore, getting familiar with the command line is the best way to go:
- -? - This is the nearly universal "I need help switch" and it's very comprehensive when it comes to nmap. Running nmap -? will give you all the details on all the other switches.
- -p - This switch allows you to tell nmap what ports to scan. It has a sec of well known ports it will scan normally, but 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.
- -sV - This switch tells nmap to investigate any open ports it detects to determine if it can find out exactly what service and version. This is what lets us fingerprint a SQL Server.
- -oG <file> - This switch isn't strictly necessary. However, what it does is put the results from a single IP all on one line. This is great if you want to use a PowerShell script to parse the scan and report your findings.
- -p - Again, we want to scan a port. In this case, -p U:1434 will do the trick.
- -sU - This tells nmap we're doing a UDP scan.
- -sV - This performs the same function as with the TCP scan.
- -oG <file> - Again, this outputs to a file that is easily parable.
Putting it All Together
In addition to the switches, I need to give it the IP range to scan. There are numerous ways to do this and the nmap instructions and the command-line help makes it all pretty simple. For the purposes of showing how to do this, I'll use a standard IP range of 192.168.5.2 to 192.168.4.254. I'm intentionally leaving off .0, .1, and .255. The .0 usually refers to the network, the .1 to the gateway (in Windows speak), and .255 is usually a broadcast address for that entire network. If you're not sure what any of that means, what you can take from that is that it doesn't correspond to a single Windows host. If you're unsure of what network ranges to use, this is where you will need to speak with your network personnel.
First, the TCP scan:
nmap -p T:1433 -sV 192.168.5.2-254 -oG tcp_scan_results.txt
And then the UDP scan:
nmap -p U:1434 -sU -sV 192.168.6.2-254 -oG udp_scan_results.txt
Reading the Output
If you've detected some SQL Servers, here's what you should be looking at for results. Disregard where you see that a port is closed or filtered. That's not what we're looking for. We should see an open port and nmap should be able to successfully see that it's SQL Server. So if we look at the output of each type of scan, we're looking for something similar to:
- TCP: Host: 192.168.5.25 (mysqlserver.mycompany.com) Ports: 1433/open/tcp//ms-sql-s//Microsoft SQL Server 2008 R2 10.50.1600; RTM/
- UDP: Host: 192.168.5.112 (yoursqlserver.mycompany.com) Ports: 1434/open/udp//ms-sql-m//Microsoft SQL Server 9.00.4035.00 (ServerName: NOBODYHOME)/
- Download a copy of nmap to install.
- Learn how to connect to and verify the existence of a named instance that might not be picked up by nmap and why it's "hidden."
- Read up on how SQL Server talks on the network.
Last Update: 2012-08-16
About the author
View all my tips