1

Scenario: Windows Server 2012R2 & SQL Server 2014 SP2, no CUs FCI adding a non-default port and keeping the default port

I made the change on both nodes via SQL Configuration Manager, however, neither failover or complete resource restart allows a connection to be made on the new port. We have tested connection to the port with the application, telnet and PoSH (Test-NetConnection) to no avail. The SysInternals PortMon utility does not show SQL Server listening on the added port. I did confirm the port shows in the registry on both nodes.

Edit: A huge detail I should have listed but forgot to. When the service restarts, there is not indication that it is listening on the new port, just the default.

What did I miss or what else do I need to check?

Obviously, I cannot test any changes without some notice.

Thank you in advance!

1
  • I both added an exception for the new port and disabled Windows Firewall to no avail. – pshore73 Sep 27 '17 at 13:24
2

You will also need to configure an additional TCP endpoint inside of SQL Server itself. If you run a query of

SELECT * FROM sys.tcp_endpoints;

I would expect you to see the default endpoint, as well as the Dedicated Admin endpoint. You will need to add another endpoint:

USE master;  
GO  
CREATE ENDPOINT [MyNewTCPEndpoint]  
STATE = STARTED  
AS TCP  
   (LISTENER_PORT = YourTCPPort, LISTENER_IP =ALL)  
FOR TSQL() ;  
GO  

And then you may need to re-grant access to the default endpoint

GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to [public];

See the Microsoft documentation on configuring SQL for multiple TCP ports for full details.

6
  • Thank you! Is a restart/failover required? – pshore73 Sep 27 '17 at 17:07
  • It should not be required, no. Just be careful as you may lose permissions on the default endpoint until the second script is executed, so that may be problematic for your users. I'd advise performing the work during a quiet period, or during maintenance (if you have that luxury). – Nic Sep 27 '17 at 19:20
  • Despite adding the endpoint and even performing a failover of the cluster, still not listening on the port I need. The SQL Error log only shows listening on the default ports. – pshore73 Oct 4 '17 at 0:53
  • Additionally, no mention of the port I am trying to add in the SQL Error log, Windows System, or Windows Application log – pshore73 Oct 4 '17 at 1:00
  • For what it is worth, I am seeing an error in the SQL Error log that an account does not exist for the other machine in the FCI. "Login failed for user '<domain>\<node>$' Reason: Could not find a login matching the name provided." – pshore73 Oct 4 '17 at 1:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.