Recommend and I doubt most companies are willing to do. This involves exposing your SQL Server to the internet, which I do One that is not very realistic and another that is possible, but also somewhat of a pain. The next logical question would be, how do I get this to work? There are two possible workaround I can think of. 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. From the design perspective, we are client side and it will try to establish that data from the client. Report Builder doesn’t go through the Reporting Services WebService to do DataSource calls which would make it server based. 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. Because we are on the Internet, there is no WINS or DNS server that is aware of MyServer. If we are doing a straight TCP connection, we will end up doing a DNS lookup. In this case, it will be a NetBIOS lookup. Because this is a named instance, we are doing the SQL Browser lookup first. 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. Assume the following configuration:ĭataSource Connection String: server=MyServer\MyInstance Database=AdventureWorks SQL Server is typically not exposed through the firewall. The overall problem is that Report Builder cannot see the SQL Server when external to the network that SQL Server resides on. I was then prompted for Credentials and then was met with the following: Open Report Builder (which starts with a blank report – and I was in connected mode with my Report Server)Ĭreate a DataSource which I select from the existing data sources on my Report ServerĪt this point, the DataSet Properties window should open up, at which point you can click on “Query Designer…” The way I reproduced the issue was by doing the following on my lab setup which was configured for Basic Authentication: The underlying issue is still the same as the other messages. When ever you see “error: 26 - Error Locating Server/Instance Specified”, it is SQL Browser related. This lookup is fielded by SQL Browser over UDP port 1434.
Named Instances have to do a lookup to get the port number for the actual instance we are connecting to. The other two are when we are trying to connect directly to the SQL Server. The first error is specific to a Named Instance server. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) Verify that the instance name is correct and that SQL Server is configured to allow remote connections. The server was not found or was not accessible. (provider: TCP Provider, error: 0 - The requested name is valid, but no data of the requested type was found.)Ī network-related or instance-specific error occurred while establishing a connection to SQL Server. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)Ī network-related or instance-specific error occurred while establishing a connection to SQL Server. When they try to create a new report through Report Builder, they encounter errors similar to the following:Ī network-related or instance-specific error occurred while establishing a connection to SQL Server. In all of the cases, reports and Report Builder function normally locally. This is where the problems come into play when a firewall is involved. Some examples of that are the Query Designer and general Metadata lookup for the DataSet. There are other aspects that will run Client Side. This is assuming that we are in connected mode in Report Builder. For example, DataSource retrieval and preview of a report. There are some aspects of Report Builder that will run server side (from the context of the Report Server). Some were using Named Instances, others were using a Default Instance for the Data Source. In this scenario, the customer has a data source defined on the Report Server. We have had a few customer calls come in on this scenario that I thought this needed to be documented a bit.
0 kommentar(er)
