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

Question

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

Sakthivel Chidambaram

SSCertifiable

Points: 5531
More actions
June 11, 2008 at 10:19 am

#192077

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sakthivel Chidambaram SSCertifiable Points: 5531 More actions · Answer 1

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

Perry Whittle SSC Guru Points: 233821 More actions · Answer 2

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" 😉

K. Brian Kelley SSC Guru Points: 114562 More actions · Answer 3

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
@kbriankelley

Sakthivel Chidambaram SSCertifiable Points: 5531 More actions · Answer 4

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

pg53 Ten Centuries Points: 1303 More actions · Answer 5

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