5

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.

enter image description here

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.

Improve this question
1
  • What are your IIS settings for SSRS? Whenever I've had anonymous set for anything I get some odd behavior, so I've always preferred to not use anonymous...
    – Jason B.
    Commented Jul 13, 2016 at 16:54

3 Answers 3

Reset to default
1

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.

Improve this answer
2
  • You're correct. Report builder requires a direct connection to SQL Server. We just ran into this trying to give our SaaS customers the ability to build their own reports. Like you, we'd assumed that ReportBuilder only needed a connection to SSRS (which is tightly secured and only talks HTTPS). There's no way in hell that we would expose SQL Server over the internet, so this makes ReportBuilder useless for cloud service providers like us.
    – Simon Brangwin
    Commented May 6, 2019 at 4:44
  • @SimonBrangwin what we did to get around this issue, we created a windows 10 VM in a DMZ, gave end users access to that VM and allowed traffic between the VM and the SQL Server, thus gave end users ability to use Report builder without exposing our SQL Server to the internet.
    – M.Ali
    Commented May 9, 2019 at 9:21
0

I had this issue after migrating a repository to an alternative server. The way worked-around it was going in to the individual connection configuration for each report and data source and entering a dedicated service (local admin) account and saving the credentials.

This way, the local admin is always accessing the items rather than the users however it was a messy and time-consuming workaround.

Improve this answer
1
  • Maybe powershell might help with mass modifications of reports to repoint their data sources. Check out the Get- and Set- ItemDataSources
    – codedawg82
    Commented Jan 7, 2018 at 18:25
0

I had this problem only since yesterday and solved this by the following steps

1 - Go onto the Reporting Server where the problem is.

    • Go into Reporting Services Configuration Manager
    • Connect and go to Web Service URL, and at the bottom of the Web Service URL, COPY the URL from Report Server Web Service URLs, where the url is stated including the port number
    • On the same server, launch Report Builder.
    • Go to File Menu, and at the bottom, click Options
    • On the first line of 'View or Modify Report Builder settings, PASTE the URL into the first line of the dialog box called 'USE this report server or Sharepoint site by default:'

7 - Click OK and Close Report Builder

8 - Try and open your reports.

It worked for me, hope it worked for you.

Improve this answer
1
  • This doesn't address the issue but may help others.
    – Simon Brangwin
    Commented May 6, 2019 at 4:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.