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.
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 hosted on one Windows server (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 a different 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
When the Reporting Services server connects to the SQL Server instance to retrieve data, will it always build a connection with the underlying IP address of the Windows server (168.xxx.xxx.70 / preferred) 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 1
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.
I want to reduce by one firewall rule. (See screenshot further down)
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 hosting 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:
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)
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:
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:
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.