alwayson listener connection granted regardless specified listener port

Question

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.

Answer 1

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.

Answer 2

Hi @Jiong AN ,

You can configure port 1433, the default SQL Server instance port, for the Availability Group Listener. In that case, you don’t have to specify the port number explicitly in a connection string or client connection. If you specify a custom port, the client must explicitly specify the port. Refer to MS document Listener port.

>Question is should listener only allow connection through port 12345?

Yes, you are right. If you specify a custom port, then AG listener only allow connection through this port.

>where 1433 should only allowed by connection the server hostname service directly.

An availability group listener is a virtual network name (VNN) that clients can connect to in order to access a database in a primary or secondary replicas of an Always On availability group.

The SQL instance port 1433 is for the standard alone SQL server instance connection.

The AG listener port and SQL server instance port are different.

> Interesting thing is when client connection via listener, both ports 1433 and 12345 are granted.

It is due to your primary replica SQL instance port is 1433. If you failover AG to another replica that SQL server port is not 1433, then the AG listener connection failed through port 1433.

I did a test in my environment.

---

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.

Answer 3

Hi Cathy
Thanks for reply with details. in my case looks different.
-1. make sure my listener is on 12345

-2. connecting on port 12345

-3. connected successfully on port 12345

-4. now try to connecting with the default port via listener

-5. it successful

-6. try to connect port 1433 via listener

-7. success again

Answer 4

No. From what I understand, you do not need to. As long as your listener is a different port. When u trying to connect through the listener name, you only allowed by the specifed port number right? pls correct me, if i am wrong.

Answer 5

ok. thats much clear now. It actually means there are two open ports for connect via any listener port. 1. connect with the listener port number, 2. connect with the sql service port. both are granted via a listener.