Forum Discussion
Strange behavior of MSSQL Listener name + port
Hello, everyone.
We are running MSSQL 2022, we have 2 listener and port, said LSNA with port 1434, LSNB with port 1435. The connection is no problem. But strange is that it can also go into DB with LSNA,1435 and LSNB,1434 which do not exist, it will go to DB according to port but not listener+port.
Anyone can give advise to me on this? Thank you very much in advance.
It's the same thing as I mentioned before.
If you LSNA and LSNB resolve to the same IP address, there's little you can do.
If LSNA and LSNB resolve to different IP addresses, you can change the listener configuration to only accept connections made to a specific IP address and port combination.
Configure SQL Server to listen on a specific TCP port - SQL Server | Microsoft Learn
Set ListenAll to false on the TCP/IP protocol page, and remove port allocations (both dynamic and static) on all IPs other than the wanted IP.
Remember to remove the values from IPAll as well, as they override more specific settings if set.
By changing this configuration in SQL Server Configuration manager, you should be able to stop LSNA:1435 connections to the LSNB instance, assuming LSNA and LSNB are different IP addresses.
If not, SQL Server has no way to differentiate between these connection strings, as they are to the correct IP:Port pair, even if it is the "wrong" Name:Port pair.
There is one more alternate way to stop connections to LSNA from reaching LSNB, dependant on connection string contents. This does not require multiple IP addresses.
You can issue server certificates to your two instances, one for LSNA, one for LSNB, and set SQL Server to "Force Encryption".
This will fail client connections hitting the other instance, as the client will realize it's connected to the wrong instance and back off.
If the client includes "trust server certificate = true" in their connection string, this approach will not work.
Transport Layer Security and digital certificates - SQL Server | Microsoft Learn
3 Replies
Thanks, Sivert Solem.
I understand what you mean. My case is in same DB server with 2 different instances.
Let said server A.
Instance A, Listener LSNA, port 1434
Instance B, Listener LSNB, port 1435use SSMS to login with LSNA,1434 will go to Instance A
use SSMS to login with LSNB,1435 will go to Instance B, both works as expected.
But strange is that
login with LSNA,1435 will go to instance B
login with LSNB,1434 will go to instance A, these combinations are not correct, we expect it can't login and prompt out error. Still, it can login to the instance according to port number but ignore the listener's name.
Is this normal per MSSQL design or any setting to correct it?Thanks a lot
It's the same thing as I mentioned before.
If you LSNA and LSNB resolve to the same IP address, there's little you can do.
If LSNA and LSNB resolve to different IP addresses, you can change the listener configuration to only accept connections made to a specific IP address and port combination.
Configure SQL Server to listen on a specific TCP port - SQL Server | Microsoft Learn
Set ListenAll to false on the TCP/IP protocol page, and remove port allocations (both dynamic and static) on all IPs other than the wanted IP.
Remember to remove the values from IPAll as well, as they override more specific settings if set.
By changing this configuration in SQL Server Configuration manager, you should be able to stop LSNA:1435 connections to the LSNB instance, assuming LSNA and LSNB are different IP addresses.
If not, SQL Server has no way to differentiate between these connection strings, as they are to the correct IP:Port pair, even if it is the "wrong" Name:Port pair.
There is one more alternate way to stop connections to LSNA from reaching LSNB, dependant on connection string contents. This does not require multiple IP addresses.
You can issue server certificates to your two instances, one for LSNA, one for LSNB, and set SQL Server to "Force Encryption".
This will fail client connections hitting the other instance, as the client will realize it's connected to the wrong instance and back off.
If the client includes "trust server certificate = true" in their connection string, this approach will not work.
Transport Layer Security and digital certificates - SQL Server | Microsoft Learn
Your description of the issue is not particularly clear, so I'm going to make some assumptions:
LSNA and LSNB are DNS entries that resolve to the same IP Address:
- SQL Server listens to that IP, and both 1434 and 1435
- Any combination of LSNX,143Y connects to the instanceLSNA and LSNB are DNS entries that resolve to different IP Addresses:
- The ports are assigned to 0.0.0.0 (IPv4 any) and/or :: (IPv6 any)
- Any combination of LSNX,143Y connects to the instanceYou should be able to verify your listeners with
select * from sys.dm_tcp_listener_states
