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.
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.
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.