CSS SQL Server Engineers

This is the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support. Posts are provided by the CSS SQL Escalation Services

SharePoint Adventures : Why isn't Claims working with SSRS?

SharePoint Adventures : Why isn't Claims working with SSRS?

  • Comments 4

SharePoint 2010 introduced a new authentication architecture called Claims Based Authentication

SharePoint Claims-Based Identity
http://msdn.microsoft.com/en-us/library/ee535242.aspx

When using Claims Based Authentication for your SharePoint site, in order for authentication to flow between services, those services needs to be claims aware. That means that they understand what to do with the Claims Token as it is not an Windows/NT Token that we normally think of. Applications that are Claims aware can transition a Claims token to a Windows Token by way of the Claims to Windows Token Services (C2WTS). But this would be an explicit call from that application. Excel Calculation Services within SharePoint is a good example of a Claims aware service that makes use of C2WTS to transition from the Claims token to the Windows Token when you go to refresh an Excel Workbook from within SharePoint.

Reporting Services 2008 R2, in contrast, is a good example of a Service that is not Claims aware and does not recognize the token. So, what does RS do? The following Books Online Documentation talks about it briefly.

Claims Authentication and Reporting Services
http://msdn.microsoft.com/en-us/library/ff487970.aspx

RS has two authentication modes within the Add-In for SharePoint

clip_image001

"Windows Authentication" will do what we expect. It will pass the Windows Token and can make use of Kerberos, if Kerberos is configured. See my previous Blog post about using Kerberos with the Report Server. When using "Trusted Account", we do not pass the Windows Token back to the Report Server. Instead, the RS Proxy will attach the SPUser Token from SharePoint into the header of the SOAP request.

The Report Server will take the SPUser token and use that as the user's credential. We can validate the user using the SharePoint API's to make sure they have access to things such as the RDL (Report). One thing you will notice though is that the actual User ID looks a little difference from a Report Perspective.

Classic Windows Site w/ Windows Authentication:

clip_image002

This shows up like we would expect with the Domain\User format.

Windows Claim site w/ Trusted Authentication:

clip_image003

This looks a little different. This is what it will show when we are in a claims site. Specifically this is the format when using Windows Claims. The "w" before the pipe indicates that we are using Windows Claims. I will show in a later post what that looks like for a different provider within SharePoint. For now, I'm just going to stick with Windows Authentication. This is what we extract from the SPUser token because there is no Windows Token. We still the security context of Domain\User, but we have a prefix on it and it is separated by a pipe "|".

I mentioned before that RS will use "Trusted Account" even when you choose "Windows Authentication" from the Integration settings. To prove that, lets run the same report again with "Windows Authentication" selected to see what the User ID looks like.

clip_image004

clip_image005

You can see that we still don't see the Windows representation, but we see the Claims representation. A nice trick though is that you can leave the setting for "Windows Authentication" and have a Classic and Claims site side by side, and they will both work. You can have Kerberos working with the Classic site, while using the SPUser token with the Claims site.

From a Data perspective this looks a little different. If we have a Data source on a Claims site and that data source is setup to use Windows authentication, it will not work.

clip_image006

We will get the following error:

An error has occurred during report processing. (rsProcessingAborted)
  Cannot impersonate user for data source 'DataSource1'. (rsErrorImpersonatingUser)
    This data source is configured to use Windows integrated security. Windows integrated security is either disabled for
    this report server or your report server is using Trusted Account mode. (rsWindowsIntegratedSecurityDisabled)

You'll notice that the exception is "rsWindowsIntegratedSecurityDisabled".

When using a Claims Site with Reporting Services, you have to store the credentials within the Data Source. There is no way to pass the Windows Credential to the backend data source. This is because RS doesn't have the Windows Token. We only have the SPUser token, which is not even the Claims/SAML Token. RS doesn't know about the Claims aspect and also doesn't know about the Claims to Windows Token Service (C2WTS).

So, you can still use reports and they will render under a Claims site, but the restriction is that you can't use Windows Authentication back to the data source. As a result also, this makes configuration a little simpler with Reporting Services as you don't really need Kerberos in this situation. You can avoid that headache if all you are going to have is a Claims site with Reporting Services Integrated.

 

UPDATE – 8/21/2012:

I’ve been pinged on this twice today with the same question related to this post, so I thought I would add an update.  This issue is specific to SQL 2008 R2 and earlier versions.  SQL 2012 will go in as a Shared Service within the SharePoint farm and also is Claims Aware.  Also, as a SharePoint Shared Service, you are actually forced to Claims even if your WFE is setup as Classic Windows Auth mode. 

You can read a little more about this, for SQL 2012, in the following MSDN document.

Reporting Services - Claims to Windows Token Service (C2WTS)
http://technet.microsoft.com/en-us/library/hh231678

 

Adam W. Saxton | Microsoft SQL Server Escalation Services
http://twitter.com/awsaxton

Leave a Comment
  • Please add 4 and 2 and type the answer here:
  • Post
  • Is there an approach to resolve the issue getting with Reportbuilder not working with claims based sites?   that's a pretty big showstopper now.   Is there a different approach for next release?

  • So, storing the credentials in the data source will fix is that only work around?

  • I might be missing something here, but what about Claims Based for Dev/Testing from a Visual Studio Development client?

    I have a SharePoint List on a  Web app under Claims based.  I'm trying to figure out how to connnect to that SP list from my SSRS 2012 Dev Client by passing User/PW but get a 403 Denied.  I am able to connect to other lists on a web app (on the same farm) set to NTLM with no problem.

    Thanks.

  • Great information here, thank you for helping me find a solution to a tricky problem :)

Page 1 of 1 (4 items)