SSRS report runs in Report Builder but user can't open and edit dataset

Question

I have a client where their SSRS reports all run as expected from within the Report Manager and from Visual Studio/Report Builder on any machine.

When a report is in design view on any machine other than the server however, the datasets cannot be edited. On trying to open the Query Designer to edit the SQL script, they are presented with this error:

Unable to connect to data source '[Data source name]'.

Timeout expired. The timeout period elapsed prior to completion of the operation or the the server is not responding.

And yet, I can close this error and click Test Connection on the Shared Data Source with success and run the report in Report Builder without issue. Credentials are explicitly entered, we are not using Windows Authentication.

This happens on every machine other than the server which has SSRS installed on regardless of who is logged in, including the account that has administrator privileges on the SSRS server.

What reasons could there be for the report able to connect and return data but, using the same credentials, not be able to connect to the data source to edit the dataset query?

Answer 1

Is the database hosted on the same physical server as SSRS? If not, it sounds like a double hop authentication/delegation issue.

https://blogs.msdn.microsoft.com/brushstrokes/2013/02/18/kerberos-authentication-in-reporting-services/

Basically, your authentication path looks like this: workstation - report server - report datasource.

If you are running it directly on the server, the authentication path looks like this: report server - report data source.

If the report server has not been authorized as a delegate for the data source, your credentials will not get passed.

When you test credentials, from the report server, it is checking as itself (no double-hop). From your workstation, you are connecting directly from the designer to the datasource (no double-hop).