How to configure a multi-host computer to allow SQL Server access

When the server must provide a connection to two or more networks or network subnets, a typical solution is to use a multi-host computer. This computer is usually located in a peripheral network, also known as DMZ, a peripheral security area, or a shielded subnet. This topic describes how to configure SQL Server and Windows Firewall for Advanced Security in a multi-host environment to provide multiple network connections for SQL Server instances.

Note:

A multi-host computer has multiple network adapters or has been configured as a network adapter to use multiple IP addresses. The dual-host computer has two network adapters or is configured as a network adapter to use two IP addresses.
 

Before proceeding to this topic, you should be familiar with configuring Windows Firewall to allow SQL Server to access the information provided in the topic. This topic contains basic information about how SQL Server components are used with the firewall.

This example assumes that:

The computer running SQL Server is installed with Windows Server 2008 or Windows Vista. If you are running Windows Server 2003 or Windows XP, see the precautions for running Windows Server 2003 or Windows XP following this topic.

Two network adapters are installed on the computer. One or more network adapters can be wireless. You can use the IP address of a network adapter and the loopback IP address (127.0.0.1) as the second network adapter to simulate two network adapters.

For simplicity, the IPv4 address is used in this example. Use an IPv6 address to perform the same process.

Note:

An IPv4 address is a string of four numbers, which are eight bytes ). Each number is less than 255, separated by periods, such as 127.0.0.1. An IPv6 address is a string of eight hexadecimal numbers separated by a colon, such as fe80: 4898: 23: 3: 49a6: f5c1: 2452: b994.

Firewall Rules may allow access through a specific port such as port 1433, or allow access to the SQL Server database engine program (sqlservr.exe ). Neither method is better than the other method. Because servers in the peripheral network are more vulnerable to attacks than servers on the Intranet, this topic assumes that you want to perform more precise control and separately select open ports. For the above reason, this topic assumes that you will configure SQL Server as a listener fixed port. For more information about the ports used by SQL Server, see configure Windows Firewall to allow SQL Server access.

In this example, TCP port 1433 is used to configure access to the database engine. You can use the same general steps to configure other ports used by different SQL Server components.

The general steps in this example are as follows:

Determine the IP address of the computer.

Configure SQL Server to listen on a specific TCP port.

Configure Windows Firewall for Advanced Security.

Optional Process

If you already know the available IP address of your computer and SQL Server uses it, skip these steps.

Determine available IP addresses on the computer
On the computer where SQL Server is installed, click Start and run, type cmd, and click OK ".

In the Command Prompt window, Type ipconfig, and then press Enter to list available IP addresses on this computer.

Note:

The ipconfig command sometimes lists many possible connections, including disconnected connections. The ipconfig command can list both IPv4 and IPv6 addresses.
 

Note the IPv4 address and IPv6 address in use. Other information in the list, such as the temporary address, subnet mask, and default gateway, is important information for configuring the TCP/IP network. However, this information is not used in this example.

Determine the IP address and port used by SQL Server

Click Start, point to all programs, Microsoft SQL Server 2008 R2, and configuration tools, and then click SQL Server Configuration Manager ".

In the SQL Server Configuration Manager Console, expand "SQL Server network configuration" and "<Instance name> protocol", and double-click TCP/IP.

On the "IP address" tab of the "TCP/IP properties" dialog box, several IP addresses are displayed, in the format of IP1, IP2 ..., Until IPAll. One of these IP addresses is the IP address of the loop adapter (127.0.0.1 ). Other IP addresses are the IP addresses configured on the computer.

For any IP address, if the "TCP dynamic port" dialog box contains 0, it indicates that the database engine is listening for dynamic ports. In this example, use a fixed port instead of a dynamic port that will change during restart. Therefore, if the "TCP dynamic port" dialog box contains 0, delete 0.

Note the TCP ports listed for each IP address to be configured. In this example, we assume that both IP addresses listen on the default port 1433.

If you do not want SQL Server to use some available ports, change the "All listener" value to "no" on the "protocol" tab. On the "IP address" tab, change the "activity" value of the IP address you do not want to use to "no ".

Configure Windows Firewall for Advanced Security

After knowing the IP address used by the computer and the port used by SQL Server, you can create firewall rules and configure these rules for specific IP addresses.

Create firewall rules

Log on to the computer where SQL Server is installed as an administrator.

Click Start and run, type wf. msc, and click OK ".

In the "User Account Control" dialog box, click "continue" to use administrator creden。 to open the Windows Firewall management unit for Advanced Security.

On the overview page, confirm that Windows Firewall is enabled.

In the left pane, click inbound rules ".

Right-click "inbound rules" and click "new rule" to open the "new inbound rule wizard ".

You can create rules for SQL Server programs. However, because this example uses a fixed port, select "Port" and then click "Next ".

On the "protocol and port" Page, select TCP.

Select "specified local port ". Enter the port numbers separated by commas (,), and then click "Next ". In this example, you will configure the default port. Therefore, enter 1433.

On the "operations" Page, view the options. In this example, do not use the firewall to force a secure connection. Therefore, click "allow connection" and then click "Next ".

Note:

Your environment may require secure connections. If you select one of the security connection options, you may have to configure the certificate and forced encryption options. For more information about secure connections, see encrypt connections to SQL Server and how to enable encrypted connections to the Database Engine SQL Server Configuration Manager ).
 

On the configuration file page, select one or more configuration files for the rule. If you are not familiar with the firewall configuration file, click the "learn more about the configuration file" link in the firewall program.

If the computer is a server and only available when connected to the domain, select "Domain" and click "Next ".

If the computer is a mobile computer, such as a portable computer, it is likely to use multiple configuration files when connected to different networks. For mobile computers, different access functions can be configured for different configuration files. For example, access is allowed when the computer uses a domain configuration file, but not when the computer uses a public configuration file.

On the "name" Page, provide the rule name and description, and click "finish ".

Repeat this process to create another rule for each IP address that SQL Server will use.

After creating one or more rules, perform the following steps to configure each IP address on the computer as a rule.

Configure Firewall rules for specific IP addresses

On the "inbound rules" page of "Advanced Security Windows Firewall", right-click the rule you just created and click "properties ".

In the "rule properties" dialog box, select the "range" tab.

In the "local IP Address" area, select "The following IP addresses" and click "add ".

In the "IP address" dialog box, select "this IP address or subnet" and type one of the IP addresses to be configured.

Click OK ".

In the remote IP address area, select the following IP addresses and click Add ".

Use the "IP address" dialog box to configure the connection for the selected IP address on the computer. You can enable connections from a specific IP address, IP address in a certain range, the entire subnet, or from a specific computer. To correctly configure this option, you must be familiar with the network. For network information, contact the network administrator.

To close the "IP address" dialog box, click "OK", and then click "OK" to close the "rule attributes" dialog box.

To configure other IP addresses on multiple hosts, use another IP address and another rule to repeat this process.

Precautions for running Windows Server 2003 or Windows XP

Configure a multi-host computer that runs Windows Server 2003 or Windows XP in a way similar to configuring Windows Server 2003 and Windows Vista. However, because the Windows Firewall is unavailable, you must use different ports for each IP address. The general steps are as follows.

Configure Firewall Rules for the selected IP address in Windows Server 2003 or Windows XP
Configure the database engine to listen to multiple TDS endpoints. For more information, see how to configure the database engine to listen on multiple TCP ports.

Enable the SQL Server Configuration Manager to disable listening for all IP addresses by setting the "All listeners" option to "no.

Configure SQL Server to listen on different TCP ports of each IP address, and then restart SQL Server.

Use the Windows Firewall program to create firewall rules for each port, and use range settings to limit each port to the connection from the expected IP address, IP address of some ranges, the entire Subnet or a specific named computer.

Original article address

View more articles

Edit recommendations]