Configuring Kerberos so SSRS 2012 reports can consume SharePoint 2010 as a data source

  • Article

I recently had the need to configure Kerberos constrained delegation for SQL Server Reporting Services 2012 (SSRS) that was running in SharePoint Integrated Mode in a SharePoint 2010 farm.  SSRS reports needed to leverage the “XML” data source and consume the SharePoint web services (such as “listdata.svc”).  The documentation for configuring this scenario is scattered (most Kerberos examples show delegation to a backend database server or SSAS), so hopefully this post will save some time and effort tracking down the pieces.

ASSUMPTIONS

  • SSRS and the Claims to Windows Token Services (C2WTS) are each configured as domain accounts (separate accounts, if following the principle of least privilege)
  • SharePoint address used as the “XML” data source by the SSRS report resolves to a WFE server in the farm
  • SSRS is configured to run on an application server in the SharePoint farm
  1. Create “dummy” service principal names (SPNs) for the SSRS service account and the C2WTS service account (this enables the “Delegation” tab on those accounts).  NOTE: Duplication of SPNs is not allowed, so each “dummy” SPN must be unique.
    • SetSPN –S SP/<unique SPN> <domain>\<SSRS account> (Example: SetSPN –S SP/SSRS.dummy mydomain\svc_ssrs)
    • SetSPN –S SP/<unique SPN> <domain>\<C2WTS account> (Example: SetSPN –S SP/Claims.dummy mydomain\svc_spclaims)
  2. Configure constrained delegation (Step #3 in section “” in this KB) on the C2WTS and SSRS accounts, so that they can delegate to your web application’s web app pool account
  3. Add a dependency on the C2WTS service (dependent on Cryptography service – section “Add Startup dependencies the WIF C2WTS service” in this KB)
  4. Add the C2WTS account to the local administrator’s group on your SharePoint servers
  5. Grant the following rights to the C2WTS account (via local security policy).  NOTE: These are required even though the account has been added as a local administrator.
    • Act as part of the operating system
    • Log on as a service
    • Impersonate a client after authentication
  6. Restart the C2WTS service on each server