Juan Ignacio Oller Aznar
In this tutorial, you will learnaboutthe 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 aDatabase Management System(DBMS).One of the most popular is Microsoft SQL Serverfor 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 atthe ports used by SQL Serverand how to open and close them on both theWindows 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 theports used by Microsoft SQL Serverand thefunction that they are used for.
Managing SQL Server ports on a Windows server
Before you get started
To complete this tutorial successfully andmanage your SQL ports on your Windows server, you will need:
- AWindows 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 areTCP ports1433, 1434, 4022 and 135, and UDP port1434. Here is an explanation of what each one does:
- Port1433/TCP: Default instance running over TCP.
- Port 1434/TCP: Dedicated adminconnection.
- Port 4022/TCP: SQL Server service broker.
- Port 135/TCP: Used by the Transact-SQL debugger.
- Port 1434/UDP: Used by the SQL browserservice. 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 use7022/TCP.
- Port 7022/TCP: Database Mirroring. Also possible to use5022/TCP.
- Port 21/TCP: Replication usingFTP.
- 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 byAnalysis Services
The following ports are used for Analysis Services:
- Port2383/TCP: Used byAnalysis Services for the default instance.
- Port2382/TCP: Used by the SQL Server browser service.
- Port80/TCP: Used when configuring Analysis Services viaIIS/HTTP.
- Port443/TCP: Used when configuring Analysis Services via IIS/HTTPS.
Ports used byReporting Services
Here are the most commonly used ports by SQL Server andReporting Services:
- Port80/TCP: Used by Reporting Services web services.
- Port443/TCP: Used byReporting Services web services.
Ports used by Integration Services
The ports most commonly used by SQL Server andIntegration Services:
- Puerto 135/TCP: Used byRemote Procedure Calls.
Other SQL Server ports and services:
Ports used by otherservices and functions in SQL Server.
- Port135/TCP: Used byWMI (Windows Management Instrumentation) andMS DTC (Microsoft Distributed Transaction Coordinator).
- Port1432/UDP: Used by the Management Studio browse button to connect to the SQL Server browser service.
- Port500/UDP: Used by IPSec traffic.
- Port4500/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 theMicrosoft website.
Part 1 – Configuring SQL ports on the Windows firewall
When opening ports, the first thing to do will be toopen 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, weare 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 theWindows Firewallsettings.Click on the search bar,type “Firewall”(1) and clickon“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) andclick 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 eitherTCP or UDP. This is generally defined by the port number itself.
Next, you have thechoice to openallports for the specified protocol or enter specific ports. We absolutely donotrecommend opening all ports as this could compromise security. Instead, we recommendselecting the option“Specificlocal ports”(9)and then entering theport number or numbers that you wish to open(10).
NOTE:If you wishto open more than one port, you need to enter them separated by commasunless they are consecutive ports, in which case, you can put the first and last portsseparated 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), thenclick on “Next”(13).
Select “Allow the connection”
in the “Profile” section,you need to select when the rule applies(14).
There arethree network profilesto 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 rulea name(16) anda 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 tocheck 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 operationusing the command prompt, also known asPowerShell.
This is also a fairly straightforward process. All you need toknow is the port number that you wishto open, whether it isinbound or outbound(normally inbound), and whether it is aUDP 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 specifywhether 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 thenetwork 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”, butwe recommend limiting thisby 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 touse to connect.This is composed of the protocol (TCP or UDP), in this case,TCP, and theport 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 touse to connect, in this case just theport 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 yourinfrastructureshould be abasic priorityfor any business looking toavoid exposing itsDBMSto unauthorised access or malicious attacks.
This tutorial explainshow tomanage 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 toplatform@jotelulu.comor calling+3491 133 37 10.
Thank you for choosing Jotelulu!
Categories:Servers