I was having a Twitter conversation on this yesterday and naturally Aaron Nelson (blog | twitter) popped in with "PowerShell!"  I like PowerShell, don't get me wrong, but there are a few non-intuitive ways to check for SQL Server that likely are a little tedious to code in PowerShell. All three involve fingerprinting a network port, two of which are TCP (1433 and 2433) and one which is UDP (1434). Robert Davis (blog | twitter) popped in with the Microsoft Assessment and Planning (MAP) Toolkit, which is nice, too, but there is one catch: you have to be in a domain that trusts the account doing the scan (whether the same domain or a trusting domain). So finding "rogue" SQL Servers out there is out. I'm a security type, so I believe in the Pokemon approach of "Gotta catch 'em all!" As a result, there are a couple of tools I use when I need to scan and locate SQL Servers.

Note: All of these could be classified as hacking tools. So before you use any of them, ensure that you have the permission in writing from someone authorized to give it allowing you to run any of these tools.

Chip Andrews' SQL Ping

The SQL Ping set of tools has been out in the field for a long time (if you don't get there, be patient and try back in a day or so, as his provider is switching things around). It uses all known methods of detecting SQL Servers. The list of what it does was actually provided in another tool, SQL Recon, also by Chip. SQL Recon only went 1 version and then Chip went back to SQL Ping. BTW, SQL Ping now also has a command line option, meaning you can automate the scans and then parse them into a database. This allows you to find deltas really quickly. One other word of advice is to get someone with domain admin membership to run this and the next tool, because then you can successfully scan the registry and services and hit WMI of all systems. These aren't the sort of rights DBAs have.

Quest Discovery Wizard for SQL Server

I don't tend to use this, but I'll list it because it is basically using the licensed pieces from Chip's tool (which I've used a lot), but has a nice GUI. If you're going to use just one tool, Quest Discovery Wizard for SQL Server is a good one, too.

Nmap:

Nmap isn't SQL Server specific. Rather, it's an overall network security scanner. As a security type, this was a common tool I used, especially when we saw some activity that didn't look right. I wanted to know what ports were open on a given system and I didn't always have rights to it. This is where Nmap comes in. Nmap does those sorts of scans in various and sundry ways. And it also has great fingerprinting capabilities meaning it doesn't just tell you the port, but based on information it collects back from the port scans, it can tell you what it finds out about what's listening on those ports. And that means identifying applications like SQL Server and even the operating system.

Parsing the Results:

Now, all of these can produce output in formatted files, and here is where Aaron's idea of using PowerShell has some great merits. I've been a Perl guy, so I wrote scripts way back when to do the imports I needed, but I can see myself re-writing all of these in PowerShell now. It'll be good practice. However, any scripting language will do. But the idea is to get the results, process them, store them, and then the next time, compare them. That tells you how the environment is changing and if folks are breaking the rules.