Using nmap to scan for SQL Servers on a network

By:   |   Updated: 2012-08-16   |   Comments (14)   |   Related: > Security


Problem

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?

Solution

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:

TCP scans:

  • -? - 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.
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 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)/
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2012-08-16

Comments For This Article




Wednesday, April 12, 2023 - 10:58:18 AM - Nazareth Berlanga Back To Top (91096)
D

Only if you specify the alternate port. for example

nmap -p T:<ALTERNATE PORT HERE> -sV 192.168.0.2-254 -oG tcp_scan_results.txt

If you don't know the port, perhaps try nmap -sS <IP ADDRESS>

Tuesday, August 21, 2018 - 3:18:53 AM - D Back To Top (77259)

 I know this is an old article but I hope you can asnwer my question...

Will this detect ans SQL server if publishe on an non standard port?

For example it the server is accessible on 6283.

Thursday, August 30, 2012 - 11:28:16 PM - K. Brian Kelley Back To Top (19333)

MAP does, but it requires the appropriate administrative credentials to do its job. And that's the point: what if you don't have access to a SQL Server because it's not supposed to be there - a rogue system or one where someone installed it and wasn't supposed? Then you need another tool.

The OSQL -L works most of the time if the SQL Server isn't intentionally being hidden and if udp/1434 isn't being blocked. Because of the nature of the protocol used for the SQL Server Listener/Browser, it can still fail, even when SQL Server isn't being hidden and udp/1434 isn't being blocked. I've see this in the event of clusters without a default isntance.

Again, these are all great tools when you have full access and I highly recommend them for normal operations. However, the point of this tip is to try and discover SQL Servers when you don't have full access because someone is breaking the rules or when you're dealing with an "appliance" that's not on a trusted domain.

Thursday, August 30, 2012 - 2:11:09 PM - Robert de Held Back To Top (19327)
MAPS does support SQL authentication and tries multiple accounts and discovery mechanisms, I think we have to distinct 2 things, Discovery and Assess a quick way on same subnet OSQL -L > C:\servers.txt

Thursday, August 30, 2012 - 8:40:56 AM - K. Brian Kelley Back To Top (19317)

Robert and Fabrizio, MAP works fine but it has some significant limitations when I look at things from a security side.

MAP works great *if* you can guarantee all the systems are on the domain or on a trusting domain from the account being used. One thing MAP does not do is handle cases where you've got non-domain SQL Servers, such as those running on appliances or those running on systems that shouldn't be on the network. Also, if you don't have domain admin rights (or rights in SCCM), but you're still responsible for auditing for SQL Servers, then MAP doesn't help you.

As a security type, I can't guarantee I will have either scenario. If you're scanning your network, you should never assume you have access to all the hosts connected. If you do, you won't catch the system that shouldn't be there. You should definitely take advantage of a tool like MAP, but cover your bases - also use more generic scanning tools like nmap.

Thursday, August 30, 2012 - 7:17:10 AM - Fabrizio Faleni Back To Top (19308)

I can only agree with Robert de Held: with the MAP Toolkit you'll have a tool that not only will scan your network to find out SQL Server instances, but it will also inventory precious info such as whether the server is physical or is a Virtual Machine, it will give you the version of SQL Server and of the Operating System installed, the number of databases, the CPU Load, it will even recognize Oracle, SyBase and MySQL databases, even if they run on Linux/Unix OS!

At my knowledge the toolkit cannot be scripted yet, but there is a Forum to understand how to use it at best. It is a Solution Accelerator that can help us for licensing or consolidation purposes: so. Maybe it will not do what you need, but it is worth being proposed.

Resources are available here: http://technet.microsoft.com/en-us/library/bb977556.aspx

Fabrizio

Thursday, August 30, 2012 - 4:37:02 AM - Robert de Held Back To Top (19303)

I would just use the MAPS toolkit! its free flexible and does it all out of the box,   or query SCCM /SMS database on servers having SQL service running

Friday, August 17, 2012 - 10:24:14 AM - SQLPing3 Back To Top (19094)

If you're going to use nmap, then I'd suggest you look into wmic as an addition i.e.

wmic /node:"servername" service where (Caption LIKE "%sql%" or Caption LIKE "%OtherInterestingService%") get caption, description, Displayname,  PathName, Startname, Status

or

wmic /node:"ip address" service where (Caption LIKE "%sql%" or Caption LIKE "%OtherInterestingService%") get caption, description, Displayname,  PathName, Startname, Status

 

Additionally, you can get nmap to output in XML format directly (I use -oA which outputs in multiple formats), and then load it into SQL Server directly with something like:

DECLARE @InputXML XML

SELECT @InputXML = CAST(x AS XML)
FROM OPENROWSET(BULK '\\server\share\xml.xml', SINGLE_BLOB) AS T(x)

SELECT
   host.value('@starttime[1]', 'bigint') AS StartTime
  ,host.value('@endtime[1]', 'bigint') AS EndTime
  ,host.value('address[1]/@addr', 'varchar(128)') AS Address1
  ,host.value('(hostnames/hostname/@name)[1]', 'varchar(128)') AS Name1
  ,host.value('(hostnames/hostname/@type)[1]', 'varchar(128)') AS Name1Type
  ,host.value('(os/osmatch/@name)[1]', 'varchar(128)') AS OSMatchName1
  ,host.value('(ports/port/@portid)[1]', 'int') AS Port1ID
  ,host.value('(ports/port/@protocol)[1]', 'varchar(128)') AS Port1Protocol
  ,host.value('(ports/port/state/@state)[1]', 'varchar(128)') AS Port1State
  ,host.value('(ports/port/service/@name)[1]', 'varchar(128)') AS Port1ServiceName
  ,host.value('(ports/port/service/@product)[1]', 'varchar(128)') AS Port1ServiceProduct
  ,host.value('(ports/port/service/@version)[1]', 'varchar(128)') AS Port1ServiceVersion
  ,host.value('(ports/port/service/@ostype)[1]', 'varchar(128)') AS Port1ServiceOSType
  ,host.value('(ports/port/service/@method)[1]', 'varchar(128)') AS Port1ServiceMethod
  ,host.value('(ports/port/@portid)[2]', 'int') AS Port2ID
  ,host.value('(ports/port/@protocol)[2]', 'varchar(128)') AS Port2Protocol
  ,host.value('(ports/port/state/@state)[2]', 'varchar(128)') AS Port2State
  ,host.value('(ports/port/service/@name)[2]', 'varchar(128)') AS Port2ServiceName
  ,host.value('(ports/port/service/@product)[2]', 'varchar(128)') AS Port2ServiceProduct
  ,host.value('(ports/port/service/@version)[2]', 'varchar(128)') AS Port2ServiceVersion
  ,host.value('(ports/port/service/@ostype)[2]', 'varchar(128)') AS Port2ServiceOSType
  ,host.value('(ports/port/service/@method)[2]', 'varchar(128)') AS Port2ServiceMethod
FROM @InputXML.nodes('/nmaprun/host') AS X(host)
ORDER BY host.value('address[1]/@addr', 'varchar(128)')

Thursday, August 16, 2012 - 9:14:51 PM - K. Brian Kelley Back To Top (19087)

SQLPing3 is good if you can use a GUI. Same with Quest's Discovery Wizard. Chip did put out a command-line version of SQLPing3, but it'll sometimes crash unexpectedly. This is actually why I went back to nmap. I can automate the scans to run and then I can follow up with the reports to investigage and remediate.

Thursday, August 16, 2012 - 4:08:09 PM - SQLPing3 Back To Top (19077)

As with others, get permission from networking and security groups before running any of these tools.

 

Nmap is a general port scanning tool; as mentioned, named instances are important too and are by default on dynamic ports.  Related to this, SQL Server Express does not enable the DAC by default, and is often installed as one or more named instances.  Additionally, the networking group can tell you if there are subnets or networks you can't see from your PC.

For SQL in specific, SQLPing3 is a more dedicated tool, and has more ways of detecting SQL Servers.  Note that you should be careul; by default the "Brute force passwords" option is enabled, and you may not want that (if you really want to check password security, you need to get the hashes into an offline GPU based cracking tool with good dictionary and good rules).

 

Thursday, August 16, 2012 - 12:23:48 PM - K. Brian Kelley Back To Top (19074)

This is true of any scan, though. The purpose of the scan is to identify possible SQL Servers. Further investigation is always warranted. Sorry if I wasn't clear on this. It's a given on the security/auditing side. And if we do hit what we know is a false positive (which should be documented), we verify that it still is and move on.

Although, to be honest, if I can hit a print queue (printer) directly, that gives me pause. A good practice is to lock them down on separate subnets where they can only be hit directly by the print servers. This prevents an attacker from compromising and using a printer. This has been known as an exploit point for years:

http://www.schneier.com/blog/archives/2006/08/printer_securit.html

 

Thursday, August 16, 2012 - 11:49:19 AM - UrbNerd Back To Top (19073)

Even after doing an NMAP on an IP range you will find yourself in certain cases searching down empty rabbit holes. HP print server applications...Xerox etc etc use SQLite which in most cases use the default port of 1433 and you can find multiple occurances of SQL "instances" only to find out that they are printing queues. In our environment we typically change the default port upon initial installation in clusters due for security purposes. Beware of relying fully on the NMAP to give you all the information you need, because you may be loading a gun with blank bullets.

Thursday, August 16, 2012 - 9:04:37 AM - K. Brian Kelley Back To Top (19071)

For a default instance, SQL Server will always listen on tcp/1433 for a standard setup. Named instances, which were introduced in SQL Server 2000, allow for more than one instance of SQL Server to be present on a server. The assumption is that you'd install the first instance as a default instance. As a result, tcp/1433 would already be taken. Since there isn't a default port for instance #2, instance #3, etc., SQL Server picks one at random and then is willing to change if need be. That's why you scan udp 1434 to pick up the other TCP ports.

 

Thursday, August 16, 2012 - 8:39:32 AM - Rick Back To Top (19068)

The advisory on permission is not overdue, if you run tools like nmap on the network, network intrusion detection might go off, especially if you do not specify a port number.

 

One thing bugs me though - isn't SQL 2008 R2 and up set up to use a dynamic port instead of 1433? If so, just looking for connections on 1433 will nog be enough (and the warning is even more in place...)














get free sql tips
agree to terms