Find SQL Server Instances Across Your Network Using Windows PowerShell

By:   |   Comments (14)   |   Related: > 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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

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.

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

Monday, December 6, 2021 - 7:56:05 PM - bass_player Back To Top (89537)
Did you:

1) Enable the SQLBrowser service on the machine where the SQL Server instance is running?
2) Allow inbound traffic to your Windows Firewall for the SQLBrowser service?

Friday, December 3, 2021 - 6:30:03 PM - Thomas Hotvedt Back To Top (89524)
I ran the powershell command [System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources() on a untility server that is running Central Management Servers, but it yields no results. I also ran Enable-PSRemoting.

I am very new to Powershell.

Tuesday, March 3, 2020 - 9:00:05 PM - TrixM Back To Top (84911)

This article should really be updated with better advice re handling the firewall.

It should not be disabled outright these days. Simply have a rule that allows inbound connections to UDP port 1434. It's likely that SQL Server setup will enable this port for you if you enable the SQL Browser service

Monday, December 3, 2018 - 12:15:37 PM - Shomari Matsuura Back To Top (78397)

Helped, thank you.

Monday, March 13, 2017 - 11:43:49 AM - bass_player Back To Top (50985)

 Glad that this tip helped.

Monday, March 13, 2017 - 6:59:18 AM - Tom Dent Back To Top (50967)

 Exactly what I needed, thank you!!!


Thursday, December 22, 2016 - 8:03:47 AM - Keith Truesdell Back To Top (45032)

This is awesome and exactly what I need!

A few situations in my current environment that I have also heard about in many other places, which add to the "problems" section.
First is that when I started, everyone coveted their little area of IT, including the databases.  I would be told to get projects done and connect to certain databases, but no one would tell me what that database was, where, the server, or even give me an ID to connect to it. The manager didn't truly know these servers or databases either and only cared about completing projects, so he would instead waste an hour talking to us about how we have to share and then send us out only to have myself in the same situation without an ID or place to start.  With Oracle and AS400 and other servers I found other tools to get what I needed and eventually hacked at some code that would help me find a few of those things.  This helps with the MS SQL server side of that environment.
Second, is after a few years my place is switching all over to MS SQL Server and we have 3 or 4 server admins spinning up these servers without telling everyone else.  We also have some high level managment coming in and saying we need Product X which sits on top of a MS SQL Server and no one knows about it besides the server admins (and by server admins, I mean VM Ware type server admins, not DBAs...they setup the server and get the initial install of MS SQL Server with the license applied and leave it either up to the contract company for Product X or someone else to do any more details). And what eventually happens is that we end up getting requests to connect to these places and we dont' know where they are.



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

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

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

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

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

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

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

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)

get free sql tips
agree to terms