SharePoint Adventures : Setting up Reporting Services with SharePoint Integration
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
https://msdn.microsoft.com/en-us/library/bb326358.aspx
Deployment Topologies for Reporting Services in SharePoint Integrated Mode
https://msdn.microsoft.com/en-us/library/bb510781.aspx
Configuring Reporting Services for SharePoint 2010 Integration
https://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.
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.
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.
When that is done you should see the following
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.
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.
Once that is done, it should look like the following:
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.
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.
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.
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.
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.
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.
We will click on "Reporting Services Integration".
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
https://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
Going back to the ReportServer URL, we should see something similar to the following
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.
Adam W. Saxton | Microsoft SQL Server Escalation Services
https://twitter.com/awsaxton