Learn more about SQL Server tools


Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories


Find SQL Server Instances Across Your Network Using Windows PowerShell

By:   |   Read Comments (7)   |   Related Tips: More > PowerShell


As more and more SQL Server instances get installed in my organization by different departments in both servers and desktops, I need to find out where they are as I'll be responsible for managing and administering these instances. How do I list out all the SQL Server instances across my entire organization without even knowing where they are?


You may have probably heard about the term SQL Server sprawl - the uncontrolled and uncoordinated deployment of different SQL Server instances of different editions. It's easy to track Enterprise and Standard Edition instances since we regularly do audits for licensing purposes. But, then, there are third-party and line-of-business applications that use SQL Server Express as their backend database that get installed by people from different departments of your organization. Who knows whether these SQL Server instances sit on their desktops or on servers allocated by the IT department? And the worst part is, we DBAs get charged for administering them.

You may have used tools like SQL Ping and a bunch of other stuff in the past, some of which are highlighted in this SQL Server Central article, to discover SQL Server instances in your network. The good news is, your nightmare is about to end. Since Windows PowerShell runs on top of the .NET Framework, it can leverage any .NET Framework-based assemblies available on the machine that it is running on top of (this includes having SQL Server 2005 Client Tools with Server Management Object (SMO) to administer SQL Server instances). For this particular case, you don't even need to have SMO installed on your client machine to enumerate SQL Server instances across your organization as the .NET Framework already includes ADO.NET 2.0. ADO.NET contains the System.Data.Sql namespace which contains classes that support SQL Server-specific functionality. One of these classes is the SqlDataSourceEnumerator which can be used for enumerating all available instances of SQL Server within your local network, even if the SQL Server service is stopped. The SqlDataSourceEnumerator class has the Instance property that gets an instance of the SqlDataSourceEnumerator, which can then be used to retrieve information about available SQL Server instances. A method called GetDataSources retrieves a DataTable containing information about all visible SQL Server instances in the network from SQL Server 2000 onwards.

And this really highlights the power and simplicity of Windows PowerShell. This task can be done in a single line of code, using the information mentioned above.


A method called GetDataSources retrieves a DataTable containing information about all visible SQL Server instances in the network from SQL Server 2000 onwards

See, that wasn't so hard, was it? The default output of the script will display the four major properties - ServerName, Instancename, IsClustered and Version. You can have an instant documentation by piping the results in a text file for later review. To know more about the different properties and methods available to you from the object returned by the script, you can use the Get-Member cmdlet.

[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources() | Get-Member

one is a default instance while the other one is a SQL Server Express instance - and a SQL Server 2008 named instance

You'll see that in my test environment, I have a Windows 7 machine running two SQL Server 2005 instances - one is a default instance while the other one is a SQL Server Express instance - and a SQL Server 2008 named instance. You'll also see a Windows Server 2003 with two SQL Server 2005 instances and, looking at the Version property, one is not patched at all. While the Windows 7 machine is a member of an Active Directory domain, it is interesting to note that the Windows Server 2003 machine is not. This means that even machines in a Workgroup configuration can be discovered for SQL Server instances even if the account that you are using to run the script does not have any permissions at all on the remote machine.


I may have overstated the fact that this is really very easy to do. However, the reason that it appears to be very easy is because of two things that I have done. First, the SQL Browser service is started. The SQL Browser service is the one making it possible for the discovery of SQL Server instances on a computer. The challenge here is that the SQL Browser service is not started by default. So, how do we start it? And how do we even know that it exists on a specific machine? There are a couple of ways to do that. One of which is by using the Invoke-Command cmdlet available in Windows PowerShell v2.0. This cmdlet can be used to run commands on local or remote computer. The assumption here is that the target computer also has Windows PowerShell v2.0 installed and that Windows PowerShell Remoting is enabled on all the target computers. As this is beyond the scope of work for the DBA, I will not cover PowerShell Remoting in this tip.

Another option is by using the Get-Service cmdlet which can assist us in this task. You can run the Get-Service cmdlet to query for the SQL Browser service on a computer and, if it is not started, force it to start using the Start-Service cmdlet. Both of these cmdlets do not require Windows PowerShell on the remote computer

Get-Service | Where-Object {$_.Name -eq "SQLBrowser"} | Start-Service

 run the Get-Service cmdlet to query for the SQL Browser service on a computer

Since you won't be dealing with just a handful of computers, it's best to work with your domain administrators to write a script to enumerate servers and workstations in your Active Directory domain for this purpose and pipe the results to the script (it's one of those reasons why you should be in good terms with your systems administrators). If you are running Windows Server 2008 R2 in your environment, the Active Directory PowerShell cmdlets can make this task even simpler.

The second reason why this task seem so easy is that the Windows Firewall has been configured to allow the SQL Browser service to accept inbound connections. Starting with Windows XP Service Pack 3 and Windows Server 2003 Service Pack 1, Windows administrators have had to deal with the Windows Firewall to allow services to function as expected in the local network. If you are simply running this script once every month, you can ask your domain administrators to disable the Windows Firewall via Active Directory Group Policy (and yet another reason why you should be in good terms with your domain administrators). A screenshot of this Group Policy setting in Active Directory is shown below, making it easier to disable the Windows Firewall on all the computers in your organization.

Windows Firewall has been configured to allow the SQL Browser service to accept inbound connections
Next Steps

Last Update:

About the author
MSSQLTips author Edwin Sarmiento Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

View all my tips
Related Resources

More SQL Server Solutions

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    Notify for updates 

Get free SQL tips:

*Enter Code refresh code     

Tuesday, May 17, 2016 - 9:44:52 AM - Andre Grootveld Back To Top

I get the same issue as I have in my old script: it will only return the instances within the same VLAN (same subnet mask).... Anyone a solution for that?


Thursday, August 22, 2013 - 6:23:24 PM - bass_player Back To Top

This is dependent on the SQL Browser service. Thus, if the service is not running or you have firewall rules in place that prevent this service from being broadcasted across your network, it may not work

Thursday, August 22, 2013 - 3:59:34 PM - Ken Back To Top

we are having a problem with the below powershell script not pulling back all of our instances of SQL Server any help would be greatly appreciated 



$SQLServers = [System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources() | 



        $instance = $_.ServerName

        write-host $instance



Wednesday, March 13, 2013 - 9:10:24 AM - Mark C Back To Top

Why does it not list SQL Servers install on a Virtual Server?

Tuesday, December 11, 2012 - 3:23:19 AM - Hans Brouwer Back To Top

The lines of codes given result in error messages on a Windows XP pc. Are these lines only valid for Vista of W7 or other? A specific .NET level?

Wednesday, September 19, 2012 - 9:28:47 AM - bass_player Back To Top

If you are seeing at least a server name, it means that the SQL Server instance is a default instance. If you are not seeing anything at all, check your SQL Browser service if it is running on all of the SQL Server instances & check if the Windows Firewall is allowing traffic to and from the SQL Browser service

Tuesday, September 18, 2012 - 9:53:33 PM - Hendra Back To Top

Hi there,

I try to find the SQL Server Name including the Instances Name, but no luck, the result is only show the SQL Server name. What happens? Could you advice me. Thanks

FYI, I'm using Ms. Windows 7 64bit and PowerShell v.2 (that already part of Ms. Windows 7)

Learn more about SQL Server tools