Configure a Multi-Homed Computer for SQL Server Access

Article
06/13/2017

When a server must provide a connection to two or more networks or network subnets, a typical scenario uses a multi-homed computer. Frequently this computer is located in a perimeter network (also known as DMZ, demilitarized zone, or screened subnet). This topic describes how to configure SQL Server and Windows Firewall with Advanced Security to provide for network connections to an instance of SQL Server in a multi-homed environment.

Note

A multi-homed computer has multiple network adapters or has been configured to use multiple IP addresses for a single network adapter. A dual-homed computer has two network adapters or has been configured to use two IP addresses for a single network adapter.

Before you continue in this topic, you should be familiar with the information provided in the topic Configure the Windows Firewall to Allow SQL Server Access. This topic contains basic information about how SQL Server components work with the firewall.

Assumptions for this example:

There are two network adapters installed in the computer. One or more of the network adapters can be wireless. You can simulate having two network adapters by using the IP address of one network adapter, and using the loopback IP address (127.0.0.1) as the second network adapter.
For simplicity, this example uses IPv4 addresses. The same procedures can be performed by using IPv6 addresses.

Note

IPv4 addresses are a series of four numbers known as octets. Each number is less than 255, separated by periods, such as 127.0.0.1. IPv6 addresses are a series of eight hexadecimal numbers separated by colons, such as fe80:4898:23:3:49a6:f5c1:2452:b994.
Firewall rules could allow access through a specific port, such as port 1433. Or firewall rules could allow access to the SQL Server Database Engine program (sqlservr.exe). Neither method is better than the other. Because a server in a perimeter network is more vulnerable to attack than servers on an intranet, this topic assumes that you want to have more precise control, and individually select the ports that you open. For that reason, this topic assumes that you will configure SQL Server to listen on a fixed port. For more information about the ports that SQL Server uses, see Configure the Windows Firewall to Allow SQL Server Access.
This example configures access to the Database Engine by using TCP port 1433. The other ports that are the different SQL Server components use can be configured by using the same general steps.

The general steps in this example are as follows:

Determine the IP addresses on the computer.
Configure SQL Server to listen on a specific TCP port.
Configure Windows Firewall with Advanced Security.

Optional Procedures

If you already know the IP addresses available to your computer and that are used by SQL Server, you can skip these procedures.

To determine the IP addresses available on the computer

On the computer on which SQL Server is installed, click Start, click Run, type cmd and then Click OK..
In the Command Prompt window, type ipconfig, and then press ENTER to list the IP addresses available on this computer.

Note

The ipconfig command sometimes lists many possible connections, including connections that are disconnected. The ipconfig command can list both IPv4 and IPv6 addresses.
Note the IPv4 addresses and IPv6 addresses that are being used. The other information in the list, such as temporary addresses, subnet masks, and default gateways is important information for configuring a TCP/IP network. But this information is not used in this example.

To determine the IP addresses and ports used by SQL Server

Click Start, point to All Programs, point to Microsoft SQL Server 2014, point to Configuration Tools, and then click SQL Server Configuration Manager.
In SQL Server Configuration Manager, in the console pane, expand SQL Server Network Configuration, expand Protocols for <instance name>, and then double-click TCP/IP.
In the TCP/IP Properties dialog box, on the IP Addresses tab, several IP addresses appear in the format IP1, IP2, up to IPAll. One of these is for the IP address of the loopback adapter, 127.0.0.1. Additional IP addresses appear for each IP Address configured on the computer.
For any IP address if the TCP Dynamic Ports dialog box contains 0, this indicates that the Database Engine is listening on dynamic ports. This example uses fixed ports instead of dynamic ports which could change upon restart. Therefore if the TCP Dynamic Ports dialog box contains 0, delete the 0.
Note the TCP port that is listed for each IP address that you want to configure. For this example, assume that both IP addresses are listening on the default port, 1433.
If you do not want SQL Server to use some of the available ports, on the Protocol tab, change the Listen All value to No; and on the IP Addresses tab, change the Active value to No for the IP addresses that you do not want to use.

Configuring Windows Firewall with Advanced Security

After you know the IP addresses that the computer uses and the ports that SQL Server uses, you can create firewall rules, and then configure those rules for specific IP addresses.

To create a firewall rule

On the computer on which SQL Server is installed, log on as an administrator..
Click Start, click Run, type wf.msc, and click OK.
In the User Account Control dialog box, click Continue to use the Administrator credentials to open the Windows Firewall with Advanced Security snap-in.
On the Overview page, confirm that the Windows Firewall is enabled.
In the left pane, click Inbound Rules.
Right-click Inbound Rules, and then click New Rule to open the New Inbound Rule Wizard.
You could create a rule for the SQL Server program. However, because this example uses a fixed port, select Port, and then click Next.
On the Protocols and Ports page, select TCP.
Select Specified local ports. Type the port numbers separated by commas, and then click Next. In this example, you will configure the default port; therefore, enter 1433.
On the Action page, review the options. In this example, you are not using the firewall to force secure connections. Therefore, click Allow the connection, and then click Next.

Note

Your environment might require secure connections. If you select one of the secure connections options, you might have to configure a certificate and the Force Encryption option. For more information about secure connections, see Enable Encrypted Connections to the Database Engine (SQL Server Configuration Manager) and Enable Encrypted Connections to the Database Engine (SQL Server Configuration Manager).
On the Profile page, select one or more profiles for the rule. If you are unfamiliar with firewall profiles, click the Learn more about profiles link in the firewall program.
- If the computer is a server and is available only when it is connected to a domain, select Domain, and then click Next.
- If the computer is a mobile computer (for example a laptop), it is likely to use multiple profiles when it connects to different networks. For a mobile computer, you can configure different access capabilities for different profiles. For example, you might allow access when the computer uses the Domain profile but not allow access when it uses the Public profile.
On the Name page, provide a name and description for the rule, and then click Finish.
Repeat this procedure to create another rule for each IP address that SQL Server will use.

After you have created one or more rules, perform the following steps to configure each IP address on the computer to use a rule.

To configure the firewall rule for a specific IP addresses

On the Inbound Rules page of the Windows Firewall with Advanced Security, right-click the rule that you just created, and then click Properties.
In the Rule Properties dialog box, select the Scope tab.
In the Local IP address area, select These IP addresses, and then click Add.
In the IP Address dialog box, select This IP address or subnet, and then type one of the IP addresses that you want to configure.
Click OK.
In the Remote IP address area, select These IP addresses, and then click Add.
Use the IP Address dialog box to configure connectivity for the selected IP address on the computer. You can enable connections from specified IP addresses, ranges of IP addresses, whole subnets, or from certain computers. To configure this option correctly, you must have a good understanding of the network. For information about the network, see the network administrator.
To close the IP Address dialog box, click OK; and then click OK to close the Rule Properties dialog box.
To configure the other IP addresses on a multi-homed computer, repeat this procedure by using another IP address and another rule.

Share via