Configure windows firewall for sql server

Configure the Windows Firewall to allow SQL Server access

[!INCLUDE SQL Server-Windows Only]

Firewall systems help prevent unauthorized access to computer resources. If a firewall is turned on but not correctly configured, attempts to connect to [!INCLUDEssNoVersion] might be blocked.

To access an instance of the [!INCLUDEssNoVersion] through a firewall, you must configure the firewall on the computer that is running [!INCLUDEssNoVersion]. The firewall is a component of [!INCLUDEmsCoName] Windows. You can also install a firewall from another vendor. This article discusses how to configure the Windows Firewall, but the basic principles apply to other firewall programs.

[!NOTE]
This article provides an overview of firewall configuration and summarizes information of interest to a [!INCLUDEssNoVersion] administrator. For more information about the firewall and for authoritative firewall information, see the firewall documentation, such as Windows Firewall security deployment guide.

Users familiar with managing the Windows Firewall, and know which firewall settings they want to configure can move directly to the more advanced articles:

• Configure a Windows Firewall for Database Engine Access
• Configure the Windows Firewall to Allow Analysis Services Access
• Configure a Firewall for Report Server Access

Basic firewall information

Firewalls work by inspecting incoming packets, and comparing them against the following set of rules:

• The packet meets the standards dictated by the rules, then the firewall passes the packet to the TCP/IP protocol for more processing.
• The packet doesn’t meet the standards specified by the rules.
  • The firewall then discards the packet.- If logging is enabled, an entry is created in the firewall logging file.

The list of allowed traffic is populated in one of the following ways:

• Automatically: When a computer with a firewall enabled starts communication, the firewall creates an entry in the list so that the response is allowed. The response is considered solicited traffic, and there’s nothing that needs to be configured.

• Manually: An administrator configures exceptions to the firewall. It allows either access to specified programs or ports on your computer. In this case, the computer accepts unsolicited incoming traffic when acting as a server, a listener, or a peer. The configuration must be completed to connect to [!INCLUDEssNoVersion].

Choosing a firewall strategy is more complex than just deciding if a given port should be open or closed. When designing a firewall strategy for your enterprise, make sure you consider all the rules and configuration options available to you. This article doesn’t review all the possible firewall options. We recommend you review the following documents:

• Windows Firewall Deployment Guide
• Windows Firewall Design Guide
• Introduction to Server and Domain Isolation

Default firewall settings

The first step in planning your firewall configuration is to determine the current status of the firewall for your operating system. If the operating system was upgraded from a previous version, the earlier firewall settings may have been preserved. The Group Policy or Administrator can change the firewall settings in the domain.

[!NOTE]
Turning on the firewall will affect other programs that access this computer, such as file and print sharing, and remote desktop connections. Administrators should consider all applications that are running on the computer before adjusting the firewall settings.

Programs to configure the firewall

Configure the Windows Firewall settings with either Microsoft Management Console or netsh.

• Microsoft Management Console (MMC)

  The Windows Firewall with Advanced Security MMC snap-in lets you configure more advanced firewall settings. This snap-in presents most of the firewall options in an easy-to-use manner, and presents all firewall profiles. For more information, see Using the Windows Firewall with Advanced Security Snap-in later in this article.

• netsh

  The netsh.exe is an Administrator tool to configure and monitor Windows-based computers at a command prompt or using a batch file. By using the netsh tool, you can direct the context commands you enter to the appropriate helper, and the helper does the command. A helper is a Dynamic Link Library (.dll) file that extends the functionality. The helper provides: configuration, monitoring, and support for one or more services, utilities, or protocols for the netsh tool.

  All operating systems that support [!INCLUDEssNoVersion] have a firewall helper. [!INCLUDEwinserver2008] also has an advanced firewall helper called advfirewall. Many of the configuration options described can be configured by using netsh. For example, run the following script at a command prompt to open TCP port 1433:

  netsh firewall set portopening protocol = TCP port = 1433 name = SQLPort mode = ENABLE scope = SUBNET profile = CURRENT

  A similar example using the Windows Firewall for Advanced Security helper:

  netsh advfirewall firewall add rule name = SQLPort dir = in protocol = tcp action = allow localport = 1433 remoteip = localsubnet profile = DOMAIN

  For more information about netsh, see the following links:

  • Netsh Command Syntax, Contexts, and Formatting
  • How to use the «netsh advfirewall firewall» context instead of the «netsh firewall» context to control Windows Firewall behavior in Windows Server 2008 and in Windows Vista

• PowerShell

  See the following example to open TCP port 1433 and UDP port 1434 for [!INCLUDEssNoVersion] default instance, and [!INCLUDEssNoVersion] Browser Service:

  New-NetFirewallRule -DisplayName "SQLServer default instance" -Direction Inbound -LocalPort 1433 -Protocol TCP -Action Allow
  New-NetFirewallRule -DisplayName "SQLServer Browser service" -Direction Inbound -LocalPort 1434 -Protocol UDP -Action Allow

  For more examples, see New-NetFirewallRule.

• For Linux

  On Linux, you also need to open the ports associated with the services you need access to. Different distributions of Linux and different firewalls have their own procedures. For two examples, see SQL Server on Red Hat, and SQL Server on SUSE.

Ports used by SQL Server

The following tables can help you identify the ports being used by [!INCLUDEssNoVersion].

Ports used by the Database Engine

By default, the typical ports used by [!INCLUDEssNoVersion] and associated database engine services are: TCP 1433, 4022, 135, 1434, UDP 1434. The table below explains these ports in greater detail. A named instance uses Dynamic ports.

The following table lists the ports that are frequently used by the [!INCLUDEssDE].

For step-by-step instructions to configure the Windows Firewall for the [!INCLUDEssDE], see Configure a Windows Firewall for Database Engine Access.

Dynamic ports

By default, named instances (including [!INCLUDEssExpress]) use dynamic ports. means each time [!INCLUDEssDE] starts, it identifies an available port and uses that port number. If the named instance is the only instance of the [!INCLUDEssDE] installed, it will probably use TCP port 1433. If other instances of the [!INCLUDEssDE] are installed, it will probably use a different TCP port. Because the port selected might change every time that the [!INCLUDEssDE] is started, it’s difficult to configure the firewall to enable access to the correct port number. If a firewall is used, we recommend reconfiguring the [!INCLUDEssDE] to use the same port number every time. A fixed port or a static port is recommended. For more information, see Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Manager).

An alternative to configuring a named instance to listen on a fixed port is to create an exception in the firewall for a [!INCLUDEssNoVersion] program such as sqlservr.exe (for the [!INCLUDEssDE]). The port number won’t appear in the Local Port column of the Inbound Rules page when you’re using the Windows Firewall with Advanced Security MMC snap-in. It can be difficult to audit which ports are open. Another consideration is that a service pack or cumulative update can change the path to the [!INCLUDEssNoVersion] executable file and invalidate the firewall rule.

To add an exception for SQL Server using Windows Firewall with Advanced Security, see Use the Windows Firewall with Advanced Security snap-in later in this article.

Ports used by Analysis Services

By default, the typical ports used by [!INCLUDEssNoVersion] Analysis Services and associated services are: TCP 2382, 2383, 80, 443. The table below explains these ports in greater detail.

The following table lists the ports that are frequently used by [!INCLUDEssASnoversion].

If users access [!INCLUDEssASnoversion] through IIS and the Internet, you must open the port on which IIS is listening. Next, specify port in the client connection string. In this case, no ports have to be open for direct access to [!INCLUDEssASnoversion]. The default port 2389, and port 2382, should be restricted together with all other ports that aren’t required.

For step-by-step instructions to configure the Windows Firewall for [!INCLUDEssASnoversion], see Configure the Windows Firewall to Allow Analysis Services Access.

Ports used By Reporting Services

By default, the typical ports used by [!INCLUDEssNoVersion] Reporting Services and associated services are: TCP 80, 443. The table below explains these ports in greater detail.

The following table lists the ports that are frequently used by [!INCLUDEssRSnoversion].

When [!INCLUDEssRSnoversion] connects to an instance of the [!INCLUDEssDE] or [!INCLUDEssASnoversion], you must also open the appropriate ports for those services. For step-by-step instructions to configure the Windows Firewall for [!INCLUDEssRSnoversion], Configure a Firewall for Report Server Access.

Ports used by Integration Services

The following table lists the ports that are used by the [!INCLUDEssISnoversion] service.

For step-by-step instructions to configure the Windows Firewall for [!INCLUDEssISnoversion], see Integration Services Service (SSIS Service).

Other ports and services

The following table lists ports and services that [!INCLUDEssNoVersion] might depend on.

Special considerations for port 135

When you use RPC with TCP/IP or with UDP/IP as the transport, inbound ports are dynamically assigned to system services as required. TCP/IP and UDP/IP ports that are larger than port 1024 are used. The ports are referred to as «random RPC ports.» In these cases, RPC clients rely on the RPC endpoint mapper to tell them which dynamic ports were assigned to the server. For some RPC-based services, you can configure a specific port instead of letting RPC assign one dynamically. You can also restrict the range of ports that RPC dynamically assigns to a small range, independent of the service. Because port 135 is used for many services, it’s frequently attacked by malicious users. When opening port 135, consider restricting the scope of the firewall rule.

For more information about port 135, see the following references:

• Service overview and network port requirements for the Windows Server system
• Remote procedure call (RPC)
• How to configure RPC dynamic port allocation to work with firewalls

Interaction with other firewall rules

The Windows Firewall uses rules and rule groups to establish its configuration. Each rule or rule group is associated with a particular program or service, and that program or service might modify or delete that rule without your knowledge. For example, the rule groups World Wide Web Services (HTTP) and World Wide Web Services (HTTPS) are associated with IIS. Enabling those rules will open ports 80 and 443, and [!INCLUDEssNoVersion] features that depend on ports 80 and 443 will function if those rules are enabled. However, administrators configuring IIS might modify or disable those rules. If you’re using port 80 or port 443 for [!INCLUDEssNoVersion], you should create your own rule or rule group that maintains your preferred port configuration independently of the other IIS rules.

The Windows Firewall with Advanced Security MMC snap-in allows any traffic that matches any applicable allow rule. So if there are two rules that both apply to port 80 (with different parameters). Traffic that matches either rule will be permitted. So if one rule allows traffic over port 80 from local subnet and one rule allows traffic from any address, the net effect is that all traffic to port 80 is independent of the source. To effectively manage access to [!INCLUDEssNoVersion], administrators should periodically review all firewall rules enabled on the server.

Overview of firewall profiles

Firewall profiles are used by the operating systems to identify and remember each of the networks by: connectivity, connections, and category.

There are three network location types in Windows Firewall with Advanced Security:

• Domain: Windows can authenticate access to the domain controller for the domain to which the computer is joined.
• Public: Other than domain networks, all networks are initially categorized as public. Networks that represent direct connections to the Internet or are in public locations, such as airports and coffee shops should be left public.
• Private: A network identified by a user or application as private. Only trusted networks should be identified as private networks. Users will likely want to identify home or small business networks as private.

The administrator can create a profile for each network location type, with each profile containing different firewall policies. Only one profile is applied at any time. Profile order is applied as follows:

1. The domain profile is applied if all interfaces are authenticated to the domain controller where the computer is a member.
2. If all interfaces are either authenticated to the domain controller or are connected to networks that are classified as private network locations, the private profile is applied.
3. Otherwise, the public profile is applied.

Use the Windows Firewall with Advanced Security MMC snap-in to view and configure all firewall profiles. The Windows Firewall item in Control Panel only configures the current profile.

Additional firewall settings using the Windows Firewall item in Control Panel

The added firewall can restrict the opening of the port to incoming connections from specific computers or local subnet. Limit the scope of the port opening to reduce how much your computer is exposed to malicious users.

[!NOTE]
Using the Windows Firewall item in Control Panel only configures the current firewall profile.

Change the scope of a firewall exception using the Windows Firewall item in Control Panel

1. In the Windows Firewall item in Control Panel, select a program or port on the Exceptions tab, and then select Properties or Edit.

2. In the Edit a Program or Edit a Port dialog box, select Change Scope.

3. Choose one of the following options:

   • Any computer (including computers on the Internet): Not recommended. Any computer that can address your computer to connect to the specified program or port. This setting might be necessary to allow information to be presented to anonymous users on the internet, but increases your exposure to malicious users. Enabling this setting an allow Network Address Translation (NAT) traversal, such as the Allow edge traversal option will increase exposure.

   • My network (subnet) only: A more secure setting than Any computer. Only computers on the local subnet of your network can connect to the program or port.

   • Custom list: Only computers that have the IP addresses listed can connect. A secure setting can be more secure than My network (subnet) only, however, client computers using DHCP can occasionally change their IP address; will disable the ability to connect. Another computer, which you had not intended to authorize, might accept the listed IP address and connect to it. The Custom list is appropriate for listing other servers that are configured to use a fixed IP address.

     IP addresses can be spoofed by an intruder. Restricting firewall rules are only as strong as your network infrastructure.

Use the Windows Firewall with Advanced Security snap-in

Advanced firewall settings can be configured by using the Windows Firewall with Advanced Security MMC snap-in. The snap-in includes a rule wizard and settings that aren’t available in the Windows Firewall item in Control Panel. These settings include:

• Encryption settings
• Services restrictions
• Restricting connections for computers by name
• Restricting connections to specific users or profiles
• Edge traversal allowing traffic to bypass Network Address Translation (NAT) routers
• Configuring outbound rules
• Configuring security rules
• Requiring IPsec for incoming connections

Create a new firewall rule using the New Rule wizard

1. On the Start menu, select Run, type WF.msc, and then select OK.
2. In the Windows Firewall with Advanced Security, in the left pane, right-click Inbound Rules, and then select New Rule.
3. Complete the New Inbound Rule Wizard using the settings that you want.

Add a program exception for the SQL Server executable

1. From the start menu, type wf.msc. Press Enter or select the search result wf.msc to open Windows Defender Firewall with Advanced Security.

2. In the left pane, select Inbound rules.

3. In the right pane, under Actions, select New rule…. New Inbound Rule Wizard opens.

4. On Rule type, select Program. Select Next.

5. On Program, select This program path. Select Browse to locate your instance of [!INCLUDEssNoVersion]. The program is called sqlservr.exe. It’s normally located at:

   C:Program FilesMicrosoft SQL ServerMSSQL<VersionNumber>.<InstanceName>MSSQLBinnsqlservr.exe

   Select Next.

6. On Action, select Allow the connection. Select Next.

7. On Profile, include all three profiles. Select Next.

8. On Name, type a name for the rule. Select Finish.

For more information about endpoints, see:

• Configure the Database Engine to Listen on Multiple TCP Ports
• Endpoints Catalog Views (Transact-SQL)

Troubleshoot firewall settings

The following tools and techniques can be useful in troubleshooting firewall issues:

• The effective port status is the union of all rules related to the port. It can be helpful to review all the rules that cite the port number, when trying to block access to a port. Review the rules with the Windows Firewall with Advanced Security MMC snap-in and sort the inbound and outbound rules by port number.

• Review the ports that are active on the computer on which [!INCLUDEssNoVersion] is running. The review process includes verifying which TCP/IP ports are listening and also verifying the status of the ports.

• The PortQry utility can be used to report the status of TCP/IP ports as listening, not listening, or filtered.
  (The utility may not receive response from the port if it has a filtered status.)
  The PortQry utility is available for download from the Microsoft Download Center.

List which TCP/IP ports are listening

To verify which ports are listening, display active TCP connections and IP statistics use the netstat command-line utility.

1. Open the Command Prompt window.

2. At the command prompt, type netstat -n -a.

   The -n switch instructs netstat to numerically display the address and port number of active TCP connections. The -a switch instructs netstat to display the TCP and UDP ports on which the computer is listening.

See also

• Service overview and network port requirements for the Windows Server system
• How to: Configure Firewall Settings (Azure SQL Database)