Configure Named Pipe and TCP/IP Settings of SQL Server

Introduction

In this tip, we are going to configure Microsoft SQL Server on a Windows machine to listen to a specific TCP port. We will enable TCP/IP and Named Pipe and configure firewall rules so that other network computers can communicate with SQL Server instances.

Pre-requisites

• Microsoft SQL Server installed on the machine (please check my article to install SQL Server here).

Configure Named Pipe and TCP/IP Protocols

Let us first configure Named Pipe and TCP/IP for SQL Server. I have installed SQL Server 2019 on my local machine.

Step 1

Open SQL Server Configuration Manager from the Start menu.

Step 2

Let's first enable Named Pipe for the SQL Server Instance (in my case MSSQLSERVER). Go to SQL Server Network Configuration (in Console pane) and click on the Protocols for MSSQLSERVER.

Step 3

In the details pane (right panel), right-click on the Named Pipes protocol, and then click Enable to enable the named pipe for that particular SQL instance.

Step 4

Next, we will enable TCP/IP connection for the SQL Server Instance. So right-click on the TCP/IP protocol and click on the Enable option.

Step 5

Now again right-click on TCP/IP protocol, and then click the Properties option.

Step 6

Now you can see the TCP/IP Properties window, click on the Protocol tab, select Yes in Listen All property.

Step 7

Now click on the IP Addresses tab, scroll down to the IPALL section. Now change the value of the TCP Dynamic Ports property to blank. If its value is zero, the Database Engine will listen on dynamic ports. Next, in the TCP Port property, enter the port number you want this IP address to listen on (in my case 1717), and then click the OK button. You can also specify multiple ports by separating them with a comma.

Note: If you want to use individual IP addresses (IPn sections), please set Listen to All property to No in the Protocol tab. Here, I want to use IPAll section, for that reason, I have set Listen All property to Yes in the Protocol tab.

Step 7

The above configurations will only take effect after we restart SQL Server Instance services. So now select the SQL Server Services option from the Console Pane.

Step 8

Now, right-click on the SQL Server (MSSQLSERVER) from the Details Pane and then click the Restart button to restart the SQL Server service.

We have successfully configured an instance of the SQL Server Database Engine to listen on a specific fixed port by using the SQL Server Configuration Manager. Next, we need to configure a firewall so that our specified port (in my case 1717) can be opened in the firewall.

Firewall Configurations to Allow SQL Port

Now that we have configured SQL Server to listen on a specific port, we need to open that particular port in the firewall. Let us see how to configure the firewall.

Step 1

Open Windows Defender Firewall from the Start menu.

Step 2

You will see the Windows Defender Firewall window from Control Panel. Click on the Advanced Settings link from the left panel.

Step 3

Now you will see Windows Defender Firewall with Advanced Security window. Click on the Inbound Rules from the left panel and then click on the New Rule button from the right panel.

Step 4

Now you will see the New Inbound Rule Wizard window. In the Rule Type step, select Port because we want to allow SQL port 1717 from the firewall and click on the Next button.

Step 5

In Protocol and Ports step, select Specific local ports and enter our SQL port 1717 in the textbox and click the Next button.

Step 6

In the Action step, select Allow the connection and click on the Next button.

Step 7

In the Profile step, check all the checkboxes and click on the Next button.

Step 8

In the Name step, specify a meaningful name for the rule and provide a description and click on the Finish button.

Step 9

Now you can see our new rule is listed in the Inbound Rules list.

Excellent! We have successfully configured a firewall to open the SQL port for the database instance connection from outside.

Expected Errors/Issues

Sometimes, you can face some issues/errors in connectivity with SQL Server instances from outside even after the above configurations. Please make sure:

1. In SQL Server Configuration Manager, in the console pane, click on the SQL Server Services, make sure SQL Server Browser service is running.
2. If you have assigned an SQL port other than 1433, you have to specify the port number in the server name at the time of the SQL connection. For example, cloude-desire-01, 1717 in my case.

Conclusion

As you can see, it is very easy to configure outside access on a specific port for SQL Server instance if you follow the above steps accurately.

History

• 27^th October, 2021: Initial version