CSS SQL Server Engineers

This is the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support. Posts are provided by the CSS SQL Escalation Services

SharePoint Adventures : Setting up Reporting Services with SharePoint Integration

SharePoint Adventures : Setting up Reporting Services with SharePoint Integration

  • Comments 5

I've had some questions in the past regarding Reporting Services Integration with SharePoint. For this post, I'm going to focus on SharePoint 2010. I'm also going to assume that you already have a SharePoint Farm setup. The examples I'm going to use will be a full SharePoint Cloud, but the steps will be similar for a SharePoint Foundation Server.

Overview of Reporting Services and SharePoint Technology Integration
http://msdn.microsoft.com/en-us/library/bb326358.aspx

Deployment Topologies for Reporting Services in SharePoint Integrated Mode
http://msdn.microsoft.com/en-us/library/bb510781.aspx

Configuring Reporting Services for SharePoint 2010 Integration
http://msdn.microsoft.com/en-us/library/bb326356.aspx

Lets start off with some key documentation that you can use for reference when you do this:

Lets also talk about my setup that I will be walking through. I will have 4 servers. This consists of a Domain Controller, a SQL Server, a SharePoint Server and a server for Reporting Services. You may opt to have SharePoint and Reporting Services on the same box, which will simplify this a bit and I will point out some of the differences.

The Reporting Services Add-In for SharePoint is one of the key components to getting Integration working properly. The Add-In needs to be installed on any of the Web Front Ends (WFE) that are in your SharePoint farm along with the Central Admin server. One of the new changes with SQL 2008 R2 & SharePoint 2010 is that the 2008 R2 Add-In is now a pre-req for the SharePoint Install. This means that the RS Add-In will be laid down when you go to install SharePoint.

clip_image001

This actually avoids a lot of issues we saw with SP 2007 and RS 2008 when installing the Add-In.

SharePoint Authentication

Before we jump into the RS Integration pieces, one thing I want to point out about the SharePoint Farm is how you setup the Site. More specifically how you configure authentication for the site. Whether it will be Classic or Claims. This choice is important in the beginning. I don't believe that you can change this option once it is done. If you can change it, it would not be a simple process.

NOTE: Reporting Services 2008 R2 is NOT Claims aware

Even if you choose your SharePoint site to use Claims, Reporting Services itself isn't Claims aware. That said, it does affect how authentication works with Reporting Services. So, what is the difference from a Reporting Services perspective? It comes down to whether you want to forward User Credentials to the datasource.

Classic - Can use Kerberos and forward the user's credentials to your back end datasource (will need to use Kerberos for that.

Claims - A Claims token is used and not a windows token. RS will always use Trusted Authentication in this scenario and will only have access to the SPUser token. You will need to store your credentials within your data source.

I'll look at the authentication pieces in later posts. For now I just want to focus on setup of RS. At this point SharePoint is installed on my SharePoint Box and setup with a Classic Auth Site on port 80. On the RS Server I have just installed Reporting Services and that's it.

Setting up Reporting Services

Our first stop on the RS Server is the Reporting Services Configuration Manager.

Service Account:

Be sure to understand what service account you are using for Reporting Services. If we run into issues, it may be related to the service account you are using. The default is Network Service. When I go to deploy new builds, I always use Domain Accounts, because that is where I'm likely to hit issues. For my server I've used a Domain Account called RSService.

clip_image002

Web Service URL:

We will need to configure the Web Service URL. This is the ReportServer virtual directory (vdir) that hosts the Web Services Reporting Services uses, and what SharePoint will communicate with. Unless you want to customize the properies of the vdir (i.e. SSL, ports, host headers, etc…), you should just be able to click Apply here and be good to go.

clip_image003

When that is done you should see the following

clip_image004

Database:

We need to create the Reporting Services Catalog Database. This can be placed on any SQL 2008 or SQL 2008 R2 Database Engine. SQL11 would work ok as well, but that is still in BETA. This action will create two databases, ReportServer and ReportServerTempDB, by default.

The other important step with this is to make sure that you choose SharePoint Integrated for the database type. Once this choice is made, it cannot be changed.

clip_image005

clip_image006

clip_image007

For the credentials, this is how the Report Server will communicate with the SQL Server. What ever account you select, will be given certain rights within the Catalog database as well as a few of the system databases via the RSExecRole. MSDB is one of these database for Subscription usage as we make use of SQL Agent.

clip_image008

Once that is done, it should look like the following:

clip_image009

clip_image010

Report Manager URL:

We can skip the Report Manager URL as it isn't used when we are in SharePoint Integrated mode. SharePoint is our frontend. Report Manager doesn't work.

Encryption Keys:

Backup your Encryption Keys and make sure you know where you keep them. If you get into a situation where you need to migrate the Database or restore it, you will need these.

clip_image011

That's it for the Reporting Services Configuration Manager. If you browse to the URL on the Web Service URL tab, it should show something similar to the following.

clip_image012

What happened? SharePoint is installed on my WFE and I finished setting up Reporting Services. In this example, Reporting Services and SharePoint are on different machines. Had they been on the same machine, you wouldn't have seen this error. We technically need to install SharePoint on the RS Box. That means IIS will be enabled as well.

Setting up SharePoint on the RS Server

So, we need to do what we did for the SharePoint WFE. First thing is to go through the Prereq install. After that is done, startup the SharePoint setup.

For the setup I choose Server Farm and a complete install to match my SharePoint Box, as I do not want a standalone install for SharePoint.

SharePoint Configuration

In the SharePoint Configuration Wizard, we want to connect to an existing farm.

clip_image013

We will then point it to the SharePoint_Config database that our farm is using. If you don't know where this is, you can find out through Central Admin through System Settings -> Manager Servers in this farm.

clip_image014

clip_image015

Once the wizard is done, that is all we need to do on the Report Server Box for now. Going back to the ReportServer URL, we will see another error, but that is because we have not configured it through Central Administrator.

clip_image016

Reporting Services SharePoint Configuration

Now that SharePoint is installed and configured on the RS server and RS is setup and setup through the Reporting Services Configuration Manager, we can move onto the configuration within Central Admin. RS 2008 R2 has really simplified this process. We use to have a 3 step process that you had to perform to get this to work. Now we just have one step.

We want to go to the Central Administrator Web site and then into General Application Settings. Towards the bottom we will see Reporting Services.

clip_image017

We will click on "Reporting Services Integration".

clip_image018

Web Service URL:

We will provide the URL for the Report Server that we found in the Reporting Services Configuration Manager.

Authentication Mode:

We will also select an Authentication Mode. The following MSDN link goes through in detail what these are.

Security Overview for Reporting Services in SharePoint Integrated Mode
http://msdn.microsoft.com/en-us/library/bb283324.aspx

In short, if your site is using Claims Authentication, you will always be using Trusted Authentication regardless of what you choose here. If you want to pass windows credentials, you will want to choose Windows Authentication. For Trusted Authentication, we will pass the SPUser token and not rely on the Windows credential.

You will also want to use Trusted Authentication if you have configured your Classic Mode sites for NTLM and RS is setup for NTLM. Kerberos would be needed to use Windows Authentication and to pass that through for your data source.

Activate feature:

This gives you an option of activating the Reporting Services on all Site collections, or you can choose which ones you want to activate it on. This just really means which sites will be able to use Reporting Services.

When it is done, you should see the following

clip_image019

Going back to the ReportServer URL, we should see something similar to the following

clip_image020

NOTE: If your SharePoint site is configured for SSL, it won't show up in this list. It is a known issue and doesn't mean there is a problem. Your reports should still work.

So, that's it! I know, that makes it look simple and depending on your deployment/configuration, you may run into other issues. I'll try and cover some of those in later posts.

clip_image021

Adam W. Saxton | Microsoft SQL Server Escalation Services
http://twitter.com/awsaxton

Leave a Comment
  • Please add 3 and 7 and type the answer here:
  • Post
  • Great post. Here’s a tool to use to create and publish your report online in minutes, without coding. You can create different graphs and layoutswww.caspio.com/.../reports.aspx

  • HI. Great post. Thanx for that. The only problem i have is that after the configuration  of the database (Reporting serivices configuration manager) the Reportserver is still in Native mode. Whatever i try, or change, it doenst change into Sharepoint integrated mode. Any tips? Thanx

  • Hi Adam.

                 Thanks for nice post, i have SP configured to 443 ssl site, and reporting services to 444 ssl.

    earlior i tried reporting services without ssl and it does not allow me to deploy reports in SharePoint Report Document Lib.

    do you come across any issue related to deployment of reports again. ?

    -Sam

  • is it mandatory to have Sharepoint installed on Report server machine? Please clear asap...

  • Great Post Adam, but we are facing a problem where we have 2 Front Ends and Report Server is configured in one of them. Is it possible to access to the reports from one front end but from the other front end we are recieving the following message: The file you are attempting to save or retrieve has been blocked from this Web site by the server administrators

    Have you ever faced a problem like that?

Page 1 of 1 (5 items)