Pages

Thursday, July 18, 2013

Installing Reporting Services In SharePoint Mode (Part I)

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

Overview

This blog article discusses how to install and configure SSRS Service Application in SharePoint Mode (Which is this Part I) but 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. The Part II covers these aspects.

Steps

Install Reporting Services In SharePoint Mode

  • Reference http://technet.microsoft.com/en-us/library/jj219068.aspx
  • From SQL media install Reporting Services in SharePoint Mode and RS Add in for SharePoint
  • Important: What and where needs to be installed and configured
  •  

    Install RS in SP Mode

    Install RS Add In

    Install SQL 2012 SP1

    KB2793634

    Run Install-SPRSxx command

    Start SSRS Service

    SP2013WFE

    No

    Yes

    Yes

    No

    Yes

    No

    SP2013APP1

    (Hosts CA)

    (Where SSRS will run)

    Yes

    Yes

    Yes

    Yes

    Yes

    Yes

    SP2013APP2
    (All Other servers)

    No

    Yes

    Yes

    No

    No

    No

     
     

    On the APP1 Server

    Login in to SP2013APP1 as SPSetup

    clip_image001 Mount SQL 2012 Enterprise (Developer) with SP1 Media (Remember you will need minimum with SP1)

    Start Setup, Switch to Installation from the left.

    clip_image002

    clip_image003

    clip_image004

    Install Reporting Services in SharePoint mode and the Reporting Services add-in for SharePoint products.

    clip_image005

    clip_image006

    clip_image007

    Download and install SP1 http://www.microsoft.com/en-us/download/details.aspx?id=35575

    clip_image008

    Download and install Non Security Update for SQL Server 2012 SP1 (KB2793634) From http://www.microsoft.com/en-us/download/details.aspx?id=36215

    clip_image009

    clip_image010

    Reboot Server

    On the WFE Server

    Follow the above chart at the beginning to install on all other servers. Ensure to install as necessary components.

    On SP2013WFE login as SPSetup and install following from SQL Media

    clip_image011

    Reboot Server

    clip_image010[1]

    On SP2013APP2

    clip_image011[1]

    Reboot

    Create and Configure SSRS Service on APP1

    Browse to CA with SPSetup

    clip_image012

    Ensure to choose your APP1 Server and View scope is All

     

    clip_image014

    Create Reporting Services service application.

    clip_image015

    clip_image016

    clip_image017

    clip_image018

    clip_image019

    Click on Provision Subscripts and Alerts

    clip_image020

    Click on Download script , save script and run in your SQL Server

    clip_image021

    clip_image022

    Back in to the Provision Subscripts and Alerts settings, I have entered CORP\Administrator since it was sysadmin on the SQL Server.

    clip_image023

    Click ok

     

    Final Services State

    image

    14 comments:

    Prashant Gaikwad said...

    Provision Subscriptions and Alerts - Getting an Error, "User name or password is incorrect". Could you please suggest, which account needs to be specified here.

    Rajesh Agadi said...

    Prashant,

    You will need an account that has sufficient permissions to grant execute permissions to the RSExecRole for objects in the service application, msdb, and master databases. You can create a dedicated AD account, add to SQL Server and provide above specific rights to follow minimal security best practice (Definitely for Production env)

    Ensure SQL Agent is running.

    I had used the domain admin in this setup for simplicity which as sysadmin rights in SQL.

    Hope this helps.

    -Rajesh

    Anonymous said...

    Your step for entering the domain admin credentials is not required.

    Read from:
    http://technet.microsoft.com/en-us/library/hh231725.aspx

    This topic describes three ways you can update the permissions:
    1.
    From the Provisions and Subscriptions and Alerts page, type credentials and click ok.

    2.
    From the Provisions and Subscriptions and Alerts page, click the Download Script button to download a transact SQL script that can be used to configure permissions.

    3.
    Run a PowerShell cmdlet to build a transact SQL script that can be used to configure permissions.


    Please update your post.

    Rajesh Agadi said...

    Hello Anonymous commenter,

    Thanks for the suggestion, I entered my domain administrator account since it was a sysadmin on my SQL. The TechNet article does not specify what specific SQL rights are necessary for this credential entry.

    Upon executing the downloaded script, your will need SP_execute rights, which is why I chose something that has sysadmin for simplicity. For the production scenario please evaluate your rights as necessary.

    Thanks
    Rajesh

    Vinoth VL said...

    Report Server Integration Feature is missing in central admin

    Osvaldo said...

    Your chart with the component required for each server is super useful for a multilayer farm. Have you any similar chart for PowerPivot on SharePoint 2013?
    Thanks.

    Unknown said...

    I'm pretty sure that the commands:
    Install-SPRSService and Install-SPRSServiceProxy should only be run on one Server in the Farm. That being the Server where the SQL Server Reporting Services Service is started.

    If I run those commands on any other Server in the Farm as well, then I'm unable to get into the System Settings of the Service Application. I always get an Error 503 in that case.

    I've followed the guide several times now with different Farms and it's always the same.

    Hope this helps.

    Otherwise a good guide :-)

    jobyjohnkj said...

    Hi Rajesh, Thank you for this blog post. It really helped me. Especially, the table of "What and where needs to be installed and configured".

    FirozOzman said...

    Thanks for the post. This helped in configuring my environment on SP2013 and SQL2014. Thanks again.

    Ravi Khambhati said...

    Is there a way to run below step through powershell or command prompt

    Install Non Security Update for SQL Server 2012 SP1 (KB2793634)

    OptiSol Solutions said...

    Thanks for this value able post. I have read all the things very carefully it’s really a helpful and effective post.

    SQL Reporting Services

    Luis DP said...

    Rajesh,

    I was having a lot of problems installing SSRS and your article cleared things up. The SQL Reporting add on for sharepoint download from the MS site is problematic. The Reporting service will have a 'error starting' message if you use the MS download. you have to use the SQL media (iso) in order to make it work.

    I spent one day figuring it out and a technet article had the solution.

    Great article!

    Luis

    Riaz said...

    Thank you for this excellent article. It's the best one I've found. It clarifies the entire process very well.

    kcn2bluesky said...

    This was a great article and thank you very much for the detail on configuring this service! Very helpful, and was instrumental in me finally getting Report Services configured successfully.