Firewall settings for SQL Server

Posted on 5/6/2013 @ 4:00 PM in #Bak2Basics by | Feedback | 2892 views

A while ago, I had blogged a quick fix for the SQL Server firewall warning when setting up SharePoint.

Firewall settings for SQL Server is a very complex topic. The specific firewall settings you may need to do, really depend on the feature set, configuration, and even your network topology. Here is some high level description though. To add to that, all these ports are configurable, especially once you consider the multitude of security devices in a real environment. A lot of these apply to SharePoint.

For the basic database engine, you need,

 

Feature

Port

Comments

SQL Server default instance running over TCP

TCP port 1433

Applicable if there is only one instance of SQL Server running on the machine. Multiple named instances will use dynamic ports. If you do need multiple instances, you should configure them to listen on a specific TCP port (how)

Multiple SQL Server named instances

The TCP port is a dynamic port determined at the time the Database Engine starts.

UDP port 1434 might be required for the SQL Server Browser Service when you are using named instances.

Dedicated Admin Connection

TCP port 1434 for the default instance. Other ports are used for named instances.

You have to enable DAC, disabled by default.

SQL Server Browser service

UDP port 1434

 

SQL Server instance running over an HTTP endpoint.

TCP 80 or 443 (SSL)

Used for an HTTP connection through a URL.

Service Broker

Usually, TCP port 4022.

This is a weirdity. There is no default port for it, but you can find what port being used by running,

SELECT name, protocol_desc, port, state_desc

FROM sys.tcp_endpoints

WHERE type_desc = 'SERVICE_BROKER'

Database Mirroring

Administrator chosen port.

You can find what port is being used by running,

SELECT name, protocol_desc, port, state_desc FROM sys.tcp_endpoints

WHERE type_desc = 'DATABASE_MIRRORING'

Replication

TCP 1433 (regular TSQL port), but web synch and FTP/UNC will require additional ports (80, 21, 137, 138 or 139 – depending upon what you set it up as)

 

TSQL Debugger

TCP port 135

 

For Analysis services, you need

Feature

Port

Comments

Analysis Services

TCP port 2383

The standard port for the default instance of Analysis Services.

SQL Server Browser service

TCP port 2382

 

Analysis Services configured for use through IIS/HTTP

TCP port 80 o4 443

Used for an HTTP connection through a URL. The PivotTable Service uses HTTP or HTTPS

Other ports you may need,

· For Reporting services, you need Port 80 and 443 (http based connections).

· For integration services, you need TCP port 135 (MS-RPC/DCOM over port 135).

· WMI will need 135, SQL Server management studio will use this.

· MSDTC uses TCP port 135

· UDP port 1434 is used for SQL Server browser service

· IPSec traffic will need UDP 500 and UDP 4500

· You will need to configure your firewall for domains and trusts (how)

· Clustering is a port-disaster. Clustering will need 135 TCP and UDP for DCOM, 3343 for the cluster network driver, 445 for SMB, and 139 for NetBIOS. Additionally, if you see event log error 1721 occur, you need to open ports 5000-5099 since the cluster service requires 100 ports for communication via RPC. If nodes are separated by firewalls, you’ll need to open ports 8011-8031 for internode RPC connectivity.

Sound off but keep it civil:

Older comments..