Skip to main content This browser is no longer supported. Show
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support. Configure the Windows Firewall to Allow SQL Server Access
In this articleApplies to: SQL Server (all supported versions) - Windows onlyFirewall systems help prevent unauthorized access to computer resources. If a firewall is turned on but not correctly configured, attempts to connect to SQL Server might be blocked. To access an instance of the SQL Server through a firewall, you must configure the firewall on the computer that is running SQL Server. The firewall is a component of Microsoft Windows. You can also install a firewall from another company. 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 SQL Server 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:
Basic Firewall InformationFirewalls work by inspecting incoming packets, and comparing them against the following set of rules:
The list of allowed traffic is populated in one of the following ways:
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 Default Firewall SettingsThe 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 FirewallConfigure the Windows Firewall settings with either Microsoft Management Console or netsh.
Ports Used By SQL ServerThe following tables can help you identify the ports being used by SQL Server. Ports Used By the Database EngineBy default, the typical ports used by SQL Server 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 Database Engine.
For step-by-step instructions to configure the Windows Firewall for the Database Engine, see Configure a Windows Firewall for Database Engine Access. Dynamic PortsBy default, named instances (including SQL Server Express) use dynamic ports. means each time Database Engine starts, it identifies an available port and uses that port number. If the named instance is the only instance of the Database Engine installed, it will probably use TCP port 1433. If other instances of the Database Engine are installed, it will probably use a different TCP port. Because the port selected might change every time that the Database Engine 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 Database Engine 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 SQL Server program such as sqlservr.exe (for the Database Engine). 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 SQL Server executable file and invalidate the firewall rule. To add a program exception to the firewall using Windows Defender Firewall with Advanced Security
For more information about endpoints, see Configure the Database Engine to Listen on Multiple TCP Ports and Endpoints Catalog Views (Transact-SQL). Ports Used By Analysis ServicesBy default, the typical ports used by SQL Server 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 Analysis Services.
If users access Analysis Services 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 Analysis Services. 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 Analysis Services, see Configure the Windows Firewall to Allow Analysis Services Access. Ports Used By Reporting ServicesBy default, the typical ports used by SQL Server 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 Reporting Services.
When Reporting Services connects to an instance of the Database Engine or Analysis Services, you must also open the appropriate ports for those services. For step-by-step instructions to configure the Windows Firewall for Reporting Services, Configure a Firewall for Report Server Access. Ports Used By Integration ServicesThe following table lists the ports that are used by the Integration Services service.
For step-by-step instructions to configure the Windows Firewall for Integration Services, see Integration Services Service (SSIS Service). Other Ports and ServicesThe following table lists ports and services that SQL Server might depend on.
Special Considerations for Port 135When 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:
Interaction with Other Firewall RulesThe 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 SQL Server 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 SQL Server, 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 SQL Server, administrators should periodically review all firewall rules enabled on the server. Overview of Firewall ProfilesFirewall 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:
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:
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 PanelThe 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. To change the scope of a firewall exception using the Windows Firewall item in Control Panel
Using the Windows Firewall with Advanced Security Snap-inAdvanced 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:
To create a new firewall rule using the New Rule wizard
Troubleshooting Firewall SettingsThe following tools and techniques can be useful in troubleshooting firewall issues:
See AlsoService overview and network port requirements for the Windows Server system FeedbackSubmit and view feedback for Which of the following does ssh not protect against?SSH does not protect against: A malicious user gaining root access to host (remote) machine. A malicious user gaining root access to the recipient machine.
Which network management protocol provides for both authentication and encryption?IPsec protocols
IPsec authenticates and encrypts data packets sent over both IPv4- and IPv6-based networks. IPsec protocol headers are found in the IP header of a packet and define how the data in a packet is handled, including its routing and delivery across a network.
Why is the telnet utility a poor choice for remote access to a device quizlet?Why is the telnet utility a poor choice for remote access to a device? It provides poor authentication and no encryption. An enterprise-wide VPN can include elements of both the client-to-site and site-to-site models.
Why is telnet a poor choice for remote access?10. Why is the telnet utility a poor choice for remote access to a device? d. It provides poor authentication and no encryption.
|