Click here to Skip to main content
15,113,225 members
Articles / Database Development / SQL Server
Article
Posted 27 Oct 2021

Tagged as

Stats

2.3K views
3 bookmarked

Configure Named Pipe and TCP/IP Settings of SQL Server

Rate me:
Please Sign up or sign in to vote.
5.00/5 (1 vote)
27 Oct 2021CPOL4 min read
How to configure Named Pipe and TCP/IP settings in SQL Server
In this tip, we will see how to enable TCP/IP and Named Pipe and configure firewall rules so that other network computers can communicate with SQL Server instances.

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.

Configure Named Pipe and TCP/IP Settings of SQL Server

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.

Configure Named Pipe and TCP/IP Settings of SQL Server

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.

Configure Named Pipe and TCP/IP Settings of SQL Server

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.

Configure Named Pipe and TCP/IP Settings of SQL Server

Step 5

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

Configure Named Pipe and TCP/IP Settings of SQL Server

Step 6

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

Configure Named Pipe and TCP/IP Settings of SQL Server

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.

Configure Named Pipe and TCP/IP Settings of SQL Server

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.

Configure Named Pipe and TCP/IP Settings of SQL Server

Step 2

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

Configure Named Pipe and TCP/IP Settings of SQL Server

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.

Configure Named Pipe and TCP/IP Settings of SQL Server

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.

Configure Named Pipe and TCP/IP Settings of SQL Server

Step 6

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

Configure Named Pipe and TCP/IP Settings of SQL Server

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.

Configure Named Pipe and TCP/IP Settings of SQL Server

Step 9

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

Configure Named Pipe and TCP/IP Settings of SQL Server

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

  • 27th October, 2021: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Hardik.Sheth1717
Team Leader
India India
SOFTWARE ENGINEER / TEAM LEAD / TECHNICAL LEAD

📅 21st April, 1991

📱 +91 97123 60303

📧 techieHS@gmail.com

🏠 Surat, Gujarat, India 🇮🇳


Software Engineer with 9+ years of experience in Client/Server, Web Application and Windows Applications development employing Object Oriented Analysis and Design (OOAD) using Microsoft Technologies and .NET Framework.

Extensive experience of n - tier web applications in ASP.NET (Webforms & MVC), ASP.NET Web API 2.0, C#, Microsoft Visual Studio .NET (2005/08/10/12/15/17), VB.NET, ADO.NET, JavaScript, AJAX, jQuery, ES6 by using .NET Framework 2.0/3.0/3.5/4.0/4.6.2/4.7.2.

Experience in designing and developing Databases in SQL Server 2005/2008/2012/2014/2017/2019 which includes the development of Functions, Stored Procedures, Views, Joins and Triggers. Writing efficient SQL queries, Query optimization and performance tuning, Indexing, Database schema design.

Experience in deploying, configuring and maintaining web sites and services on IIS 7.0.

Experience in continuous integration and continuous deployment (CI/CD) with Azure DevOps.

Experience of 3+ years in managing various services like Virtual machines, cloud storages etc. in Microsoft Azure.

Experience in managing EC2, S3, Auto Scaling, AMIs in AWS.

Experience of 2+ years in team management.

TECHNICAL SKILLS :

.NET Technologies: C#.NET (4.5/4.0/3.5/3.0 ), VB.NET (10.0/9.0/8.0 ), ASP.NET (Webforms & MVC), ASP.NET Web API 2.0, LINQ, EF6.
Databases: Microsoft SQL Server 2005/2008/2012/2014/2017/2019.
Web Technologies: ASP.NET (4.5/4.0/3.5/3.0), HTML5, CSS, XML, SOAP, web services, jQuery, JavaScript, Bootstrap, AXIOS, Promises, ES6.
Web server: Internet Information Service (IIS) 7.0.
Version Control: Team Foundation Server, Azure DevOps and GIT.
DevOps: Continuous integration and Continuous deployment using Azure DevOps 2019.
Clouds: Microsoft Azure, AWS.
I ❤️ coding and always eager to learn new technologies.

Comments and Discussions

 
-- There are no messages in this forum --