Using nmap to scan for SQL Servers on a network
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 and the second tells us that there is a named instance (at least one) present on the system. 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, but it's good 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. Getting familiar with the command line is the best way to go:
TCP scans: -? - This is the nearly universal "I need help switch". Very comprehensive. 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.
UDP scans: -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 target and command-line help make it all pretty simple. To show how to do this, I'll use a standard IP range of 192.168.5.2 to 192.168.4.254. 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.
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)/