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:

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

I (and my manager) would like to be able to create custom reports in SCCM 2012 SP1 through the Configuration Manager Administrative Console from the comfort of our workstations. Unfortunately, if you click the 'Create Report' button you get the following error:

The Report Builder click-once application does not exist on the report server 'SCCMReportingPoint.ad.contoso.com'. Ensure that the report builder application manifest exists on the server and try again.

If I attempt the same process on our SCCM server (SCCMReportingPoint.ad.contoso.com), the Configuration Manager Administrative Console correctly starts ReportBuilder and I get the Create Report Wizard.

I have done the following in an attempt to resolve this issue without success:

• Installed both ReportViewer 2012 SP1 and ReportBuilder 3.0 on my workstation
• Tried starting the Administrative Console as an administrator
• Changed the HKLM/SOFTWARE/Wow6432Node/Microsoft/ConfigMgr10/AdminUI/ReportingReportBuilderApplicationManifestName registry key from ReportBuilder_2_0_0_0.application to ReportBuilder_3_0_0_0.application as per this TechNet post.
• Looked through Reporting Services log (C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\LogFiles\ReportServerService__10_28_2013_00_00_33.log) on the SCCM server for errors. I did not find any.

I'm at a loss. I would really like to be able to setup custom reports without having to get our DBA involved (he is a busy man). Does anyone have any idea what I'm missing?

I have SSRS 2008 R2 installed on my server and need to deploy report from Business Intelligence Studio at another computer which is in the same domain and under domain user (non-administrator).

First I get the error "Unsufficient permissions". Then I started Report Manager at the server (http://localhost/Reports/) and under Site Settings / Security added my domain user to both System Administrator and System Users roles, restarted the service, but still got the same error.

Then I created a folder in Report Manager and added domain user to all available roles for that (Browser, Publisher, etc.). After that it doesn't show that error in BIS but instead shows the login dialog (though I already logged as domain user in Windows). I enter login and password but it just asking it again and doing nothing. Tried to enter administrator login details but it also just shown again.

Could you advice me how to enable access to the service or at least to some folder for non-administrator domain user?

I think I've looked through all the menus and control panel and I can't find anything to configure IIS.

I feel like IIS is already installed because SQL Server Reporting services has a webpage running and a virtual directory.

Whilst security vetting our machines, I found that one host was exposing a Microsoft-HTTPAPI/2.0 service over port 80 to the internet.

I'm not familiar with this, but after googling around, I found that SQL Server 2008 publishes SQL Server Reporting Services on port 80 by default and identifies itself as HTTPAPI/2.0. The host is also running IIS7.

I'm guessing this is probably not something that should be exposed to the world. Can anyone offer me any information or advice on the security risk of exposing this service?

Response Headers - http://#.#.#.#/
Content-Type: text/html; charset=us-ascii
Server: Microsoft-HTTPAPI/2.0
Date: Mon, 10 Aug 2009 10:44:25 GMT
Connection: close
Content-Length: 315

404 Not Found