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

Solution 1:

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

I define a firewall rule from 168.xxx.xxx.71 to 168.xxx.xxx.51:1433
The http.sys component of the SQL/SSRS instance complies with RFC 3484 and selects the source IP according to the defined rules
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

I am in no way interfering with the implementation of RFC 3484
I am in no way juggling with routes or ARP configurations
I am in compliance with RFC 3484 and Microsoft's implementation
I am not hacking any registry settings or system configurations
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

Solution 2:

SSRS supports several standard data sources as well as other .NET data sources:

https://msdn.microsoft.com/en-ca/library/ms159219.aspx

Assuming you are using the SQL native client for the data source, you have no option to specify a source IP address:

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring(v=vs.110).aspx

Therefore it stands to reason that the client will use IPADDR_ANY during the Bind() method when setting up the network connection. This leaves Windows to make the decision.

Windows 2008 and up address selection is based on the highest number of matching bits with the next hop which means the answer depends on your default gateway (or whatever specific routes you may have defined).

https://blogs.technet.microsoft.com/networking/2009/04/24/source-ip-address-selection-on-a-multi-homed-windows-computer/

I did not see any mention of routes or gateways in your diagram so that's as far as I could get.

Good luck!