Current Environment
I am in a bit of trouble here, I have a Reporting Services installed on one server and SQL Server database engine installed on another server.
The users are connecting to Reports Manager which obviously points to the server where Reporting Services are installed.
User use a generic login (a local admin account on the Report Server) to connect to the reports manager.
But when the users click on the Report Builder button on the Reports Manager, It initially failed to launch complaining `"Application cannot retrieve the files. Authentication failed."
After a lot of research I found out that, even though users entered the credentials of a Local account when connecting to the Reports Manager, but when they clicked on the Report Builder button, the credentials from their windows account were picked up hence the ClickOnce application failed to Authenticate the user and failed to Launch.
I changed this behavior by allowing Basic Authentication on Reporting Server and Allowing Anonymous Authentication on Reports Builder.
Now when the users click on the Report Builder instead of report builder just picking the credentials from the current user windows account it prompts for the credentials , the user passes the credentials of the Local Admin account on the reporting server and the ClickOnce Application is downloaded as expected.
Problem
Now the report builder is launched fine and I can right click the The datasources folder in the left pane of the designer, browse for the available data sources , test connection (shows tested successfully) . But as soon as I click on the DataSet and try to add a new dataset it throws an error saying "Authentication failed, cannot connect to the datasources."
Even though in the last step the connection test was successful but at this stage it is failing.
Important Note
The SQL Server has no external IP, it can only be seen from the reporting server. Could this be the reason that the connection is failing?
I mean does the report builder try to obtain a direct connection to the SQL Server when launched?
Or is there anything else that I have missed out?
Any pointer any suggestions are much appreciated , Thank you.
Best Answer
After a lot of digging around, I didn’t get anywhere, then eventually I got Microsoft involved to help me with the problem.
MS solution architect confirmed that the Report Builder requires a direct connection to SQL Server when launched hence the working with data sets in the report builder failed. Apparently this is BY DESIGN and it is not going to change.
This was in SQL Server 2008 R2 but the same is true for SQL Server 2012, 2014 and 2016. Not sure about 2017 but I doubt it very much if that has changed in 2017.
We ended up providing the client a machine on the network in DMZ to which end users connected remotely, only to work with the Report builder, it is a poor solution but it worked at that time and we needed a quick fix for it at that time.