Find out all SQL Server 2005 installations on our network

Question

Post reply

Find out all SQL Server 2005 installations on our network

j mangual

Ten Centuries

Points: 1197
More actions
August 14, 2007 at 6:08 am

#176273

I need to verify how many servers have SQL Server 2005 installed on our network due to licenses issues. Can anyone have any ideas or suggestions of how to accomplish this task? Thanks in advance.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

bledu SSChampion Points: 12335 More actions · Answer 1

osql -L

you can pipe the output to a text file

osql -L > c:\sqlservers.txt

you can also use SMO objects with .net and do some fancy stuff there

Everything you can imagine is real.

Joe Clifford SSCrazy Eights Points: 8739 More actions · Answer 2

Check out SQLRecon, it will "crawl"/scan for SQL Server instances and report any that it can find including version, etc. Be sure to talk to your network folks before turning it loose on a corporate network though.

http://www.specialopssecurity.com/labs/sqlrecon/

Joe

Omri Bahat-433962 SSC Eights! Points: 901 More actions · Answer 3

Just wanted to plug in my 2 cents:

osql and SMO scanning rely on successful UDP communications on port 1434 to the servers and on various (non-user-configurable) timeout parameters. In most cases of interest, while they will obtain a list of servers and instances on the domain, they will not provide a complete report of servers and instances due to technical limitations. Typical cases include:

- Cross domain issue (even in the same network)

- Firewalls (UDP 1434 is known to be a highly vulnerable port and is shut down in many firewalls)

- Slow replies (in many cases the timeout will expire before the remote reply from the server)

- Disconnected environments (i.e., cross-networks, getway communication issues, and so on)

etc.

SQLRecon- never heard of it before, but after reading the description it does look interesting.

The only way to get a reliable list is to scan the network. You can look vulnerability scanners on google (Nessus & Qualys are two leading brands of scanners; there are plenty of others).

Hope this helps.

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 4

SQLRecon is the way to go. To get the best bang out of it you will need a Domain Admin to run it.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 5

I saw a script here before (when I had to do the same search as you are doing) that used vbscript and WMI to scan the network looking for SQL Server service name. Assuming all the machines you are interested in allow remote WMI (by default, they do by admins), it was a much more effective way to search instead of using the port 1434 that osql or SMO uses. If I remember right, it even got the version based on the sqlservr.exe file version, can scan non-default instance names (by using wildcard searches), and worked whether or not SQL Server was actually running (since it searched for service name installed). t could not get the licensing of the SQL Server installation, so you don't know whether it is Enterprise, Standard, or even Express. But once you know where to look, you can use SMO or query windows to connect and get that info.

Hope this helps

Mark

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 6

Mark SQLRecon does the WMI thing ... and AD and port 1433/1434 and tells you the complete version and if I remember even checks for blank sa passwords !

It needs Domain Admin type authority to do all that I mentioned if not it just does the port discovery thing more or less ...

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

j mangual Ten Centuries Points: 1197 More actions · Answer 7

I was able to run the SQLRECON software and create a spreadsheet with the information. However, some of the clients are showing a MSSQLSERVER installation with no base version nor SSNETLIBVersion. Are these MSDE installations? I need to find out of all the installations found which are MSDE or SQLEXPRESS since we they do not require license fees($$$). Based on an article on the microsoft site anything with base version 8.xx.xx is a SQL Server 2000 installation and with 9.xx.xx is SQL Server 2005 but I can't find the versions associated with an MSDE or SQL Express installation.

j mangual Ten Centuries Points: 1197 More actions · Answer 8

By the way thank you Joe Clifford for your recomendation.

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 9

You will need Domain Admin authority or someone who has it iun order to get that information.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Joe Clifford SSCrazy Eights Points: 8739 More actions · Answer 10

Hmm... one thought would be to take your list of sql servers and compare/mix it with an export from your list of computers/servers from active directory. Depending on your arrangements with MS you're probably only on the hook for production instances of SQL server, not development, standby, staging, etc.

Once upon a time I remember seeing a list that equated the last digit of the version number to a specific version of SQL server but I don't think it was reliable. Worst case you may need to crawl run up and down your domain and get the server properties - then again, maybe SSMS, MOM, openview or other management tool is already installed in your environment?

Steve Jones - SSC Editor SSC Guru Points: 736479 More actions · Answer 11

You might be better off with taking your list and running a "connect" to each one as a domain admin (should work) and getting the version. I think that's what I had to do before.