Report Builder and Firewalls

We have had a few customer calls come in on this scenario that I thought this needed to be documented a bit.

Scenario:

In this scenario, the customer has a data source defined on the Report Server.  Some were using Named Instances, others were using a Default Instance for the Data Source.

There are some aspects of Report Builder that will run server side (from the context of the Report Server).  For example, DataSource retrieval and preview of a report.  This is assuming that we are in connected mode in Report Builder.

There are other aspects that will run Client Side.  Some examples of that are the Query Designer and general Metadata lookup for the DataSet.  This is where the problems come into play when a firewall is involved.

In all of the cases, reports and Report Builder function normally locally.  When they try to create a new report through Report Builder, they encounter errors similar to the following:

 

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

 

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - The requested name is valid, but no data of the requested type was found.)

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

The first error is specific to a Named Instance server. The other two are when we are trying to connect directly to the SQL Server.  Named Instances have to do a lookup to get the port number for the actual instance we are connecting to.  This lookup is fielded by SQL Browser over UDP port 1434.  When ever you see “error: 26 - Error Locating Server/Instance Specified”, it is SQL Browser related.  The underlying issue is still the same as the other messages.

The way I reproduced the issue was by doing the following on my lab setup which was configured for Basic Authentication:

1. Open Report Builder (which starts with a blank report – and I was in connected mode with my Report Server)

2. Create a DataSource which I select from the existing data sources on my Report Server

3. Create a DataSet

4. At this point, the DataSet Properties window should open up, at which point you can click on “Query Designer…”

   

5. I was then prompted for Credentials and then was met with the following:

   

The Problem:

The overall problem is that Report Builder cannot see the SQL Server when external to the network that SQL Server resides on.  SQL Server is typically not exposed through the firewall.  Assume the following configuration:

Report Server:

• Internet RS URL: https://www.mysite.com/ReportServer
• Public IP:  201.201.201.201
• Private IP: 10.0.0.5
• DataSource Connection String:  server=MyServer\MyInstance;Database=AdventureWorks;

SQL Server:

• Server Name: MyServer
• Instance Name: MyInstance (Port 2644)
• Private IP:  10.0.0.4

When Report Builder is opened from a client machine on the Internet (or external to the private network that SQL Server is a part of), when it goes to hit the datasource, it is actually trying to connect to MyServer\MyInstance.  Because this is a named instance, we are doing the SQL Browser lookup first.  In this case, it will be a NetBIOS lookup.  If we are doing a straight TCP connection, we will end up doing a DNS lookup.  Because we are on the Internet, there is no WINS or DNS server that is aware of MyServer.  NetBIOS or DNS will come back basically saying it couldn’t find the server name you are requesting which results in one of the errors I outlined above.

Report Builder doesn’t go through the Reporting Services WebService to do DataSource calls which would make it server based.  From the design perspective, we are client side and it will try to establish that data from the client.  I think some of the confusion is that people thing that we are in “connected” mode with the Report Server, so all functionally would occur on the Report Server itself, in which case we would expect the Report Server to be able to communicate with the SQL Server successfully.  This, unfortunately, is not the case.

Are there any workarounds?

The next logical question would be, how do I get this to work?  There are two possible workaround I can think of.  One that is not very realistic and another that is possible, but also somewhat of a pain.

Workaround 1:

This involves exposing your SQL Server to the internet, which I do NOT recommend and I doubt most companies are willing to do.  At that point, you could have an External DataSource along with an Internal DataSource.  People using Report Builder on the internet could reference the External DataSource which has the connection information for the SQL Server that would be usable from the internet.  At that point the design aspects would work, but Preview could fail depending on your network configuration if the Report Server can reference the external IP address for SQL Server from the internal side.

Then when you publish, the report can reference the Internal DataSource.

Workaround 2:

Another option is to expose your data through a WebService that is accessible via the Internet.  Then Report Builder uses can access the DataSource that is using the WebService as that resource is available to them externally.

Update - Workaround 3 (SSAS/OLAP) – Thanks David!:

For SSAS/OLAP you can setup a Connection Proxy over HTTP.  This would be usable both internally and externally and can be easily exposed through a firewall.  Be sure to use a non-standard port that is configured on your Firewall for security purposes.  Also, be aware that you are exposing your backend to the internet and to take the appropriate security measures.  SQL has a similar feature through the use of an HTTP Endpoint, but be aware that that has been deprecated and is not guaranteed to be available in a future release.

 

Overall, it will be difficult for people using Report Builder externally to access resources that are on an internal network when designing a report.  Hopefully, this will allow you to better plan your deployment of Reporting Services.

 

Adam W. Saxton | Microsoft SQL Server Escalation Services

Comments

• Anonymous
  December 04, 2009
  There is another option if you are using OLAP. And works for either internal or external.Setup a connection proxy with OLAP over HTTP, use nonstandard port, firewall rules in place on the proxy.RB -> DS -> Proxy -> SSASProfit.

• Anonymous
  December 05, 2009
  As an ISV providing SaaS services, we've been struggling with these sorts of issues for a long time. Our clients sit behind these firewalls and want to build their own reports - a true 'connected' mode would really help here.  I was hoping that it might be included in R2 / Report Builder 3, since it mentions 'connected mode' but I can't find clear documentation and haven't been able to test it for myself.By the way, we'd also like to use Custom / Forms / Basic authentication since we're building internet-facing solutions, and this has been hard enough implement with SSRS, but seems impossible with AS / OLAP even with the HTTP proxy - we can't get Excel's OLAP driver to send Basic credentials after the first request so it connects OK and then subsequent requests (enumerating DBs) fail.  Anyone else had any success with this?

• Anonymous
  February 26, 2010
  Why can't you just put an entry into the Hosts file on the report server machine ?

• Anonymous
  October 27, 2010
  This is obviously quite a limitation, especially given that Business Objects resolves its connections on the server and so does not suffer this problem. Has this been addressed in Report Builder 3?John

• Anonymous
  January 26, 2011
  Hi,Could you please provide us more information about the second workaround ?How could I "expose my data through a webservice" and use it within Report Builder ?Thanks in advance

• Anonymous
  November 01, 2011
  Thanks for the post. I have followed this guide bloggingabout.net/.../configuring-http-access-to-sql-server-2008-analysis-services-on-microsoft-windows-server-2008.aspx to do workaround 3.Can anyone post a guide for workaround 2, as I have problems with accessing my SQL DB through Report Builder as well.Peter

• Anonymous
  September 09, 2012
  We have done this by using a data model on the SSRS server.  The database is behind further firewalls and (as you note and we we'd all expect) is not accessible from the client machine at all.  The reporting server can see the database.  The client can see the reporting server.  The reporting server has a model that represents the database and against which the client users can create reports.  The reporting server runs these reports, getting the data from the database on their behalf.This means that reports can only use the semantic queries rather than SQL, but has the advantages that the reports are written against an abstracted model, insulated from changes to the database structure (as long as we update the model consistently, old reports will still work) and from the complexity (since we base the model entities on views we can pre-include appropriate joins, aggregations, etc.).  It is used by our end users with minimal training (i.e. no real query training necessary), so works pretty well.The main downside is that the model is a bit awkward to create and maintain.

• Anonymous
  January 04, 2014
  The comment has been removed

• Anonymous
  March 31, 2014
  I came up with a different, but cumbersome workaround:create a non-shared datasource in report builder using the external IP address. This will allow you to create your dataset(s)carry out initial design but run will fail so.. Save (As) the report to the Report Server Close the report in Report Builder On the Report Server change the datasource for the report to use a shared datasource that is using an internal IP address (via manage and datasources) Open the report in Report Builder again and continue report design and you will be able to run the report If you need to make any changes to the dataset(s) you will need to change the datasource back to be a non-shared datasource using the external IP address.  So you are back at step 1 and you will need to proceed through the steps above again! Not very end-user friendly.

• Anonymous
  June 20, 2015
  Hi Keith, I'm also facing similar problem with report builder and i like your idea of creating reports on user data models. However, I always receive errors while opening query builder using data model. =================================== Object reference not set to an instance of an object. (Microsoft SQL Server Report Builder)

---

Program Location:   at Microsoft.ReportingServices.SemanticQueryDesign.ExpressionUtil.SetContextEntityProperty(Expression expr, ModelEntity contextEntity)   at Microsoft.ReportingServices.SemanticQueryDesign.Filter.UpdateExpressions()   at Microsoft.ReportingServices.SemanticQueryDesign.DesignQuery.UpdateExpressions()   at Microsoft.ReportingServices.ReportBuilder.ReportModel.Report.UpdateQuery(RdlUtility rdlUtil)   at Microsoft.ReportingServices.ReportBuilder.ReportDesignUtil.QueryChanged()   at Microsoft.ReportingServices.ReportBuilder.ReportDesignUtil.SetReport(Report report, RSDPModelHost modelHost, IServiceProvider serviceProvider)   at Microsoft.ReportingServices.QueryDesigners.SMQLDesignerControl.Initialize(String query)   at Microsoft.ReportingServices.QueryDesigners.SMQLQueryDesigner.InitializeQueryDesigner()   at Microsoft.ReportingServices.QueryDesigners.InternalQueryDesignerWrapper.InitializeDesigner()   at Microsoft.ReportingServices.QueryDesigners.InternalQueryDesignerWithGenericModeWrapper.Initialize()

report builder is connected to the report server and i am able to test connection successfully on the data source. Can you or anyone else please give me some ideas on what could be wrong here?? Thanks.