Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Find SQL Servers On Your Network with OSQL and SQLCMD


By:   |   Last Updated: 2006-11-17   |   Comments (3)   |   Related Tips: More > Tools

Problem

A common problem noted among database managers and network administrators is the number of unauthorized SQL instances on a network. Without proper application of service packs and hotfixes these instances create holes through which hackers and others can exploit the database infrastructure. Another situation you may find yourself in is needing to connect to a server instance whose name you can't recall. In either case you can find what you need by using OSQL or SQLCMD.

Solution

OSQL is a command-line utility that can be used to query a database and perform other administrative tasks. It can be accessed on one server and allow you to issue commands to another directly through a command prompt, SQL Server Agent job steps, or batch files. To check the instances currently installed on the network, open a command prompt and enter OSQL -L or OSQL /L. The OSQL application listens for other SQL Servers on the both the Named Pipes and TCP/IP. Through a packet "sniffing" application where you can see that the OSQL application sends a service announcement on port 1434 (UDP):

View of the service announcement during OSQL Server list command as captured by Ethereal

Once the list of servers is returned you will notice that there are two listings for each server-one for Named Pipes and one for the servers listening on the TCP/IP ports (when the network interface is disabled only the instances on Named Pipes will be returned):

Server list when network interface is enabled

SQLCMD is another command-line utility included with SQL Server 2005 that replaces OSQL. There are some subtle differences between OSQL and SQLCMD, particularly as it relates to using the /L switch. The first difference is that it produces a cleaner list, naming an instance only once in the list of servers. It also sends numerous service advertisements prior to returning the list, as noted in this screenshot:

View of the service announcement during SQLCMD Server list command as captured by Ethereal

To find a list of SQL instances currently installed in your environment, open a command prompt and type SQLCMD /Lor SQLCMD -L:

Comparison of OSQL returned list of servers vs. SQLCMD returned list
Next Steps
  • Based on research, if SQL Servers are configured to listen on ports other than the default then they won't be included in the list
  • Capture the "rogue" instances on your network by using OSQL and SQLCMD and protect your database infrastructure
  • Review information on the OSQL Utility for SQL Server 2000 and the SQLCMD utility for SQL Server 2005
  • Check out other free tools for SQL Server
  • Look at other Command Line Tools for SQL Server


Last Updated: 2006-11-17


get scripts

next tip button



About the author
MSSQLTips author Edgewood Solutions Edgewood Solutions is a technology company focused on Microsoft SQL Server and founder of MSSQLTips.com.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, February 05, 2013 - 9:48:50 AM - Jerome Back To Top

I'm currently trying to retrieve the sql instances on my cluster with this tool but I met some trouble..

At the beginning, theses tools worked fine but after a will (few hours), they could'nt retrieve anything... When I connect to the other node of my cluster, the tool can retrieve all of them. Do you have any idea of this comportement?


Friday, November 19, 2010 - 9:54:00 AM - George Squillace Back To Top

What happens when someone installs the "Windows Internal Database"? It seems that neither OSQL nor SQLCMD arrive with that installation, and I can't use OSQL -S nor SQLCMD -S (with -L) to interrogate remotely?

So, I also don't know what the instance name is on the machine with the Windows Internal Database.


Sunday, August 01, 2010 - 2:12:59 AM - prem Back To Top
if i execute osql -L it is not displaying  a instance which is in the list..... is that due to fire wall or port issue or shud i add anything in sql to get that listed?????? Help me out on this

Learn more about SQL Server tools