Pages

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

Overview

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:

    http://technet.microsoft.com/en-us/video/Video/hh858469

    http://blogs.technet.com/b/sharepoint_-_inside_the_lines/archive/2013/05/28/sharepoint-2013-with-ssrs-2012-and-constrained-delegation.aspx

    Steps

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

    clip_image001

    clip_image002

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

    clip_image003

    clip_image004

    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

    clip_image005

    clip_image006

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

    clip_image007

    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

    clip_image008

    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:

    clip_image009

    Before

    clip_image010

    After

    clip_image011

    Get the SSRS Service account Access to the Web Applications.

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

    clip_image012

    clip_image013

    clip_image014

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

    Ensure that C2WTS is in local admin

    clip_image015

    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

    clip_image016

    clip_image017

    clip_image018

    Restart Windows Service C2WTS

    clip_image019

    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

    image

    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/portal.mydomain.com corp\SPPortal
    Setspn -S MSSQLSvc/DC:1433 corp\sqlservice
    Setspn -S MSSQLSvc/DC.corp.mydomain.com:1433 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


    clip_image021



    clip_image022



    clip_image023



    Run ADUC to, ensure Advanced Feature



    clip_image024



    Check SQLService account Attribute for SPN



    clip_image025



    clip_image026



    Configure Delegation for SPC2WTS



    From the ADUC, access SPC2WTS account and switch to Delegation



    clip_image027




    clip_image028



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



    clip_image029



    Let's switch the Web Application Authentication to Kerberos



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



    clip_image030




    clip_image031




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



    Test Connection: Coming soon…

    7 comments:

    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.

    -Rajesh