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.
Components
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).
Security
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!
Comments
Whatever you say about SQL Server.. I agree and always will
robertdba@hotmail.com
Jessica Moss´s eternal slave
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
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!
Jessica
I am lost from three weeks with this error, i tried all most every thing.
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 -
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,
Jessica
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!
Jessica
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?
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!
HTH,
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 ?
Thanks,Vishi.