SSRS 2012 with SharePoint 2010

SSRS 2008 R2 SharePoint mode has some limitations such as it could not delegate user’s credential using Windows Kerberos authentication if the web application is using claims based authentication and Adam explained it why in his blog.

Some documents indicated that SSRS 2012 will be claim aware and work with C2WTS to convert Windows claims to Windows token. I deployed it in my lab environment and it did work with kerberos authentication in both classic and claims based authentication web application. Here are the of steps of installing and configure SSRS 2012 service application.

• Install SSRS 2012 (Reporting Services – SharePoint and Reporting Services Add-in for SharePoint Products) from SQL 2012 CD on SharePoint App server
• Create a SQL server
• Optionally you can configure the Kerberos authentication as you would for Excel services
  • make sure C2WTS works (if your Excel service works with Kerberos then C2WTS is configured correctly)
  • make sure SSRS service account KCD is configured correctly
  • make sure SSRS service account has access to SharePoint web application content database(s) 
• make sure the execution account has “allow logon locally” in local security policy

• Test SSRS 2012
  • Add 3 Content types to a document library
  • Create a reporting data source
  • Create a report using report builder

You might encounter the same issue if you test it on a claim based authentication web application as I went through the process and here is the scenario:

• Logon as LITWARE\Administrator
  • Created the claim based authentication web application
  • Create a team site collection, make LITWARE\administrator as site collection administrator
  • Added 3 SSRS content types to Shared Document library
  • Created a report data source and tested successful
  • Saved this report data source to document library
  • Open the just saved data source – I got the following error

Since Litware\administrator is a domain admin, farm admin, site collection admin, how could it possible it cannot open a file just created and saved?

When my peer Jim (support engineer) asked me: If you create a data source with a regular user instead of the Administrator do you see the same error?

At first I did not want to do it because my brain was occupied by who else has more power than my litware\administrator?! But as a courtesy and be able to reply his email, I went ahead to try the following steps:

1. Added litware\lit10 as site collection admin

2. Logon as lit10

3. Clicked on the data source created by administrator – I cannot believe myself, it opened without any problem !!!

4. Then I created a report builder report, and it worked beautifully

5. Then since I have Contoso and Litware forest trusted so I went ahead to give Contoso\administrator the site collection administrator access

6. Logon as Contoso\administrator, everything works fine

7. BUT logon as litware\administrator, it does not work!!!

OK, why then? What could trigger the code permission validation think Litware\administrator does not have permission?

By looking into the UserInfo database, you can see that we have two entry for litware\administrator, one is normal claim based and another is classic based, but for Contoso\administrator and lit10, we have only one entry which is claim based. To test my theory, I’d like to remove the non claim based litware\administrator from the database to try it again, but I do not want to do it in a unsupported way – remove it from db directly J

Here is how I clean that out:

1. Logon as lit10

2. Remove litware\administrator from home owners group

3. Remove litware\administraror from site collection admin

4. Remove it from All People list but SharePoint 2010 removed the link to allow us to see all people, so we just need to get the url pattern from 2007 and pasted to 2010

https://team9.litware.local/_layouts/people.aspx?MembershipGroupId=0&FilterField1=ContentType&FilterValue1=Person

we see 3 administrators … the 2^nd one is classic one and I removed it. Go to the database UserInfo table to verify it is marked as deleted.

Re-add litware\administrator to site collection admin group, logon as litware\administrator, woohoooo, worked!!!!!