Tuesday, November 24, 2009

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.

Ben Sullins 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 - http://bensullins.com/ssrs-2008-add-in-for-moss-2007/

Talles Roberto said...

Jessica Moss´s eternal slave

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


Jessica Moss´s eternal slave

sai 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: http://msdn.microsoft.com/en-us/library/ee384252.aspx

Good luck!

sai 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.

sai 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 (http://blogs.msdn.com/sharepoint/attachment/4194088.ashx) 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: http://msdn.microsoft.com/en-us/library/bb283324.aspx

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: http://msdn.microsoft.com/en-us/library/ee384252(SQL.100).aspx. Although it is for 2008R2, I hope some of it is still applicable for you!


Vishi said...

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 ?