Find SQL Servers On Your Network with OSQL and SQLCMD

By: Edgewood Solutions | Read Comments (2) | Print Edgewood Solutions is a technology company focused on Microsoft SQL Server and founder of MSSQLTips.com. Related Tips: More

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 like Ethereal you can see that the OSQL application sends a service announcement on port 1434 (UDP):

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):

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:

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

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

Related Tips: More | Become a paid author

Last Update: 11/17/2006

Share:

Share

Tweet

Free SQL Server Learning The SQL Server Security THREAT - It’s Closer Than You Think Tutorial - SQL Server Performance Tuning and Monitoring

Comments and Feedback:

Sunday, August 01, 2010 - 2:12:59 AM - prem	Read The Tip
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

Friday, November 19, 2010 - 9:54:00 AM - George Squillace	Read The Tip
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.

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

"SQL doctor is the best SQL product on the market, by far. All of Idera's tools are great, but this is the icing on the cake!" Time-strapped database professionals use SQL Monitor to look after their servers. Try it online. SQL Servers keeping you up at night? Contact the Edgewood SQL Server consultants for a Health Check. Find and Fix SQL issues with Foglight Performance Analysis. Get a free copy. Join the over million SQL Server Professionals who get their issues resolved daily. Are you waiting on SQL Server? Learn about these DMV's.