Skip to main content

Installing and Configuring SSRS in SharePoint Integrated Mode

To put it bluntly, configuring SQL Server Reporting Services 2005 or 2008 to run in SharePoint integrated mode with Microsoft Office SharePoint Server 2007 is a pain in the behind.  The setup of the numerous moving pieces seems to get confused between two sets of configurations, two sets of security, and two sets of databases. (I would get confused with all those pieces too! ;) )  Let's take a look at some highlights of the installation steps and a few warnings that may help you in your environment.

It makes sense that you would need Reporting Services (2005 with SP2 or 2008) and SharePoint (WSS 3.0 or MOSS 2007) in some fashion on your servers.  The secret ingredient to tie everything together is actually an extra add-in that must be installed on your SharePoint server.  This is a free download that can be found here (2005) or here (2008).

Once you have installed all components, you must complete a series of screens in the SharePoint Central Administration tool.  The screens tell SharePoint where the report server resides and sets up the security so that the two pieces can talk to each other.  You need to be sure that you have a user account that has access to both server/databases to facilitate this.

Warning, warning!
Here are a few road bumps that I have run into in the past:

  • When you configure your SSRS databases, ensure that you have selected the option to create the database in SharePoint Integrated mode.  Each mode creates its own type of database and never the twain shall meet.
  • Once you get your environment set up, make sure Reporting Services reports are stored in the Default zone.  Otherwise, the reports will not render.
  • Don't mix your SharePoint and Reporting Services (2005) applications in IIS.  SharePoint should have sole ownership of its virtual, and SSRS's setup should not be disturbed.

Do as I say, not as I do
To install and configure your environment, I highly recommend that you follow the steps in the EXACT order as written in the Microsoft SQL Server Reporting Services (SSRS) Installation/Configuration Guide for SharePoint Integration Mode whitepaper.  While this document is written for SSRS 2005, it very closely aligns with the 2008 method as well.  Good luck with your setup!


Anonymous said…
Thanks for the blog, but following that word doc from MS is a waste of time. Many missing steps there. On side note I cannot beleive how difficult this is to implement. Been at it for 2 weeks with no luck. Days and dasy of work. Figured there would be a one stop shop for documentation but it's scattered everywhere. All in all a truly cruddy product.
Unknown said…
I've felt these pains many times over. SSRS 2005 wasn't too hard to integrate but 2008 SP1 had some funny quirks that I've documented on my blog -
Unknown said…
Jessica Moss´s eternal slave

Whatever you say about SQL Server.. I agree and always will

Jessica Moss´s eternal slave
Krishna said…
Hello Jessica,

Thanks for the post and i am stuck i was sucessfull in integrating the sharepoint with ssrs, but i get this error when i deploy the reports in sharepoint

Theitem 'documentlibrary/folder/test.rdl' cannot be found. (rsItemNotFound) .

so can you please let me know. How can i reslove this issue
Jessica M. Moss said…
Hi Sai,

Thanks for your comment. It's hard to determine the exact cause of an error without seeing your setup, but here are some suggestions for you to try!

For some reason, it can't find that location. Try setting up a new document library and pointing your deployment location to that library instead.

There are also some good suggestions on this page:

Good luck!
Krishna said…
Thanks! Jessica for the reply.I have deleted the document library & created the new document library and set deployment location to new document library. But still it doesnt work.

I am lost from three weeks with this error, i tried all most every thing.
Krishna said…
Starting i use to work on Dev as a standlalone Moss devlopment server, then i started a project of SSRS with sharepoint inegration and built reports in dev2 and i installed SharePointRS-Addin in dev & dev2, Sp2 in dev2 and followed the microsoft steps to make dev2 as webfront end report server to join the dev farm using this link ( i was successfull till last step of integration after so many errors.

dev server contains its own content database

dev2 has it own report server database

i installed following on both servers

SQL Server 2005 Reporting Services
SQL Server 2005 Service Pack 2
Office SharePoint Server 2007 ( dev2 in webfront end and added to dev which is in standalone )
Microsoft SQL Server 2005 Reporting Services Add-in for Microsoft SharePoint Technologies -
Jessica M. Moss said…
Hi Sai,

I'm afraid we've gone beyond what help I can give through a blog comment. You may consider looking into on-site help at this time.

Best of luck,
The.Zavala said…
I am running into what appears to be an integration issue... I am using MOSS 2007 with a SQL 2005 bk-end DB. I am trying to setup SSRS 2008 as the reporting engine but getting an error when running the SP Products and Technologies Configuration Wizard indicating there is an access denied issue. Any thoughts? ~JZ
Jessica M. Moss said…
Hi JZ,

The best thing would be the ensure that you have all of the security set up properly between all of the machines. Here is an overview of the security needed:

Good luck!
dyard said…
Do you still have the white paper?Microsoft SQL Server Reporting Services (SSRS) Installation/Configuration Guide for SharePoint Integration Mode whitepaper.

Were still on SQL 2005 and MOSS2007 I have everything configured. verified everything but still cant get the sharepoint integration recognized on the report services configuration manager. I am new to this company so Reporting services was insalled last could that be the reason?
Jessica M. Moss said…
Hi dyard,

I'm sorry I no longer have the whitepaper, but it appears that a lot of the same troubleshooting steps can be found here: Although it is for 2008R2, I hope some of it is still applicable for you!

Hi Jessica,
We have a 1 WFE (MOSS 2007) & 1 Db Server. How can I configure SSRS to use SQL Reporting in Sharepoint environment ?
Do I need to install Sharepoint 2007 in Db server as well ?

Popular posts from this blog

Upgrading your SSIS Management Framework: Part 3

At this point, you understand the options for moving an SSIS framework to the latest version of SSIS, and you've upgraded the logging portion of the framework using a hybrid approach.  The final step in the framework upgrade is handling your configurations.  Let's walk through an existing configuration implementation and how you can upgrade it by combining your existing implementation with the standard SSIS framework. Overview A typical "old-school" configuration scheme is described in the SSIS PDS book or in this blog post here: .  Starting in SSIS 2012, the configuration scheme uses environments and parameters when using the Project Deployment Model, as discussed here: . In both scenarios, the core ideas in a configuration scheme are: Provide the ability to move packages through environments without having

Manipulating Excel Spreadsheets in SSIS

Tom, an attendee at last weekend’s SQLSaturday Olympia , asked me how to refresh a spreadsheet from within SQL Server Integration Services. My first thought was to turn on the connection’s “Refresh data when opening the file” option in the spreadsheet itself and avoid the situation entirely; however, this may not always be a viable solution. Here are the steps to perform the refresh from within an SSIS package. First, ensure that Microsoft.Office.Interop.Excel is registered in the GAC. If not, install the 2007 Microsoft Office system Primary Interop Assemblies . This will need to be done on any machine where you plan on running this package. Next, create a script task in your SSIS package that contains the following code (include your spreadsheet name): Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Imports Microsoft.Office.Interop.Excel Public Class ScriptMain Public Sub Main() Dts.TaskResult = Dts.Results.Success Dim excel

Reporting Services 2008 Configuration Mistake

To start working with the management side of SQL Server Reporting Services 2008, I decided to set up a report server and report manager. Unfortunately, I made a mistake while setting up my configuration that left me a little perplexed. Here are the steps I took to cause, track down, and solve the issue. Problem: I began by opening the Reporting Services Configuration Manager from the Start Menu. I clicked through each of the menu options and accepted the defaults for any question with a warning symbol, since warning symbol typically designate an action item. After two minutes, all of the warning symbols had disappeared, and I was ready to begin managing my report server. Unfortunately, opening up a browser and trying to open up the report manager resulted in the dreaded " The report server has encountered a configuration error. (rsServerConfigurationError) " message. Sherlock-ing it: I put on my sleuthing hat and went to the log file directory: C:\Program Files\Microsoft