In this tutorial, you will learn about the ports used by SQL Server and how to open or close them on your Windows server. This way, you can be sure that your SQL Server DBMS is sufficiently protected from outside threats.
Many IT projects these days will require a Database Management System (DBMS). One of the most popular is Microsoft SQL Server for its performance, simplicity and level of support. Ultimately, the Microsoft brand is generally associated with a quality product with good technical support for users.
In this tutorial, we are going to look at the ports used by SQL Server and how to open and close them on both the Windows and Jotelulu firewalls.
NOTE:This tutorial has been written using SQL Server 2019 running on Windows 2022. However, there should not be any major differences if you are running an older version like Windows Server 2016 or Windows Server 2019.
First, we will look at the ports used by Microsoft SQL Server and the function that they are used for.
Managing SQL Server ports on a Windows server
Before you get started
To complete this tutorial successfully and manage your SQL ports on your Windows server, you will need:
- A Windows Server.
- SQL Server installed on your server.
A list of SQL ports by function
Below is a list of some of the ports that you may need to open in order for SQL Server to function correctly.
Ports used by the database engine
The ports most commonly used by SQL Server and the database engine are TCP ports 1433, 1434, 4022 and 135, and UDP port 1434. Here is an explanation of what each one does:
- Port 1433/TCP: Default instance running over TCP.
- Port 1434/TCP: Dedicated admin connection.
- Port 4022/TCP: SQL Server service broker.
- Port 135/TCP: Used by the Transact-SQL debugger.
- Port 1434/UDP: Used by the SQL browser service. Listens for incoming connections.
- Port 80/TCP: HTTP connections through a URL.
- Port 443/TCP: HTTPS connections via SSL.
- Port 5022/TCP: Database Mirroring. Also possible to use 7022/TCP.
- Port 7022/TCP: Database Mirroring. Also possible to use 5022/TCP.
- Port 21/TCP: Replication using FTP.
- Port 445/TCP: Filesharing.
- Port 137/UDP: Filesharing.
- Port 138/UDP: Filesharing.
Some ports will not need opening unless you intend to use the associated function. For example, if you are not going to use database mirroring, there is no need to open either 5022/TCP or 7022/TC.
It is also possible to establish connections using dynamic ports.
Ports used by Analysis Services
The following ports are used for Analysis Services:
- Port 2383/TCP: Used by Analysis Services for the default instance.
- Port 2382/TCP: Used by the SQL Server browser service.
- Port 80/TCP: Used when configuring Analysis Services via IIS/HTTP.
- Port 443/TCP: Used when configuring Analysis Services via IIS/HTTPS.
Ports used by Reporting Services
Here are the most commonly used ports by SQL Server and Reporting Services:
- Port 80/TCP: Used by Reporting Services web services.
- Port 443/TCP: Used by Reporting Services web services.
Ports used by Integration Services
The ports most commonly used by SQL Server and Integration Services:
- Puerto 135/TCP: Used by Remote Procedure Calls.
Other SQL Server ports and services:
Ports used by other services and functions in SQL Server.
- Port 135/TCP: Used by WMI (Windows Management Instrumentation) and MS DTC (Microsoft Distributed Transaction Coordinator).
- Port 1432/UDP: Used by the Management Studio browse button to connect to the SQL Server browser service.
- Port 500/UDP: Used by IPSec traffic.
- Port 4500/UDP: Used by IPSec traffic.
NOTE: Some of these ports can be swapped for other ones in order to protect against port scanning and other techniques used by hackers.
NOTE: You can find more information about the ports used by Microsoft SQL services on the Microsoft website.
Part 1 – Configuring SQL ports on the Windows firewall
When opening ports, the first thing to do will be to open them on the Windows firewall. Unless you’re in a laboratory environment, disabling the firewall completely is not really an option, so we will look at how to open them individually using firewall rules.
As with many IT tasks, there is more than one way to do this. You might prefer to use the graphic interface or you might prefer to use PowerShell. For the moment, we are going to use the graphic interface because it is much simpler, especially if you are not overly familiar with this kind of task. Then, later on, we will look at how to use the command prompt to perform the same operation.
First, we need to load the Windows Firewall settings. Click on the search bar, type “Firewall” (1) and click on “Windows Defender Firewall” (2).
Launch Windows Defender Firewall from the search bar
Then, click on “Advanced settings” (3).
Open Advanced Settings in Windows Defender Firewall
You will now see a window titled “Windows Defender Firewall with Advanced Security”. At the top of the left-hand pane, click on “Inbound Rules” (4). Then, on the right-hand side, click on “New Rule…” (5).
Create a new inbound rule for Windows firewall
In the window that appears, select “Port” (6) and click on “Next” (7).
Select “Port” as the type of rule
In the “Protocols and ports” section, you will need to select the type of port (8), choosing either TCP or UDP. This is generally defined by the port number itself.
Next, you have the choice to open all ports for the specified protocol or enter specific ports. We absolutely do not recommend opening all ports as this could compromise security. Instead, we recommend selecting the option “Specific local ports” (9) and then entering the port number or numbers that you wish to open (10).
NOTE: If you wish to open more than one port, you need to enter them separated by commas unless they are consecutive ports, in which case, you can put the first and last ports separated by a hyphen. E.g. To open ports 80 and 443, write “80,443”. To open ports 80 to 90, write “80-90”.
Once you have done this, click on “Next” (11).
Select the port protocol and enter the port number(s)
In the “Action” section, click on “Allow the connection” (12), then click on “Next” (13).
Select “Allow the connection”
in the “Profile” section, you need to select when the rule applies (14).
There are three network profiles to choose from, and it is up to you to choose the most appropriate one for your situation.
- Domain: For devices connected to corporate domains.
- Private: For devices connected to private networks, either at home or in the workplace.
- Public: For devices connected to public networks.
Once you have done this, click on “Next” (15).
Select the types of networks that the rule will apply to
The final step is to give the rule a name (16) and a description (17). This will help you to identify your rule later and keep them well-organised. This can be particularly important if you are opening a lot of ports.
Once you have filled in these fields, click on “Finish” (18).
Give your new rule a name and a description to make it easier to identify at a later date
At this point, it is a good idea to check that your new rule appears in the list of rules (19).
Check that your new rule has been created successfully
Repeat this process for all the ports that you wish to open unless you are opening all your ports as part of a single rule.
Some readers may prefer to perform this operation using the command prompt, also known as PowerShell.
This is also a fairly straightforward process. All you need to know is the port number that you wish to open, whether it is inbound or outbound (normally inbound), and whether it is a UDP or TCP port.
With this information, you can launch PowerShell and enter the following text:
New-NetFirewallRule -DisplayName “NAME” -Direction Inbound -LocalPort PORT -Protocol PROTOCOL -Action Allow
Where:
- DisplayName “NAME”: The name that you wish to give to your new rule. This isn’t mandatory but it is good practice to give your firewall rules descriptive names.
- Direction Inbound: The direction of the port, in this case, inbound.
- LocalPort PORT: Specifying the port number, swapping “PORT” for the number.
- Protocol PROTOCOL: To specify whether it is a TCP or UDP port.
- Action Allow: The action that the firewall should take, in this case, allowing the connection.
Creating a new inbound rule for the Jotelulu firewall
In the next window, you will need to configure the following settings:
- Select network: (21) Select the network that you wish to use to establish the connection. The default option will generally be the correct one.
- IP or Network: (22) The IP or network from which the connection will be made. By default, this is set to “Any IP Address”, but we recommend limiting this by entering the IP address or range of IP addresses that you wish to allow. This is especially the case if you are not expecting any attempted connections from any other location.
- Protocol and public port: (23) The port that you want to use to connect. This is composed of the protocol (TCP or UDP), in this case, TCP, and the port number.
- Public IP: (24) The public IP of the server that will connect.
- Private (IP): (25) The private IP address of the server that will connect.
- Private port: (26) The port that we want to use to connect, in this case just the port number.
Once configured, click on “Continue” (27) to create the rule.
Configure the Jotelulu firewall for SQL Server ports
Repeat this process until you have added all the firewall rules that you need. Then check that they have been applied successfully.
Conclusion
Securing your infrastructure should be a basic priority for any business looking to avoid exposing its DBMS to unauthorised access or malicious attacks.
This tutorial explains how to manage SQL ports on your Windows server to either open or close ports.
We hope that you have found this tutorial useful. However, if you are still having problems or would like any technical information, you can get in touch with us by writing to platform@jotelulu.com or calling +34 91 133 37 10.
Thank you for choosing Jotelulu!
