How to list all the instances and the port they are listening

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » How to list all the instances and the port...

How to list all the instances and the port they are listening

Rate Topic

Display Mode

Topic Options

Author

Message

Sakthivel Chidambaram

Posted Wednesday, June 11, 2008 10:19 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, March 21, 2013 5:40 PM
Points: 309, Visits: 807

Hi,
We have a requirement. One of our application called Discoverer will scan through the network and list the inventory of SQL Servers. But the problem is it has only option to enter the port for scanning and no option to type instance.

so if we give port as 1433 and scan the network, it misses the servers which don't have
1) Default Instance.
2) Dynamic Port is used.
3) If Instance/Server is listening in different port.

Can someone guide me on this please? Any idea to discover the list of instance and which port they listen when we specify the hostname or just specify the IP Range..? Hehe

Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc

Post #515342

Sakthivel Chidambaram

Posted Thursday, June 12, 2008 8:50 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, March 21, 2013 5:40 PM
Points: 309, Visits: 807

Or is there any T-SQL Query to find on which port a particular instance is running? I checked in sysrpocessed for one SPID it shows which protocol it has used to connect (TCP/IP or Named..) but it does not show which is the port used...

Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc

Post #515994

Perry Whittle

Posted Thursday, June 12, 2008 11:32 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:33 PM
Points: 5,201, Visits: 11,150

done this already, use xp_regread to read the values from the registry and output

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" Wink

Post #516125

K. Brian Kelley

Posted Thursday, June 12, 2008 11:34 AM

Keeper of the Duck

Group: Moderators
Last Login: Wednesday, May 08, 2013 5:14 AM
Points: 6,583, Visits: 1,787

You want to use SQLPing. Have a domain admin run it.

SQLSecurity.com Free Tools

The registry option only works if you know to connect to the SQL Server. SQLPing will use all known means of identifying SQL Servers, including those which are uninstalled but not running at the time.

K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog |

LinkedIn | Twitter

Post #516128

Sakthivel Chidambaram

Posted Friday, June 13, 2008 1:00 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, March 21, 2013 5:40 PM
Points: 309, Visits: 807

Hi Brian Kelley,
Thank you so much for your reply! Just Y'day i got this site through googling... and found that application is really usefull... Anyways thank you so much for your help!!! :)

Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc

Post #516454

pg53

Posted Thursday, September 08, 2011 4:30 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, April 18, 2013 5:28 AM
Points: 68, Visits: 212

Hi
Code to do this in VB. (I think this came from a help file somewhere)

Function FindServers() As DataTable
Dim instance As System.Data.Sql.SqlDataSourceEnumerator = System.Data.Sql.SqlDataSourceEnumerator.Instance
Dim dataTable As System.Data.DataTable = instance.GetDataSources()
Return dataTable

End Function

HTH

pg

Post #1171619

« Prev Topic | Next Topic »

Permissions