Connection Failed Error Using SQL Reporting Services with Kerberos Based Claims

  • Article

When you try to connect to a SharePoint 2010 site using Report Builder, you may receive the following error :

Connection failed.
Unable to connect to the server that is specified in the URL, ‘https://teams.jormanvmas.com/’.
Make sure that the server is running, the URL is correct, and you have permission to use the server.

image

I was on my lab environment, so I asked myself if I have permission to use the server.  I told myself I can use the server, but it still didn’t work :-)  I started digging into things, and I have the following configuration:

  • Web application is configured for Kerberos based claims. 

image

  • SQL Server Reporting services is configured for Windows Authentication in Central Admin. 

image

When you have SharePoint 2010 and Reporting Services setup this way, reporting services should fall back to using Trusted Account for the Authentication Mode.  In this mode, Reporting Services will connect using the application pool account configured for the SharePoint web application.  There was a bug in the initial release of the Reporting Services add-in, that installs with the SharePoint 2010 Prerequisite Installer, in which Reporting Services tries to delegate your claims credentials to Reporting Services.  You can tell if you are hitting this by checking the Reporting Services log.  When you get the above error in Report Builder, you will see the following entry in the Report Server log:

library!ReportServer_0-1!1784!07/26/2011-19:02:01:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: , Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The permissions granted to user 'i:0#.w|jormanvmas\jerry' are insufficient for performing this operation.;

The interesting piece is that the user in the log entry is a claims identity, when it should be the application pool account for the web application. If you really dig into the issue and attach Windbg.exe to the ReportingServicesService.exe process, the stack when the exception is thrown looks like the following.  The function at the top of the stack is actually comparing the account name that was received against the application pool accounts configured on the web applications in SharePoint.  If the accounts don’t match, you get the above error logged.

0:017> !clrstack
Microsoft.ReportingServices.SharePoint.Server.SharePointTrustedUserVerification.CheckProcessAccount(Microsoft.ReportingServices.SharePoint.Administration.ObjectModel.RSSPWebApplicationCollection, System.String)
Microsoft.ReportingServices.SharePoint.Server.SharePointTrustedUserVerification.VerifyTrustedUserAccessToWssContent(System.String, System.String, System.String)
Microsoft.ReportingServices.Library.TrustedAccountVerification.VerifyTrustedAccountAccess(System.String, System.String)
Microsoft.ReportingServices.WebServer.WebServiceHelper.ExtractWebUserInfo(Microsoft.ReportingServices.WebServer.TrustedUserHeader, System.String)
Microsoft.ReportingServices.WebServer.RsSoapBase.Init(System.String, Boolean)
Microsoft.ReportingServices.WebServer.ReportingService2010.Initialize(System.String, Boolean)
Microsoft.ReportingServices.WebServer.ReportingService2010.Initialize(System.String)
Microsoft.ReportingServices.WebServer.ReportingService2010.ListItemTypes()
System.Reflection.RuntimeMethodInfo.Invoke(System.Object, System.Reflection.BindingFlags, System.Reflection.Binder, System.Object[], System.Globalization.CultureInfo, Boolean)
System.Reflection.RuntimeMethodInfo.Invoke(System.Object, System.Reflection.BindingFlags, System.Reflection.Binder, System.Object[], System.Globalization.CultureInfo)

If you’re hitting this issue, and need to use Kerberos for authentication, you will want to install the SP1 update for the reporting services add-in on all your SharePoint 2010 boxes [you want the rsSharePoint.exe download].  This has a fix that will let Kerberos based claims fallback to Trusted Account when connecting to Reporting Services.  If you need a quick workaround, you can switch the authentication mode on the web application to NTLM.

For information on how Reporting Services works with claims authentication, see Claims Authentication and Reporting Services.