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:
Open Report Builder (which starts with a blank report – and I was in connected mode with my Report Server)
Create a DataSource which I select from the existing data sources on my Report Server
Create a DataSet
At this point, the DataSet Properties window should open up, at which point you can click on “Query Designer…”
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