Post reply

Find out all SQL Server 2005 installations on our network

  • 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.  

  • August 14, 2007 at 8:15 am

    #726787

    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.

  • August 14, 2007 at 11:03 pm

    #726961

    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

     

  • August 15, 2007 at 6:00 am

    #727007

    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.

  • August 15, 2007 at 8:27 am

    #727051

    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."

  • August 15, 2007 at 11:27 am

    #727131

    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

  • August 15, 2007 at 12:14 pm

    #727141

    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."

  • August 20, 2007 at 3:32 pm

    #728142

    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.

  • August 20, 2007 at 3:33 pm

    #728143

    By the way thank you Joe Clifford for your recomendation.

  • August 21, 2007 at 8:16 am

    #728307

    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."

  • August 21, 2007 at 9:10 am

    #728340

    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?

     

  • August 21, 2007 at 9:54 am

    #728368

    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.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

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

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