Reporting Services – Remote Server Returned an Error: (401) Unauthorized
Posted by denglishbi on July 18, 2010
This past week I was working on a new project and the client was leveraging the new Microsoft BI Stack working with PowerPivot, SharePoint 2010, PerformancePoint Services 2010, and Reporting Services 2008 R2 running in Integrated Mode. Everything was working great with PowerPivot, loading files into the PowerPivot Gallery, and using PerformancePoint Services with PowerPivot as the data sources. The odd thing was that Report Builder was not working against PowerPivot, well it was kind of. Let me try and explain and walk through the steps to reproduce the issue:
- We created a Web Application in SharePoint 2010 – http://<ServerName>:51000 and then deployed the PowerPivot web application (powerpivotwebapp.wsp) to it – in order to deploy the application you need to go into Central Administration->System Settings->Manage Farm Solutions->powerpivotwebapp.wsp
- Now we will create a new site collection within this web application and we will use the Enterprise Business Intelligence Center template (one thing to note is that the URL for this site collection is being set to http://<ServerName>:51000/sites/msbi
- Now we will access the new site collection and add a new PowerPivot Gallery library to the site (go into Site Actions->More Options to add the library)
- Now we will upload a PowerPivot file to the PowerPivot Gallery library to utilize as a source (nothing fancy with the data, just some store sales data broken out by year and month with a PivotChart in the file)
- Now we will launch Report Builder 3.0 from the PowerPivot Gallery to create a report off this StoreSales.xlsx file. Once Report Builder is running we will check the properties of the data source that is generated and test the connection
- Everything looks good right? Before we try to create a dataset let’s try clicking the Build… button and testing the source connection one more time
- Oh no…that can’t be good. Well now let’s back out of the and try to create a dataset
- Hmmm…same error message ‘The remote server returned an error: (401) Unauthorized.’ How can this be? We are site collection administrators, owners of the site, created the PowerPivot file and uploaded it, and we can even create a PerformancePoint data source and PerformancePoint content without any issues (this portion is not show here, but all of the PerformancePoint functionality does work – I should mention that this is a Single Server development environment).
- So now what. I was able to use Report Builder to create SQL Server and SharePoint List reports – ran into issues when trying to save them to the site though. In order to get them to the site I had to save the RDL files locally and then upload them to SharePoint. They did work once they were uploaded, so now what is the issue? How about we check out the Report Server to see what is going on.
- That seems to be working just fine. Now let’s try clicking on the site collection URL that we created
- Well that can’t be good. ‘An internal error occurred on the report server. See the error log for more details. (rsInternalError) The site http://<ServerName>:51000/ could not be found in the Web application SPWebApplication Name=SharePoint – 51000.’ Now according to the message it is looking for a site that would be at the Root of the Web Application that we originally created. Remember that the site collection was originally created at http://<ServerName>:51000/sites/msbi. Okay, so let’s try creating another site collection.
- Now let’s try the Report Server again. Well what do you know, now we can explore the original site collection.
- Now let’s try creating the dataset in that Report Builder report again against that PowerPivot file
- Well what do you know, it works now:) Game on!
So the lesson of the day is to make sure that you create a site collection at the Root of the web application in SharePoint, otherwise Reporting Services will definitely have some issues. Typically I would always do my development this way and then create sub sites within the site collection itself. In this case though the method that was originally chosen was to create the site collection at what I would consider a sub site level. This caused some extremely odd behavior within Reporting Services with connectivity and creating reports. The solution was to go back into Central Administration in SharePoint and create a Root level site collection for the web application.
Just kind of weird though when originally it all pointed to what I would have thought to have been a permission issue. Then after further testing I thought maybe a PowerPivot issue, then SharePoint Integration issue, and it turned out to be a site collection issue. Weird. Is this a bug? Should it be added to the Connect site?