Is there any way I can correctly identify all instances of SQL server
in my environment? We are using SMS 2003 for inventory.

I need to be able to differentiate between an actual SQL server
serving a DB, a workstation just running admin tools, an MSDE
installation, or a SQL Express installation.

Unfortunately, sqlservr.exe appears to be installed for all these
instances so I can't use that as a flag file. Add Remove programs is
another option, and that appears to identify The SQL Express version,
but does not appear to differentiate between the Server, the admin
tools install, or MSDE. The same goes for the service name.

The purpose of this is to reconcile our license counts in case of
audits. You'd think a large corporation could keep better track of
this stuff, but its kind of the wild west out here, and we're just
starting with putting up the barbed wire. I've worked a couple of
large organizations and haven't found one yet that does a good job of
this.

This it will have to something that can automated to process 100s or
even thousands of servers and workstations in our enterprise,
preferably using SMS to gather the data in some form. If necessary
I could use powershell , WMI or some vbscript, but I need something
to key on.

> Is there any way I can correctly identify all instances of SQL server
> in my environment?

There is no 100% reliable way. Most methods use the same technique to
"poll" workstations / servers in the network to check if SQL Server is
running. Unfortunately, various factors can inhibit the ability to do so...
port 1434 is closed (on individual machines, or network-wide via firewall),
some instances may be marked as hidden, some instances may not respond in
time, etc. etc.

Assuming none of these will actually be serving 24x7 production services, a
surefire way to figure out if a running SQL Server process is required by
users in your network is to take the service offline for 24 hours (or until
someone complains). :-)

Sorry I don't have a more foolproof automated way, but sadly, no such thing
exists.

A

Quest has a free tool called Quest Discovery Wizard.

There is another tool called SqlRecon, slow but works.
http://www.specialopssecurity.com/labs/sqlrecon/

Nothing I know will tell the difference between MSDE and regular SQL.
If you know a good VB programmer with WMI knowledge that might !

The Quest tool will at least give you a base line of what machines to
hit.