SQL Connections – Finding what fixed port SQL is listening on

— by Lori Brown  @SQLSupahStah

While trying to add a database to an existing AlwaysOn Availability Group (AG), I was having difficulty making a connection from the primary replica to the secondary replica.

The availability group was functioning but as I tried to add a database to it, my connection to the secondary replica was being rejected with the following error:

That was interesting since I know the replica is up.  So I also tested connecting from the primary to the secondary from Management Studio with the same result.  Normally when I see the message above I would immediately solve my connection issue by turning on the Named Pipes protocol in the Configuration Manager.  However, in this case the AG was functioning so it had to be communicating without Named Pipes being necessary and I did not have a maintenance window to restart SQL since this is a 24×7 shop.

I decided that there must be a port that SQL is using that is not the normal 1433.  So, I found the sys.dm_tcp_listener_states dmv that gave me the info that I was looking for.  In the below table, I could see that the AG listener was using port 5022 as it should and SQL was using 1434 and another port.  That had to be the port I was looking for!

SELECT * FROM sys.dm_tcp_listener_states

Since I had just taken over these boxes, I had no idea that they were not listening on the regular port so I confirmed my findings in the properties of the TCP/IP protocol for the instance.

When TCP Dynamic Ports has no value, SQL will listen on the TCP port you fill in for it.

Once I connected to the secondary replica in management studio, I was then able to connect to the secondary replica in the Add Database wizard for AlwaysOn.

More info on troubleshooting and setting ports can be found at:

sys.dm_tcp_listener_states – https://msdn.microsoft.com/en-us/library/hh245287.aspx

Configure a Server to Listen on a Specific TCP Port – https://msdn.microsoft.com/en-us/library/ms177440.aspx

Troubleshoot AlwaysOn Availability Groups Configuration – https://msdn.microsoft.com/en-us/library/ff878308.aspx

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!