SQL Server functions on various ports that are typically blocked in firewalls. If you need to connect to your SQL Server from another machine for either using the database, the other features, or to simply use the Management Studio with it, you will need to open a few ports. In this article, I give you the details on how to do this, along with simple NETSH commands for the same.
SQL Server functions on various ports that are typically blocked in firewalls. If you need to connect to your SQL Server from another machine for either using the database, the other features, or to simply use the Management Studio with it, you will need to open a few ports. In this article, I give you the details on how to do this, along with simple NETSH commands for the same.
In a good server design, you will be running each of these components on different dedicated servers. For the sake of simplicity, I am assuming all of the services are on one server. If you are running them on different machines, you will need to split the operations below between each server as appropriate.
A Note About SQL Server Browser Service
Many articles out there will tell you that the Browser service is not required if you do not use the “Browse..” feature of the Management Studio to locate the SQL Server instance to connect to it. However, you may find in your particular case that Management Studio will not connect to your SQL Server instance at all unless the Browser service is turned on. In order for it to work, you will need to turn on the SQL Browser service and open the corresponding UDP port. We tell you how in the instructions below.
SQL Server Ports List
Here is a list of ports and the component addressed by it that we will be opening:
- 135 – SQL Debugger and RPC port – if you plan to remote debug stored procedures, etc.
- 1433 – Database engine – both application and management studio connectivity
- 1434 – “Administration Connection” or SQL Browser – management studio connectivity
- 2383 – Analysis Services – both application and management studio connectivity
- 2382 – SQL Server Browser – required for management studio
- 4022 – Service Broker – only if you use SQL Server Service Broker
In addition, if you need server to server SQL Server enumeration by the SQL Browser service, you will need to enable Multicast over UDP.
GUI – Windows Firewall with Advanced Security
As system administrator (on the machine with SQL Server), fire up Windows Firewall with Advanced Security from your Administrative Tools folder.
- Start a New Rule
- Select “Custom”
- Select “All programs”
- With “Protocol Type” as “TCP”, set Local Ports to “Specific Ports” and enter the list of ports to open. For this example, we open 1433, 1434 and 2382.
- Leave “Local IP address” as “Any” if your SQL Server is listening on all local IP addresses (a really bad idea), otherwise select and enter the specific IP addresses your SQL Server is bound to.
- Leave “Remote IP address” as “Any” if you wish to permit everything that tries to connect through your firewall (again, a really bad idea). You should typically allow specific IP address ranges only to connect, so configure it for specific IP addresses of your client systems or the IP address range of your application servers’ addresses.
- Leave the selections in the next two screens as is, unless you have the need to change it to be more specific
- Enter a name for your profile (“
Allow: Inbound: TCP: SQL Server Services”). Finish the wizard.
Command line – NETSH
As a system administrator (on the machine with SQL Server), fire up the Command Prompt in Administrator mode. Type the following command:
C:\> NETSH advFirewall firewall add rule name="Allow: Inbound: TCP: SQL Server Services"
dir=in action=allow protocol=TCP localport=1433,1434,2382
If you wish to open the other TCP ports as well (135,2383,4022), simply add them to the list for the “localport” parameter.
Enabling Multicast over UDP for SQL Browser Enumeration
This is only required for server to server enumeration and typically should be kept OFF. But if you require to do this, you can use the following NETSH command:
C:\> NETSH firewall set MulticastBroadcastResponse ENABL
