Firewall – SQL Server Reporting Services (SSRS) IP Handling on Multi-Instance Servers

firewallipsql serverssrswindows-server-2012-r2

Tl;Dr

I have a SQL Server instance (SQLSERVER01-i01) with a dedicated IP address and port (162.xxx.xxx.51:1433) on a multi-instance SQL Server (each SQL Server instance on the Windows Server has its own IP address) which are all running on one Windows server (SQLSERVER01 / 162.xxx.xxx.50).

I also have a dedicated Reporting Services instance (SQLSERVERRS01-i01) with its own IP address and port (168.xxx.xxx.71:1433), which is running on a different Windows server (SQLSERVERRS01) with its own IP address (168.xxx.xxx.70).

The dedicated Reporting Services server has an application APPL1 which can be reached either via http://SQLSERVERRS01-i01:80/Reports_APPL1 or via http://SQLSERVERRS01:80/Reports_APPL1.

SSRS will pick up both requests because of the *:80configuration in the Reporting Services Configuration for the host headers.

We have multiple firewalls between each IP range, which means we have to apply for a specific rule for each IP-to-IP or IPrange-to-IP connection. However when two servers are involved, then security dictates that it always has to be an IP-to-IP rule in the firewall.

Question

(based on screen shot further down)

When the Reporting Services server connects to the SQL Server instance (on 162.xxx.xxx.51) to retrieve data, will it always build a connection with the underlying IP address of the Windows server (168.xxx.xxx.70 / preferred) that SSRS is running on, or will it (sometimes) use the IP address of the SQL Server Reporting Services instance (168.xxx.xxx.71)?

This is relevant for the configuration of the firewall rule using an IP-to-IP approach. I will either have to apply for a rule that defines a 168.xxx.xxx.71 to 162.xxx.xxx.51 connection via port 1433 or a 168.xxx.xxx.70 to 162.xxx.xxx.51 connection via port 1433.

Currently I would apply for both firewall rules.

Bonus Question

Can I configure the Reporting Services server to communicate with a dedicated IP address? In this case with the 168.xxx.xxx.71 address.

Answers I am not looking for

I am not seeking advice on how to optimize the firewall configuration or how to implement a zoning concept for our networks. (It's already in the pipeline). Additionally I am not interested in feedback suggesting that having SQL Server and SSRS on the same server would resolve my issues. I know that and would gladly do it but for the third-party software required to run together with the SSRS components.

It works

The configuration I have works if I apply both firewall rules between the SSRS and SQL Server instance. 

168.xxx.xxx.71 --> 162.xxx.xxx.51 : 1433
168.xxx.xxx.70 --> 162.xxx.xxx.51 : 1433

I want to safely reduce by one firewall rule and ensure that everything will still work. (See screenshot further down)
Edit: The articles I have read so far are suggesting I only need the second rule, but there is no guarantee.

Articles I have already consulted

  1. Security Considerations for a SQL Server Installation
    Base article.

  2. Configure the Windows Firewall to Allow SQL Server Access
    This article points to all other articles regarding firewall configuration for SQL Server.

  3. Configure a Windows Firewall for Database Engine Access
    No word of IP addresses used.

  4. Configure a Firewall for Report Server Access
    This article was pretty interesting as it noted:

    If you are accessing SQL Server relational databases on external computers, or if the report server database is on an external SQL Server instance, you must open port 1433 and 1434 on the external computer.

    …but still not a word about IP configuration/settings/defaults.

  5. Source IP address selection on a Multi-Homed Windows Computer

  6. The functionality for source IP address selection in Windows Server 2008 and in Windows Vista differs from the corresponding functionality in earlier versions of Windows

Articles 5 & 6 were kindly provided to me by James(dba.se). They currently seem to be the most appropriate answers. I am however a bit sceptical that one article mentions the use of multiple NICs whereas I have only one NIC with multiple IPs assigned to it. Tom(dba.se) has also chipped in with advice and general comments.

Why here and not in dba.stackexchange.com

I was reluctant at first to post this question in serverfault.com because of the complex nature of the question. The question has both tendencies to be SQL Server specific, but also to be Windows Server specific. Eventually I decided to post it here, because I think it is a Windows Server IP handling thingy (for loss of better words).

If a moderator thinks that I will get a better response in dba.stackexchange.com then please move the question over there.

The Long Explanation

In our environment we have Windows servers hosting multiple SQL Server instances and multiple IP settings. We add complex firewall configurations, dedicated SQL Server Reporting Services (SSRS) servers and come up with an environment that looks a bit like this:

Environment Overview

Basically we can have one Windows Server running up to 15 (fifteen) SQL Server instances on individual IP addresses. The same is valid for the dedicated Reporting Services instance.

Firewall Rules

The different IP ranges are currently not configured as zones, which means we have to configure each firewall rule independently as an IP-to-IP or IPrange-to-IP rule. When two servers are involved, then security dictates that it always has to be an IP-to-IP rule. Each SQL Server instance will have its own set of rules for the firewalls involved in communications, be this a server-to-server or client-to-server link. Applying for a firewall rule currently incurs a four to six week waiting period. Reducing the amount of firewall rules will reduce the amount of pressure on the network security team.

SQL Server Instance IP Configuration

Configuring a SQL Server instance to pick up only on a dedicated IP and port is performed by modifying some settings in the SQL Server Configuration Manager utility. The first step is to start the SQL Server Configuation Manager and in the left section select the SQL Server Network Configuration | Protocols for InstanceName. In the left pane left-click the TCP/IP Protocol Name and Enable the protocol. Then left-click the protocol again and bring up the Properties for TCP/IP window.

Then ensure the following settings are set in the Protocol register:

Enabled           : Yes
Listen All        : No

In the IP Adresses register check the following settings for the IP address in question (e.g. for the Reporting Services server in this example it would be for 168.xxx.xxx.71)

Active            : Yes
Enabled           : Yes
IP Address        : 168.xxx.xxx.71
TCP Dynamic Ports : 
TCP Port          : 1433

Note: It is important that the setting for TCP Dynamic Ports is empty not just a 0 (zero).

Now you have a SQL Server instance that will only pickup database connections on 168.xxx.xxx.71 using the port 1433.

SQL Server Instance Summary

The SQL Server Browser service is not running and each individual SQL Server instance is configured to use only its own IP address on port 1433. Given a SQL Server instance called GENERAL, a Windows server with the host name SQLSERVER01 and two IP addresses 162.xxx.xxx.50 (host) and 162.xxx.xxx.51 (SQL Instance) I will end up with the following configuration items:

Windows Server      : SQLSERVER01 
Windows Server IP   : 162.xxx.xxx.50
SQL Server Instance : SQLSERVER01-i01 (DNS A record)
SQL Server Instance : GENERAL (can only be used on the host itself)
SQL Server IP/Port  : 162.xxx.xxx.51:1433

The SQL Server will not pick up requests for 162.xxx.xxx.50:1433, because no SQL Server instance is configured to listen on this IP address in the SQL Server Configuration Manager utility. The SQL Server will only pick up requests for SQLSERVER01-i01 (on port 1433) or 162.xxx.xxx.51,1433.

SQL Server Reporting Services Instance Summary

The SQL Server Browser service is not running and each individual SQL Server Reporting Services instance is configured to use only its own IP address on port 1433. Given a SQL Server Reporting Services instance called GENERAL, a Windows server with the host name SQLSERVERRS01, an application on the SSRS named APPL1 and two IP addresses 168.xxx.xxx.70 (host) and 168.xxx.xxx.71 (SQL Instance) I will end up with the following configuration items:

Windows Server      : SQLSERVERRS01 
Windows Server IP   : 168.xxx.xxx.70
SQL Server Instance : SQLSERVERRS01-i01 (DNS A record)
SQL Server Instance : GENERAL (can only be used on the host itself)
SQL Server IP/Port  : 168.xxx.xxx.71:1433
Reporting Services  : http://sqlserverrs01-i01/Reports_APPL1
                      http://sqlserverrs01/Reports_APPL1

The SQL Server will not pick up requests for 168.xxx.xxx.70:1433, because no SQL Server instance is configured to listen on this IP address in the SQL Server Configuration Manager utility. The SQL Server will only pick up requests for SQLSERVER01-i01 (on port 1433) or 162.xxx.xxx.71,1433.

SSRS will pick up requests for either http://sqlserverrs01-i01/Reports_APPL1 or http://sqlserverrs01/Reports_APPL1 because of the *:80configuration in the Reporting Services Configuration for the host headers.

I hope I have supplied enough information for anybody willing to spend their time writing an answer and I look forward to your technical details and links.

Written with StackEdit and later manually modified to be stackexchange compatible.

History

Edit 1: Initial Release
Edit 2: Reformatted for readability. Moved explanation SF / DB down. Added host name for Windows Server
Edit 3: Fixed wrong IP addresses in firewall rules listing.
Edit 4: changed the word hosting to running (it's a non-virtualized environment) in some places. Added IP address in once sentence
Edit 5: Added a list of articles I have already consulted and referenced support
Edit 6: Cleaned up History section

Best Answer

Introduction

According to the various documents I found during my initial research and the documents provided in links and discussions I have come up with a solid, reliable, compliant solution.

RFC 3484

The binary comparisons conducted further down and the rules applied are according to RFC 3484 which is apparently also valid for IPv4 addresses.

RFC 3484 also states just after Rule 8 that

Rule 8 may be superseded if the implementation has other means of
choosing among source addresses.  For example, if the implementation
somehow knows which source address will result in the "best"
communications performance.

Source IP address selection on a Multi-Homed Windows Computer

Now not all rules in the RFC 3484 apply to IPv4 addresses. The Microsoft Blog article Source IP address selection on a Multi-Homed Windows Computer explains which rules apply.

There is a small section just below the Windows Vista/Windows Server 2008 Behaviour that reads:

Similar to XP when if a program doesn’t specify a source IP, the stack references the target IP address, and then examines the entire IP route table so that it can choose the best network adapter over which to send the packet. After the network adapter has been chosen, the stack uses the address selection process defined in RFC 3484 and uses that IP address as the source IP address for the outbound packets.

Seeing as I have only one NIC in the SQL/SSRS instance the first part is moot. Windows Server will always chose the only NIC available.

So far combining RFC 3484 with the Microsoft Blog results in both IP addresses being valid candidates for the source IP address. The explanation follows further down in the answer.

The Cable Guy

An article from the Cable Guy The Cable Guy Strong and Weak Host Models goes into more details on how the IP selection works in a Strong Host Sending and Receiving environment and in a Weak Host Sending and Receiving environment. A good additional read, but sheds no more light on how the source IP is selected. The article relates to the already known RFC 3484.

Explaining the unexplainable

In order to explain the solution we first have to convert the IP addresses in question to their binary equivalents. Seeing as I did not provide gateways in my question I will assume two values.

Source IP Addresses and Binary Notation

Here is a list of the converted binary values for the IP addresses involved. 

10101000.00000001.00000001.01000110   168.xxx.xxx.070/128   Windows Server
10101000.00000001.00000001.01000111   168.xxx.xxx.071/128   SQL Server / SSRS Instance
10101000.00000001.00000001.00000010   168.xxx.xxx.002/128   Gateway (Assumption 1)
10101000.00000001.00000001.01100010   168.xxx.xxx.100/128   Gateway (Assumption 2)
11111111.11111111.11111111.10000000   255.255.255.128/025   Subnet Mask / CIDR

Target IP Addresses and Binary Notation

10101000.00000000.00000000.00110011   168.xxx.xxx.051/128   SQL Server

Example 1 : Gateway IP lower than SQL/SSRS Instance IP

In this example I am going to assume that the IP address of the gateway is lower than the IP address of the SQL Server / SSRS instance, namely 168.001.001.002.

If you compare both binary addresses of the Windows Server and SQL Server / SSRS instance, then you have the following:

SQL/SSRS Instance IP
10101000.00000001.00000001.00000010 (Gateway Assumption 1)
10101000.00000001.00000001.01000111 (SQL/SSRS)
-----------------------------------
xxxxxxxx.xxxxxxxx.xxxxxxxx.x------- (x=matching high order bits)

Window Server IP
10101000.00000001.00000001.00000010 (Gateway Assumption 1)
10101000.00000001.00000001.01000110 (Windows)
-----------------------------------
xxxxxxxx.xxxxxxxx.xxxxxxxx.x------- (x=matching high order bits)

Result Example 1

In this example both IP addresses have the same amount of matching high order bits (or longest matching prefix). Until now the http.sys process will use either of the IP addresses for outgoing communications.

Example 2 : Gateway IP higher than SQL/SSRS Instance IP

In this example I am going to assume that the IP address of the gateway is higher than the IP address of the SQL Server / SSRS instance, namely 168.001.001.100.

If you compare both binary addresses of the Windows Server and SQL Server / SSRS instance, then you have the following:

SQL/SSRS Instance IP
10101000.00000001.00000001.00000010 (Gateway Assumption 2)
10101000.00000001.00000001.01100010 (SQL/SSRS)
-----------------------------------
xxxxxxxx.xxxxxxxx.xxxxxxxx.x------- (x=matching high order bits)

Windows Server IP
10101000.00000001.00000001.00000010 (Gateway Assumption 2)
10101000.00000001.00000001.01100010 (Windows)
-----------------------------------
xxxxxxxx.xxxxxxxx.xxxxxxxx.x------- (x=matching high order bits)

Result Example 2

Even though the IP address of the gateway is now higher than the IP address of the Windows server and the SQL/SSRS instance, the amount of matching high order bits (or longest matching prefix) are still the same. Until now the http.sys process will use either of the IP addresses for outgoing communications.

Summary of Findings So Far

So far, it is impossible to tell which IP address the http.sys process will use for outgoing communications running on the SQL/SSRS instance (.71) on the windows server (.70).

"When you have eliminated the impossible, whatever remains, however improbable, must be the truth" - Sherlock Holmes

There are situations where the source IP address can definitely be pin-pointed/selected/defined with the aforementioned RFC and Microsoft knowledge. But if the IP addresses are just too near to each other and near the gateway, well it's all just down to luck. Or is it?

Seeing as I am in the position of making the (firewall) rules and Microsoft has an ...

implementation (that) has other means of choosing among source addresses. For example, if the implementation somehow knows which source address will result in the "best" communications performance.

...then all I have to do to determine the IP address of the http.sys process is to create only one firewall rule with the desired IP address.

What happens

  1. I define a firewall rule from 168.xxx.xxx.71 to 168.xxx.xxx.51:1433
  2. The http.sys component of the SQL/SSRS instance complies with RFC 3484 and selects the source IP according to the defined rules
  3. The IP address 168.xxx.xxx.71 (on NIC1) is determined as the source IP address to reach the IP address 168.xxx.xxx.51 via port 1433 and is thus assigned to all outgoing packets

Benefits

  1. I am in no way interfering with the implementation of RFC 3484
  2. I am in no way juggling with routes or ARP configurations
  3. I am in compliance with RFC 3484 and Microsoft's implementation
  4. I am not hacking any registry settings or system configurations
  5. I HAVE ONE FIREWALL RULE LESS

Verification

I have yet to have the IP address removed from the firewall rules, but I am confident that it will work as designed/defined. A summary will follow.

History

Edit 1 Initial Post
Edit 2 Cleaned up answer, added History section

Related Topic