Post reply

Is there a way to find the SQL port for a server remotely

  • March 2, 2011 at 8:35 am

    #388950

    Hi all

    I have to undergo the tedious exercise of finding out the SQL port for every instance of SQL in our estate - tedious because i can't figure out a way to do this without having to log onto each box and checking the port through configuration manager.

    Is there something clever I can do to gather all this info from my local machine??

    Thanks!

    Doodles

  • March 2, 2011 at 8:53 am

    #1293562

    Do you change all the ports on your SQL Server installs or do you leave them as standard (1433, 1434)?

  • March 2, 2011 at 9:14 am

    #1293576

    I'm new to the company but my assumption is most are on the default ports but there will be a handful that have been changed...

  • March 2, 2011 at 9:26 am

    #1293584

    SQL Recon is a free utility that will help you find all installations on a network; other than that, it's reading the registry in multiple places on multiple machines, maybe via powershell...makes it a lot harder.

    I've got a TSQL that reads the registry for 2005 and below; never updated it to read the new position for 2008 or R2.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 2, 2011 at 9:27 am

    #1293585

    Your best bet will be SQL Ping.

    http://www.sqlsecurity.com/Tools/FreeTools/tabid/65/Default.aspx

    Chris

    Learning something new on every visit to SSC. Hoping to pass it on to someone else.

  • March 2, 2011 at 9:33 am

    #1293588

    The quest discovery tool (freeware) has always worked pretty well for me.

    http://www.quest.com/discovery-wizard-for-sql-server/

    If you have access to the instance, it will grab all the db info if you want as well.

    Also has powershell cmdlets, if you want to script it and schedule scans.

    Chris

  • March 3, 2011 at 9:46 am

    #1294112

    That's brilliant - thanks guys.

    I tried the Quest tool but when i scan it only seems to pick up one server! Not sure why but I didn't have too much time to play so moved onto the SQLPing tool and that works a treat.

    Thanks for responding! 🙂

    Doodles

  • March 3, 2011 at 1:21 pm

    #1294300

    If you are using a query tool that can connect to pultiple instances, such as SQL 2008's SSMS or RedGate's MultiScript (there are several others), you can issue the following query to get the port through which you connected:

    select local_tcp_port from sys.dm_exec_connections where session_id = @@spid

    The following query looks like it would work, but seems only work properly in stand-alone (non-clustered instances) I tried:

    select port, is_dynamic_port from sys.tcp_endpoints where is_admin_endpoint = 0

    Eddie Wuerch
    MCM: SQL

  • March 7, 2011 at 7:10 am

    #1295317

    This is great, thanks!! I can run it fine through Mgmt Studio CMS.

    Thanks again

    Doodles

Viewing 9 posts - 1 through 8 (of 8 total)

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