More dbatools–Get-DbaTcpPort

After the MVP Summit, I was excited to check out more code and features in all aspects of SQL Server, but since I’d had a quick conversation with Aaron Nelson and Chrissy LeMaire, I decided to tackle another part of dbatools.

BTW, they gave me a cool sticker.

Photo Nov 11, 5 05 19 PM

I like using PoSh for some tasks, especially when I don’t have an easy way to do something in SSMS or want to run a task across a variety of instances. In this case, as I glanced through the September updates, I found a good one.


I don’t love the mixed naming, and I’ll get used to it, but I do love the autocomplete in PoSh. I can type this:

2016-11-11 17_13_13-powershell

When I hit tab, I get this:

2016-11-11 17_13_19-powershell

So I don’t need to worry about the case, but I can easily use Get-DbaTcpPort. This is one of those quick things I’ve done more than a few times as I troubleshoot connectivity with VMs or new instances. I have learned I can get this from the error log, but that’s a pain.

The main thing I can do here is pass in the SqlServer parameter and get a port. Here’s the ports for an instance on this machine:

2016-11-11 18_06_24-powershell

There is a –Detailed option, which gives me the server name and cleaner results.

2016-11-11 18_07_57-powershell

I was expecting the result of whether this was static or now, but it’s not appearing. I see the warning, but am not sure why this doesn’t work. Apparently this works with SqlWmi, but I’d like to know more to troubleshoot things.

When I hit one of my other instances, I got this:

2016-11-11 18_07_02-powershell

At first I thought this was my remote admin connections setting, which was disabled, but that wasn’t it. I found instead that I’d left TCP disabled by default on this instance. Once I changed that and restarted the instance, it worked fine.

2016-11-11 18_14_14-powershell

This isn’t something I’d use often, but it’s a really quick way to check on the status of an instance port if you need to. I’d urge you to give it a try and see what you think.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

6 Responses to More dbatools–Get-DbaTcpPort

  1. Hey Steve,
    Thanks for the write-up! SQL WMI is indeed a beast. When running this command against local instances, you need to Run As Administrator. Running as a regular user works well for remote servers.

    Initially, an informative exception with the RunAs information was thrown but was subsequently replaced with a generic exception message. I have fixed this bug and it is scheduled for this weekend’s release 🙂


  2. Chuck says:

    There actually is an easy way to get this in SSMS (or any query tool).

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


    • Yep! That’s actually the exact query that’s executed for non-detailed port information. The benefit that I love about PowerShell is that you can execute this SQL easily on multiple machines at a time. Here’s how I get all the tcp port information across my estate (using Registered Servers)

      Get-SqlRegisteredServerName -sqlserver sql2014 | Get-DbaTcpPort

      The other benefit is that it’s easy to find corresponding commands, instead of having to search for the T-SQL on a regular basis. So you can do a Get-Command *dba*tcp* or a Get-Command *dba*port* (here’s hoping the asterisks work) and see all of the commands related to tcp or ports.


  3. way0utwest says:

    Ah, didn’t remember there was a query that could be used. Now that I see it, I’m sure I’ve seen that. However, if I’m not connected to the instance, I think the PoSh tool is a great way of finding the port quickly.


  4. Pingback: Securing Your Instances | Voice of the DBA

Comments are closed.