Thursday, July 18, 2013

Configuring Security for SSRS in SharePoint Mode (Part II)

This blog is part of Series : Comprehensive SharePoint 2013 Development Environment Installation and Configuration


This blog article discusses how to configure security for SSRS in SharePoint mode install and configure SSRS Service Application in SharePoint Mode (Which is this Part II) assuming you have followed the previous blog on how to Install and Configure SSRS in SharePoint Mode.

There is more to it when it comes to actually being to deploy and run the SSRS reports under the SharePoint hood that is relating to Security. Since the default security provide for SharePoint 2013 Web Applications is Windows provider for Claims. When an SSRS Reports needs to run on the SharePoint page, we have various options to configure the underlying SSRS Data Source(s) and security. In either case the authentication from SharePoint Farm (SSRS Service within the SP Farm) to the underlying data source will need to happen. Since the account in question is maintained as Claim within the context of SharePoint Farm, while the SSRS data  source(s) within the Windows network Server recognize Windows account principle, we will need to convert our Claim account to Windows account and send it across the server (Double hop) ( I think you get the gist and a hint at Kerberos!) .

  • We will need to ensure Claims to Windows Token Service (C2WTS) is running on SharePoint servers.
  • Then we will need to configure Constrained Delegation on the  C2WTS account.
  • Switch Reporting Service application to Kerberos
  • Switch our Web Application where the SSRS reports will be deployed to Kerberos
  • Configure necessary SPNs

    My References:


    Create or ensure following Service Accounts (Described under this blog “Service Accounts Preparation ”)



    Register new Service accounts as Managed Service account within your SharePoint Farm



    Provision Service account for SSRS Service

    May be already done if followed the “Installing Reporting Services In SharePoint Mode (Part I) “.  From CA, Security>General Security>Configure service accounts



    Provision Service account for Claims to Windows Token Service on all SharePoint Servers


    C2WTS Service

    Validate on the Windows Services on all SharePoint servers under the Services Console that now the Lon On As account is changed from Local System to our domain account


    SSRS Security

    On the APP1 Server (Where SSRS Service is running) update Authentication from Windows to Negotiate (Kerberos). Access the Report Server Configuration file by following below path:






    Get the SSRS Service account Access to the Web Applications.

    Example for Portal Web App, from any SharePoint server, run SP PowerShell as administrator




    On the Server where PerformancePoint will run the check for C2WTS Windows Service account

    Ensure that C2WTS is in local admin


    Configure Local Security Policy

    Check Local Security Policy on the APP1 to ensure your CORP\SPC2WTS account is added:

    Local Policy>User Rights Assignment

    • Act as part of OS (SPC2WTSService)
    • Impersonate a client after authentication
    • Logon as service




    Restart Windows Service C2WTS


    Add the WSS_WPG Windows Group to the c2wtshost.exe.config file.  The file is located \Program Files\Windows Identity Foundation\v3.5\c2wtshost.exe.config

  • From your Local Groups

  • image



    IIS Reset

    Ensure SQL Server for Kerberos Delegation

    Register SPN for SQL Server Service Account and the SSRS Service account

    Setspn -S HTTP/portal corp\SPPortal
    Setspn -S HTTP/ corp\SPPortal
    Setspn -S MSSQLSvc/DC:1433 corp\sqlservice
    Setspn -S MSSQLSvc/ corp\sqlservice
    Setspn -S SP/C2WTS corp\SPC2WTS
    Setspn -S SP/SSRS corp\SPSSRS
    Setspn -S SP/PPS corp\SPPerfPointUser
    Setspn -S SP/Excel corp\SPExcelUser




    Run ADUC to, ensure Advanced Feature


    Check SQLService account Attribute for SPN



    Configure Delegation for SPC2WTS

    From the ADUC, access SPC2WTS account and switch to Delegation



    Select SQL Server service with Port 1433 or whatever you have designated for your SQL Server.


    Let's switch the Web Application Authentication to Kerberos

    From CA>Manage Web Applications> Select your web app and chose Authentication providers



    This completes the configuration. In the next blog lets create an example SSRS, Deploy to SharePoint and test.

    Test Connection: Coming soon…


    Anonymous said...

    Hi Rajesh,

    Can you provide inputs on creating test connection and test report to test the completion of SSRS - SharePoint integration.

    Unknown said...

    Heya! This guide is a lifesaver.

    Once question, though, what in the guide creates the WPS_WPG group that we add to the c2wtshost.exe.config file? My server doesn't end up with that group after the dust settles...

    Thanks again!

    Rajesh Agadi said...

    Response for the above post by Unknown....

    Good question...

    I am validating if this should be WPS_WPG or WSS_WPG...

    Will post back once I have clear answer

    Anonymous said...

    Rajesh Thanks for helping us by blogging good stuff. it lifesaver. Reaching to find any updates on creating WPS_WPG group.

    Rajesh Agadi said...

    Hello Anonymous..

    It should be WSS_WPG, I have updated the blog above now...

    Anonymous said...

    Hi Rajesh, Great Article, I am trying to follow this, but still facing some challenges. Do you think we need to create these SPN's too :-
    Setspn -S SP/C2WTS corp\SPC2WTS
    Setspn -S SP/SSRS corp\SPSSRS
    Setspn -S SP/PPS Corp\SPPerfPointUser
    Setspn -S SP/Excel corp\SPExcelUser
    -I am not using performance pointer and excel user in my project? do I need to still create these SPN's? And also What is "SP/" refers too here? because I can create SPN for HTTP and MSSQLSvc, but not sure with SP/ - can you pleas clarify. thanks Kings

    Rajesh Agadi said...

    Hi Kings,

    If you are not using PerformancePoint and ExcelServices, then only skip etspn -S SP/PPS Corp\SPPerfPointUser
    Setspn -S SP/Excel corp\SPExcelUser

    But all other SPNs are necessary.

    In SPN, like HTTP and MSSQLSvc, Microsoft has registered the SP namespace for registering SharePoint related services for Kerberos. So you have to use SP only like you have to use MSSQLSvc for registering SQL Server service.

    Hope This helps.