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
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.
(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.
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.
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
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:
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.
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.
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.
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