question

JiongAN-1498 avatar image
0 Votes"
JiongAN-1498 asked JiongAN-1498 commented

alwayson listener connection granted regardless specified listener port

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-generalsql-server-transact-sqlwindows-server-clustering
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered Cathyji-msft edited

Hi @JiongAN-1498,

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered Cathyji-msft edited

Hi @JiongAN-1498,

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.

143263-screenshot-2021-10-25-101450.jpg

143233-screenshot-2021-10-25-102055.jpg

143234-screenshot-2021-10-25-101420.jpg

143224-screenshot-2021-10-25-103434.jpg


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.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JiongAN-1498 avatar image
0 Votes"
JiongAN-1498 answered JiongAN-1498 commented

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

-2. connecting on port 12345
143284-capture3.jpg
-3. connected successfully on port 12345

143207-capture2.jpg

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

-5. it successful

143197-capture5.jpg

-6. try to connect port 1433 via listener
143269-capture6.jpg
-7. success again
143286-capture7.jpg



capture1.jpg (34.3 KiB)
capture3.jpg (44.0 KiB)
capture2.jpg (23.8 KiB)
capture4.jpg (47.5 KiB)
capture5.jpg (31.2 KiB)
capture6.jpg (45.8 KiB)
capture7.jpg (24.8 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @JiongAN-1498,

Did you change your SQL AG primary replica SQL instance port from 1433 to another custom port?

0 Votes 0 ·

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.

0 Votes 0 ·
JiongAN-1498 avatar image
0 Votes"
JiongAN-1498 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JiongAN-1498 avatar image
0 Votes"
JiongAN-1498 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LimitlessTechnology-2700 avatar image
0 Votes"
LimitlessTechnology-2700 answered JiongAN-1498 commented

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--

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you taking time to help. Everything are very clear now.

0 Votes 0 ·