By: MSSQLTips | Comments (3) | Related: > 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):
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
About the author
MSSQLTips.com 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
