Find SQL Servers On Your Network with OSQL and SQLCMD

By:   |   Comments (3)   |   Related: > Tools


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.


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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author MSSQLTips was started in 2006 to provide SQL Server content about various aspects of SQL Server and other database platforms.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, February 5, 2013 - 9:48:50 AM - Jerome Back To Top (21924)

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 (10379)

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 1, 2010 - 2:12:59 AM - prem Back To Top (10001)
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

get free sql tips
agree to terms