Getting a SharePoint List Data Source to work with Reporting Services Native Mode

A case came up where the user was trying to use Report Builder in a Reporting Services instance that was not integrated with SharePoint.  It was in Native Mode configuration.  They indicated that they were getting a 401 error.  My initial thought was that we were hitting a Kerberos issue.  Of note, they were trying to hit a List that was in SharePoint 2013. 

SharePoint 2013 is defaulted to use Claims Authentication Sites.  So, most would probably ignore the Kerberos aspects of the SharePoint site.  I was able to reproduce the issue locally because I had done the same thing.

I created the Data Source within Report Builder to hit my SharePoint 2013 site:  https://capthelo/, and when I click on “Test Connection” within the Data Source Dialog Window, I get the following error.

dataextension!ReportServer_0-1!9cc!06/11/2013-14:25:58:: e ERROR: Throwing Microsoft.ReportingServices.DataExtensions.SharePointList.SPDPException: , Microsoft.ReportingServices.DataExtensions.SharePointList.SPDPException: An error occurred when accessing the specified SharePoint list. The connection string might not be valid. Verify that the connection string is correct. ---> System.Net.WebException: The request failed with HTTP status 401: Unauthorized.

This happens because when you click “Test Connection” the connection test is actually performed on the Report Server itself not directly from Report Builder.  I had blogged a while back regarding Report Builder and Firewalls where I talk about how some of the items in Report Builder will try to connect direction, but “Test Connection” is not one of them.

At this point, we could ignore the error and hit OK on the Data Source Dialog and try and create a DataSet. When I go to the Query Designer, it appears to have worked.  This because the DataSets and Query Designer are coming from Report Builder itself.  It is a direct Web Request from the Report Builder Process and not the Report Server, so I don’t get an error.

However, this is misleading.  This may make you believe that it is working properly, but when you deploy and try to run the report, you will be back to the 401 error because we are now coming from the Report Server which will be down the same path that the original error with the “Test Connection” had.  From the DataSet/Query Designer perspective, this is a straight shot from Report Builder to SharePoint, so we can get away with an NTLM connection for the Web Request and the Windows Credential is valid. 

From the Report Server, however, this is called a Double Hop and to forward Windows Credentials you need Kerberos to do that.   Even when your SharePoint 2013 site is configured for Claims.  This actually has nothing to do with SharePoint, it has everything to do with Reporting Services.  The Report Server is the one trying to delegate the Windows Credential to whoever the receiving party is for the Web Request (or SQL Connection if that is your Data Source).  In this case, it is SharePoint 2013.  Because Kerberos isn’t configured properly, IIS (which is hosting SharePoint), received an anonymous credential for the Web Request and rejects it accordingly with a 401 error.

In my case, I was using a Domain User Account for the RS Service Account (BATTLESTAR\RSService – https://chieftyrol).  It had the proper HTTP SPN on it.  Also my SharePoint site was using a Domain User account for the AppPool identity within IIS (BATTLESTAR\spservice – https://capthelo) and this had the proper HTTP SPN on it.

So, now I just need to verify the Delegation properties for the RSService Account. Because I’m using the RSService account for other things that includes Claims within SharePoint 2013, I’m forced to Constrained Delegation on this account and need to continue using that.  If you are not bound to Constrained Delegation, you could choose the option “Trust this user for delegation to any service (Kerberos Only)” which is considered Full Trust and should correct the issue.  If you are using Constrained Delegation, you have to add the proper service that you want to delegate to.  In my case that is for my SharePoint site and is http/capthelo.battlestar.local.  After I added it, it looked like the following.

Then I restarted the Reporting Services Service and created the Data Source again.  At that point, the “Test Connection” returned Success!

Adam W. Saxton | Microsoft Escalation Services
https://twitter.com/awsaxton