Connecting to Data Sources outside a Firewall with SQL Server 2005 Reporting Services
In my last post I discussed how to configure database security on two SQL Server 2005 instances outside a firewall to enable operational reporting. In this post I’ll examine the security configuration of the SQL Server 2005 Reporting Services (SSRS) instance inside a firewall to be used for operational reporting.
To quickly review the scenario, I have a production SQL Server 2005 database for the Airframe application that is hosted by a third party outside the firewall and used by customers and partners. Using log shipping, I created a hot standby of the production database on a second instance of SQL Server 2005, also hosted outside the firewall. The hot standby is refreshed hourly, and is to be used for both disaster recovery and as a data source for operational reports.
The operational reports are intended for internal employees who need better access to Airframe data to do their jobs, not for customers and partners who are users of the Airframe system itself. One approach would be to add the operational reports directly to the Airframe application. I decided against this since the application is already in production and the resources required to add operational reporting functionality to the core application were cost prohibitive. Also, operational reporting is not a core requirement for the customers and partners who use Airframe, but are intended for internal employees who may not even have access to the Airframe application who need better access to the data.
I decided on a much simpler approach, which was to develop an incremental operational reporting capability using SSRS that is separate from the Airframe production application. The data for the operational reports would need to come from the hot standby system outside the firewall to avoid reporting overhead on the production environment. Users of the operational reports, all of which are internal employees, will want fast access to them using internal SharePoint sites, so the SSRS instance used will need to be hosted inside the firewall and secured using the internal user’s Active Directory credentials via Windows authentication.
Enough background, let’s get into some implementation. I think I mentioned that we need these reports yesterday, so I wanted to build some simple reports and get them deployed quickly to test connectivity and performance. After locating a server to host my SSRS instance for operational reporting, I did a quick default installation of SSRS to test my connection to the data source outside the firewall.
Next, I created a new SSRS report server project on my workstation in Visual Studio 2005 and added a shared data source to the project for my hot standby server. I used the FQDN of the hot standby server as the server name, and set the credentials to use the reporting account I blogged about in my previous post which uses SQL Server Authentication.
At first I was unable to connect since the server is outside the firewall, so I installed the ISA Server 2004 firewall client on my workstation and I was able to establish a connection from within the development environment. I developed a quick report that displays all of the children users for a parent user, and deployed it to my SSRS operational reporting instance, and tried to view the report from the SSRS report manager web site.
At this point I ran into a snag. The Report Server
My first feeble attempt was to install the ISA Firewall Client on my operational reporting server, create a data source and a simple report, and try to run the report. I kept getting the following error from Reporting Services when attempting to view the report:
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'Airframe Hot Standby'. ---> System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - A socket operation was attempted to an unreachable host.)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.ReportingServices.DataExtensions.SqlConnectionWrapper.Open()
at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.ReportRuntimeDataSourceNode.OpenConnection(DataSource dataSourceObj, ReportProcessingContext pc)
From the error I was able to determine that SqlClient (the .NET data provider SSRS uses to connect to SQL Server) was reach the host for the data source which is outside the firewall. To help diagnose the problem I wrote a quick C# console app that tests the connection to the hot standby database outside the firewall using System.Data.SqlClient and copied it to the server that hosted the SSRS operational reporting instance. At first the connection test failed, but after installing the firewall client the test succeeded.
SSRS however was still unable to connect even with the firewall client installed. As I dug a little deeper, I found that some firewalls disable access to built-in accounts like NETWORK SERVICE. SSRS, which is basically an ASP.NET 2.0 application, runs in an application pool whose identity is set to NETWORK SERVICE by default. There’s a great Patterns and Practices article on MSDN that discusses this in detail here. So my next step was to reconfigure SSRS to use a different application pool associated with a real domain service account.
The application pool settings for SSRS can be configured using the Reporting Services Configuration tool. Just connect to the server, then click on the Web Service Identity section . You can create a new application pool for both the Report Server and Report Manager components, and associate them with a different identity. In my case I used the same service account we use for our SQL Server 2005 services.
After I applied these changes and restarted everything, Reporting Services was able to connect to my hot standby server outside the firewall via the firewall client. In my next post, I’ll focus on how I built my first set of operational reports.