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
Security Considerations for a SQL Server Installation
Base article.Configure the Windows Firewall to Allow SQL Server Access
This article points to all other articles regarding firewall configuration for SQL Server.Configure a Windows Firewall for Database Engine Access
No word of IP addresses used.Configure a Firewall for Report Server Access
This article was pretty interesting as it noted:
...but still not a word about IP configuration/settings/defaults.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.
Source IP address selection on a Multi-Homed Windows Computer
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:
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
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
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:
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.
Target IP Addresses and Binary Notation
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:
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:
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 ...
...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
Benefits
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
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!