Share via

alwayson listener connection granted regardless specified listener port

Jiong AN 146 Reputation points
2021-10-24T08:37:19.947+00:00

My sql server service is running on default port 1433. set up alwayson AG listener on port 12345. Interesting thing is when client connection via listener, both ports 1433 and 12345 are granted. Question is should listener only allow connection through port 12345? where 1433 should only allowed by connection the server hostname service directly.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,879 questions
Windows Server Clustering
Windows Server Clustering
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.Clustering: The grouping of multiple servers in a way that allows them to appear to be a single unit to client computers on a network. Clustering is a means of increasing network capacity, providing live backup in case one of the servers fails, and improving data security.
962 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,563 questions
0 comments
Accepted answer
  1. CathyJi-MSFT 21,096 Reputation points Microsoft Vendor
    2021-10-25T07:22:31.333+00:00

    Hi @Jiong AN ,

    > When u trying to connect through the listener name, you only allowed by the specifed port number right?

    Yes, you are right. I also mentioned this in my first reply.

    I did a test to help you understood your issue.

    Server A AG primary replica Instance number port number 1433
    Server B AG secondary replica instance number port number 36888

    AG listener DNS name---AGlistenertest AG listener port number 12345

    1. Server A is primary replica, server B is secondary replica.
      1)Using AGlistenertest,12345, the connection is successful.
      2)Using AGlistenertest,1433, the connection is successful.
    2. Failover the AG primary replica from server A to server B. Then the new primary replica is server B.
      1)Using AGlistenertest,12345, the connection is successful.
      2)Using AGlistenertest,1433, the connection failed.
      3)Using AGlistenertest,36888, the connection is successful.

    When you successfully connected, it happened that the port number of the primary replica SQL instance port is 1433. We can use the primary replica instance port number to connect, but if AG failover, you need to change the port number in the connection string to the port number of new primary replica port number, but if you use AG listener port number, no matter which replica is primary replica and how the port number of the instance is changed , it will not affect the connection of you AG.

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments

5 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Limitless Technology 39,421 Reputation points
    2021-10-28T09:35:39.003+00:00

    Hello JiongAN,

    Thank you for your question.

    When configuring an availability group listener, you must designate a port. You can configure the default port to 1433 in order to allow for simplicity of the client connection strings. If using 1433, you do not need to designate a port number in a connection string. Also, since each availability group listener will have a separate virtual network name, each availability group listener configured on a single WSFC can be configured to reference the same default port of 1433.

    You can also designate a non-standard listener port; however this means that you will also need to explicitly specify a target port in your connection string whenever connecting to the availability group listener. You will also need to open permission on the firewall for the non-standard port.

    --If the reply is helpful, please Upvote and Accept as answer--