Cluster SSAS, How change the default listening port configuration

Microsoft SQL Server default port

Port / December 21, 2021

Author: Sanjay Mishra

Reviewers: David P. Smith (Active Network), Mike Ruthruff (Bungie Studios), Matt Neerincx, Luis Carlos Vargas Herring, Piyush Ranjan, Steven Schneider

My job provides me opportunity to work across different sections of customers – some customers who like all defaults (default instances, default port numbers, etc.), and some customers who don’t like defaults at all. Customers who don’t like defaults want full control over naming instances and choosing the port number the SQL Server service listens on.

As you know, one can have a Default SQL Server instance (MSSQLSERVER) or a named SQL Server instance. The Default SQL Server instance listens on port 1433, by default. Some customers don’t like the port number to be known to the whole world, and may like their Default instance listen on a different custom port number.

A named SQL Server instance listens on a dynamic port, by default. The dynamic port is selected by the operating system, and therefore, may or may not meet the corporate policies of some customers, who may want specific applications use specific ports. In these cases, you may like to explicitly specify which port your SQL Server instance listens on. You can do this using the SQL Server Configuration Manager ( as shown in Figure 1.

Figure 1: Specifying a static port number for a SQL Server instance

More information on this in the KB article: .

When you specify a non-default static port number for a SQL Server instance, as in Figure 1, the connection string must specify the port number (assuming SQL Server Browser is not running, more on this a bit later), for example, port 54145 below:

Data Source=”MyServer1TESTSQL, 54145“;Initial Catalog=AdventureWorks; Integrated Security=True; …

If you don’t want to specify the port number in the application connection string, you can use SQL Server aliases on the client.

It is important to note that, if you have a Windows firewall on the SQL Server machine, you must open the corresponding port for inbound connections.

Another alternative to specifying port number in the client connection string is the SQL Server Browser service (. SQL Server Browser service is running, the client can connect to the SQL Server instance without specifying the port number, such as:

Data Source=”MyServer1TESTSQL“;Initial Catalog=AdventureWorks;Integrated Security=True; …

It is important to note that, the SQL Server Browser service runs on the UDP port 1434, and if you have a Windows firewall on the SQL Server machine, you must open the UDP port 1434 for inbound connections. That is one more port to open in the firewall, and one more well-known port number.

The port assignments become a bit involved with AlwaysOn Availability Groups, because, you now deal with port numbers for multiple instances of SQL Server (AG primary and AG secondaries), as well as a port number for the AG Listener (Figure 2).

Figure 2: Specifying a port number for an AG Listener

You can choose the port numbers in many different ways.

  • Specify different port numbers for each instance and a different port number for the Listener.
  • Specify the same port number for all the instances (as a standard), and a different port number for the Listener.
  • Specify the same port number for all the instances as well as the Listener. This is possible because the IP address of the SQL Server instance is different from the IP address if the Listener.

The following examples illustrate the above scenarios. In each of the following examples, we have a 3-node Availability Group – each machine running a named SQL Server instance, and each machine has Windows firewall enabled.

Different port numbers for each instance (AG replica), and a different port number for the Listener

Figure 3 shows a SQL Server AlwaysOn AG deployment for High Availability and Disaster Recovery. Each of the three SQL Server instances are listening on different ports (12345, 23456, 34567 respectively), and the AG Listener is listening on port 98765.

Figure 3: SQL Server AlwaysOn AG HADR configuration, with different port numbers for each instance and a different port number for the AG Listener

In this case, if you have a Windows firewall on the SQL Server machines, you will need to allow two ports on each machine – the port on which the SQL Server instance is running (12345, 23456, 34567 respectively), as well as the port 98765 through the firewall on each machine. The application needs to specify the AG Listener port number while connecting to the Listener. For example, the connection string will look like:

Data Source=”TestAGListen, 98765“;Initial Catalog=AdventureWorks; Integrated Security=True;…

Unlike connecting to a SQL Server instance, if you are connecting to the AG Listener, the SQL Server Browser doesn’t help you omit the port number in the connection string. For the remaining examples, we will ignore the SQL Server Browser.

Same port number for all the instances (AG replicas), and a different port number for the AG Listener

Figure 4 shows a SQL Server AlwaysOn AG deployment for High Availability and Disaster Recovery. Each of the three SQL Server instances are listening on port 12345, and the AG Listener is listening on port 98765.

The client connection string for Figure 4 will be similar to the client connection string for Figure 3.

Figure 4: SQL Server AlwaysOn AG HADR configuration, with the same port number for all the SQL instances, and a different port number for the AG Listener

Same port number for all the instances as well as the Listener

Some customers use the same port number for the SQL Server instances as well as for the Listener (Figure 5, so that they need to allow only one port through the firewall.

Figure 5: SQL Server AlwaysOn AG HADR configuration with the same (non-default) port number for SQL Server instances and the AG Listener

In Figure 5, all the instances as well as the Listener use the same non-default port number (12345). Figure 6 shows an example where all the instances as well as the Listener use the same default port number (1433). In the example of Figure 6, the clients don’t need to specify port number in their connection strings.

Data Source=TestAGListen;Initial Catalog=AdventureWorks; Integrated Security=True;…

Figure 6: SQL Server AlwaysOn AG HADR configuration with the same port number (1433) for SQL Server instances and the AG Listener

necklace of good deeds black desert good deeds full movie free good deeds movie online good deeds (2012) police doing good deeds 9 11 good deeds bible verse doing good deeds without recognition blessing good deeds quotes tyler perry movie good deeds soundtrack good deeds soundtrack see your good deeds do good deeds quotes ellen degeneres good deeds good deeds reviews good deeds movie soundtrack mr deeds looks good to me good deeds don't go unpunished someone who does good deeds no good deeds movies good deeds you can do example of good deeds our good deeds are as filthy rags watch tyler perry good deeds how to do good deeds good deeds scripture no good deeds online good deeds pembroke ma hours the words "good words, good thoughts, good deeds" were used to sum up the view of morality of the 100 good deeds for eddie what does the bible say about bragging about good deeds kindness good deeds quotes bill gates good deeds good deeds book police good deeds good deeds moving company good deeds foundation good deeds society tyler perry good deeds torrent good deeds go unpunished kkk good deeds why is good deeds at first unable to accompany everyman good deeds calendar watch tyler perry good deeds online free quote about good deeds quotes on doing good deeds good deeds quote good deeds will not get you to heaven

Source: blogs.msdn.microsoft.com