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.

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

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 - http://bensullins.com/ssrs-2008-add-in-for-moss-2007/
Unknown said…
Jessica Moss´s eternal slave

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

robertdba@hotmail.com

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

Good luck!
Jessica
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 (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,
Jessica
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!
Jessica
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!

HTH,
Jessica
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 ?
Thanks,Vishi.

Popular posts from this blog

SSIS Configuration to Configuration to Configuration Schema

I've gotten several requests to put down in writing the configuration schema that I use as the base of my SQL Server Integration Services framework. It contains a set of configurations: an indirect environment variable configuration, which points to an XML configuration file configuration, which points to a SQL Server configuration. I learned this configuration from the Project REAL Reference Implementation . If you're getting started with a BI implementation, I highly recommend that you download it for some great ideas and best practices. Steps to implement: 1) Create an environment variable on your machine with the name of SSIS_CONFIG_FILE and the value of: C:\SSIS\Config\MasterConfigFile.dtsConfig. 2) Create an SSIS configuration file at C:\ SSIS\Config\MasterConfigFile.dtsConfig with the line: <configuration valuetype="String" path="\Package.Connections[CONFIG_SERVER].Properties[ConnectionString]" configuredtype="Property"><configuredv

Execute SQL Task Designer Limit

After migrating a package from DTS to SSIS, I had a problem with an Execute SQL Task. I couldn't change any characters in the SQLStatement property; I couldn't add any new characters; I could delete characters, but not retype them! After googling several variations of "integration services" "read only" and "Execute SQL Task", I deleted about half of the entry in a fit of frustration. Lo and behold, I could type again. Apparently, there is limit on the size or number of characters that can be entered in the SQLStatement property. From my experimentation, I came up with a limit of 32767 characters. The interesting thing is that the restriction only seems to be on the designer. If you set the SourceType to "Variable" and use a variable that contains more than 32767 characters, the task will execute. Also, if you use the "Direct Input" SourceType and modify the package XML to set the SQLStatement longer than 32767 characters,

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