Consideration for changing the default port of default SQL instance on Multi-subnet Failover Cluster

Security Hardening or Security Audits in an enterprise are some of the motivation for DBAs to run SQL Server default instance on a non-default port. For standalone instances, there are no challenges in changing the default instance port to non-default port, however for clustered instance there are some considerations since cluster.exe performs IsAlive check by logging into SQL Server instance.

First to avoid some confusion or myth if any. When a client is connecting to default instance of SQL Server listening on default port (1433) or non-default port, SQL Browser service is not involved & has no role to play in case of default instance. This is the same reason why you observe SQL Browser service is disabled, when you install only default instance of SQL Server on a given server.  SQL Browser service has a role to play only for Named instances & not when Default instances is listening on non-default ports.

For a clustered default instance of SQL Server, when we change the port of the SQL Instance to non-default port, the SQL Server resource might fail to come online & you might see the following error messages in Cluster Log

<< The following abstract is for Clustered default instance of SQL 2012 running on non-default port>>
00002840.00003ef0::2015/01/16-23:25:19.637 INFO [RES] SQL Server : [sqsrvres] Server name is SQLFCI
00002840.00003ef0::2015/01/16-23:25:19.637 INFO [RES] SQL Server : [sqsrvres] Service name is MSSQLSERVER
00002840.00003ef0::2015/01/16-23:25:19.637 INFO [RES] SQL Server : [sqsrvres] Dependency expression for resource ‘SQL Network Name (SQLFCI)’ is ‘([5c22a982-9400-416b-b5bd-95da07998601] or [50660f59-4f33-4cd1-9dc0-84f6b32ebb76])’
00002840.00003ef0::2015/01/16-23:25:19.637 INFO [RES] SQL Server : [sqsrvres] This instance is running with IP OR dependency. Resource DLL Will enable MultiSubnetFailover=yes in connection string
00002840.00003ef0::2015/01/16-23:25:19.637 WARN [RES] SQL Server : [sqsrvres] This is default instance. If non-default TCP/IP listening port number is specified, and SQL Server fails to come online, configure an alias on each possible owner node and try again. If it does not work, please contact customer support
00002840.00003ef0::2015/01/16-23:25:19.637 INFO [RES] SQL Server : [sqsrvres] Starting service MSSQLSERVER…
0000125c.00004990::2015/01/16-23:25:19.886 INFO [NM] Received request from client address SQLFCINodeA.
00002840.00003ef0::2015/01/16-23:25:20.842 INFO [RES] SQL Server : [sqsrvres] Service status checkpoint was changed from 0 to 1 (wait hint 20000). Pid is 19508
0000125c.00003fe8::2015/01/16-23:25:23.639 INFO [NM] Received request from client address SQLFCINodeA.
00002840.00003ef0::2015/01/16-23:25:23.842 INFO [RES] SQL Server : [sqsrvres] Service is started. SQL Server pid is 19508
00002840.00003ef0::2015/01/16-23:25:23.842 INFO [RES] SQL Server : [sqsrvres] Connect to SQL Server …
00002840.00003ef0::2015/01/16-23:25:54.855 ERR [RES] SQL Server : [sqsrvres] ODBC Error: [08001] [Microsoft][SQL Server Native Client 11.0]TCP Provider: The remote computer refused the network connection.
00002840.00003ef0::2015/01/16-23:25:54.855 ERR [RES] SQL Server : [sqsrvres] ODBC Error: [HYT00] [Microsoft][SQL Server Native Client 11.0]Login timeout expired (0)
00002840.00003ef0::2015/01/16-23:25:54.855 ERR [RES] SQL Server : [sqsrvres] ODBC Error: [08001] [Microsoft][SQL Server Native Client 11.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. (1225)
00002840.00003ef0::2015/01/16-23:25:54.855 INFO [RES] SQL Server : [sqsrvres] Could not connect to SQL Server (rc -1)
00002840.00003ef0::2015/01/16-23:25:54.855 INFO [RES] SQL Server : [sqsrvres] SQLDisconnect returns following information
00002840.00003ef0::2015/01/16-23:25:54.855 ERR [RES] SQL Server : [sqsrvres] ODBC Error: [08003] [Microsoft][ODBC Driver Manager] Connection not open (0)
00002840.00003ef0::2015/01/16-23:25:59.857 INFO [RES] SQL Server : [sqsrvres] Connect to SQL Server …
00002840.00003ef0::2015/01/16-23:26:30.860 ERR [RES] SQL Server : [sqsrvres] ODBC Error: [08001] [Microsoft][SQL Server Native Client 11.0]TCP Provider: The remote computer refused the network connection.

With Enhanced Verbose Logging in SQL 2012 as seen above, we now get an message saying that if you are running default instance on non-default TCP/IP port, we should create an alias (TCP Or NP )  to bring SQL Server resource online.

00002840.00003ef0::2015/01/16-23:25:19.637 WARN  [RES] SQL Server <SQL Server>: [sqsrvres] This is default instance. If non-default TCP/IP listening port number is specified, and SQL Server fails to come online, configure an alias on each possible owner node and try again. If it does not work, please contact customer support

In case of Multi-Subnet Failover Cluster we observed the creation of alias brought the SQL Server resources online, however SQL Agent resource failed to come online. When we observed the SQLAgent.out Log we see the following error message

2015-01-16 17:36:54 – ! [150] SQL Server does not accept the connection (error: 1225). Waiting for Sql Server to allow connections. Operation attempted was: Verify Connection On Start.
2015-01-16 17:36:54 – ! [000] Unable to connect to server ‘(local)’; SQLServerAgent cannot start
2015-01-16 17:36:59 – ! [298] SQLServer Error: 1225, TCP Provider: The remote computer refused the network connection. [SQLSTATE 08001]
2015-01-16 17:36:59 – ! [165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00]
2015-01-16 17:36:59 – ! [298] SQLServer Error: 1225, A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. [SQLSTATE 08001]
2015-01-16 17:36:59 – ! [382] Logon to server ‘(local)’ failed (DisableAgentXPs)
2015-01-16 17:36:59 – ? [098] SQLServerAgent terminated (normally)

This issue was specific to Multi-Subnet Failover Clustering since we observed that if we disable the cluster checkpoints & Modify the following registry value from 1 to 0, SQL Agent resource would come online.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.MSSQLSERVER\Cluster\RunWithIPORDependency

However it would remain online until SQL Server resource is restarted. When SQL Server resource comes online again, the SQL Server detects it as Multi-Subnet Cluster & flips the bit from 0 to 1 (Note: this is not caused due to Cluster Checkpointing)

This prevents SQL Agent resource to come online. To work around this issue, we can set the ServerHost registry for SQL Agent to point to use the Alias created which will ensure SQL Agent can connect to SQL Server & the resource comes online.

1. 1.       Remove Cluster Checkpoint

Cluster res “SQL Network Name(SQLFCI)” /removecheckpoint:”Software\Microsoft\Microsoft SQL Server\MSSQL11.MSSQLSERVER\SQLServerAgent”

1. 2.       Set ServerHost registry key to Alias name

1. 3.       Add Cluster Checkpoint

Cluster res “SQL Network Name(SQLFCI)” /addcheckpoint:”Software\Microsoft\Microsoft SQL Server\MSSQL11.MSSQLSERVER\SQLServerAgent”

After pointing ServerHost key to Alias which resolves to non-default port, SQL Agent is able to connect SQL Server & comes online.

I have filed a connect bug for the same issue & feel free to vote to bump up the priority

https://connect.microsoft.com/SQLServer/feedback/details/1090514/sql-agent-fails-to-come-online-in-multi-subnet-clustered-default-instance-of-sql-server-running-on-non-default-port

Parikshit Savjani
Premier Field Engineer