Gotcha – Listening SQL Server Availability Group Listener on non-default port

While configuring SQL Server AlwaysON Availability Groups, as a part of security hardening practice it is recommended  to configure Availability Group to listen on a non-default port other than 1433.

However the moment you configure your Availability Group Listener on a non-default port and if you SQL Server instance is also configured to listen on non default port, you might observe that you are unable to connect to the AlwaysON SQL Instance using the availability group network name and you might receive the following generic error

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

However when you try to connect to the Availability Group Listener by specifying network name and the port no. in the format

tcp:<ag listener name>,<port>

you are able to connect to the Availability Group SQL Instance.

So, the issue is you are able to connect by specifying the port no. but unable to connect with specifying the port no. Any experienced SQL expert, would tell you are unable to reach SQL Browser service which provides the port resolution for instances running on non-default port on the server.

This is an expected behavior by design of the SQL Client Provider ( yes, you read it correct it is not due to design of Availability Group Listener please read on to understand why?)

For the sake of comparison between SQL Server FCI and Availability Groups , one might argue the following

With SQL Server Failover Clustering Instance (FCI) as well, we have sql server virtual name and virtual IP which is also registered as clustered resource, but we never experience any error in connecting to the SQL Instance when running on non-default port.

Again, to compare Virtual IP & SQL Network Name in SQL FCI with Availability Group Listener, one needs to understand that the design of Availability Group Listener ( it is  more than just clustered resource )

In SQL FCI, the SQL Network Name resource and Virtual IP only maps the IP to the active node of the cluster owning the SQL Instance resource groups in the cluster whereas Availability Group Listener is an application code (similar to SQL Browser service) itself which accepts the connection and provides the read-only routing for reporting workloads based on the Application Intent parameter of the connection string.

I mentioned Availability Group listener is an application code, so does it run as a separate process or service ??

Nope, the listener application runs as part of the sqlservr.exe process which is very well explained by Arvind Shyamsundar in his blog post

http://blogs.msdn.com/b/arvindsh/archive/2012/09/12/alwayson-listener-connectivity-details.aspx

As Availability Group Listener is an application code which accepts the connection first to perform routing, it requires a separate port no. which is not required by SQL FCI Network Name and Virtual IP which merely maps the Virtual IP to active node owning the SQL resource group which is performed by cluster service. In case of SQL FCI, the connection is accepted directly by SQL Instance and hence there is only one port involved in SQL FCI  which is SQL Instance port.

Now that, we understand the functioning of Availability group listener, coming back to the main question where we started, why did the client give error when connecting to Availability Group Network Name

When you specify only the Availability Group network name as the connection string, the client provider assumes it as a connection to the default instance ( This is  because the named instance connection has ‘\’ in the connection string which is missing when u connect to the Availability Group Listener)  and hence takes your connection to port 1433.  However if neither Availability Group Listener nor SQL Instance runs on the default port of 1433, you might receive the error reported above.

( This is the reason why I said above, the behavior is due to the design of the sql client provider and not Availability group listener)

Hence the connection to the Availability Group Listener never reaches SQL Browser and always bypasses SQL Browser service since it is never considered as connection to the named instance

And why port 1433 since that is what is specified as the default port in the connection properties of the SQL Native Client if you check your SQL Configuration Manager

To conclude,

Consideration For running Availability Group Listener on non-default port:

When you are running the Availability Group Listener and SQL server instance on a non-default port, while making the client connection to the SQL Availability group listener you need to either specify the port no. in the connection string or use client side alias in order to successfully connect to the Availability Group Listener and leverage the read only routing. This is because connection to the Availability Group Listener never reaches SQL Browser and always bypasses SQL Browser service.

 

Hope this helps !!!

Parikshit Savjani
Premier Field Engineer

2 comments

  1. This problem is so frustrating. I install a named instance of 2012 on both nodes of a windows cluster and set the port to a non-default port. I configure for Always On, create the AG, and when I created the listener, I specify the non-default port and IP address reserved for the listener and all is created with no problems. However, when I connect to the listener via SSMS and expand the database section I see the AG databases from the default instance, not the named instance. I have been through a dozen blogs and have not found a resolution to this issue. Any help would be greatly appreciated. Thanks.

    1. I was able to get this working. The issue wasn’t with the listener, the issue was with my connection to the listener in SSMS. Apparently even though the listener for the named instance was created on the AG for the named instance, you must still specify the port in your SSMS connection or it will look at the default port, 1433. Thanks.

Leave a comment

Your email address will not be published. Required fields are marked *