Home > MOSS 2007, SSRS > SSRS 2008 Add-in for SharePoint

SSRS 2008 Add-in for SharePoint

October 12th, 2008 Leave a comment Go to comments

Unless you are living under the rock , you’d probably know that since SQL Server 2005 SP2 time frame , you can configure a deployment of SQL Server Reporting Services to work with a deployment of SharePoint – known as SSRS 2008 installation in SharePoint Integrated mode. In the first attempt to combine their Business intelligence and Information Portal technologies together and by releasing SQL Server 2005 SP2 , Microsoft brought two very interesting technologies together and opened up a world of interest to many people including myself . When I first read about SP2 , I was like wait a minute, this is awesome!  Both products that I had been really passionate about for years now are getting much closer! Needless to say that I’ve always appreciated the  efforts Microsoft has put into making the technology , geekyness and weirdness around it transparent to the vast majority of people out there.

When SQL Server 2005 SP2 was released , MS really demonstrated that not only do they listen to their customer feedback, they also care so much about developers and to make their life easier. For example , in the context of SharePoint and SSRS integration , now you can potentially hire a report developer,they can build the reports (in much easier way) and publish them into SharePoint and basically hand them over to the  user community and business users. From here , they can take the wheel, manage and interact with these reports (high level) without having to know what the hell is going on under the hood! Isn’t that amazing that how two completely different technologies can be combined to make things much easier for everyone? Remember, easier something is, more people will use it. More people use it, more popular you’ll become! Microsoft ,for sure, has proved that they’ve learned this very simple rule of life…

The only thing that tipped me over the edge at that time was when I first attempted to bring the the best out of both products in a “real” integration project with a very “difficult-to-get-along” kind of client! (I still have the  nightmare of those two days) .  I really don’t want to talk about those issues here , but what made it difficult for me was no  proper documentation , no active community around both products  and , to an extend,  the immaturity of the integration . Things certainly have changed since then and obviously I’ve learned my “integration” lessons as well! Not that I don’t face any issues these days, but nowadays it’s much easier to find an answer – and yes , I find the answers to the majority of my questions in the blogs of those who are blessed and willing to SHARE their POINTS with the rest of the world!

When SQL Server 2008 and SSRS 2008 was RTMed , I didn’t make the same mistake I had made back in 2005 🙂 . I decided to to gain some home-based lab experiences before I go live with this in real engagements (Didn’t I just tell you that I learnt my integration lessons? 😉  ) Surprisingly, every installations I had at my home-based farms from a single stand-alone installation to a scale-out SSRS  along with a large SharePoint Server farm went really smooth without big stucking points. Documentation around the integration is much better this time around, but I am still not happy by the coverage of SSRS 2008 and SharePoint integration by MS people and community! Let’s hope it gets better soon.

Speaking of SSRS 2008 Add-in for SharePoint, here is one question that I frequently get asked :

I have configured report server in SharePoint integrated mode. I have installed SharePoint Web front-end components on the report server computer.I have downloaded and installed the Reporting Services Add-in for SharePoint Technologies on my other  Web front-end servers (including the one that hosts the Central administation site) , but Reporting Services section doesn’t appear in the Central Administration site;therefore I cannot complete the integration.  Where did it go?

Well , the answer is : You need to activate a site collection-scoped feature called Report Server Integration Feature on the Central Administration site.

CentralAdminReportServerIntegrationFeatureAtSiteCollectionLevel

This feature has two different behaviors when gets activated on Central administration site than other sites. When activated on the Central administration site , the feature does all of the things it does for other type of sites , plus it adds a section  called Reporting Services under the Application Management. This section must be used to make sure SharePoint is aware of my SSRS instance existence. Here is where the fun part starts 🙂 .

There are three options in this section:

SSRSCustomSectionCentralAdmin

  1. Grant Database Access: First you need to specify the server which hosts reporting services database, whether it is on a default or named instances. Essentially what happens here is that the Report Server endpoint and Windows service accounts for that instance (named or default) will be granted required access to the SharePoint databases. During this process, the Report Server service will be restarted.  This is an essential step in integration.
  2. Manage integration settings  :  You need to specify Report server URL and the authentication. Pretty straightforward.
  3. Set Server Defaults :You set all of your basic defaults. This page contains all of the things you’d normally use Reporting Services Configuration tool to configure them, but they are now managed via SharePoint tier. For example making sure that all data sources use integrated security, so on and so forth. Ad-hoc reporting  is also a powerful feature which can be set and controlled from here.

There is one more action that Reporting Services Add-in for SharePoint Technologies performs on the Central Administration site which is provisioning SSRS integrated help content in the HelpFold  folder:

HelpFolder

The add-in also installs some application pages, including pages that you open in Central Administration to set the report server URL and other integration settings in Central Administration and other sites.

ReportServerLayoutPictures

In addition to the application pages , the Proxy endpoints are also placed in the 12\ISAPI\ReportServer folder. As you can tell , all of the Reporting Services Proxy endpoints are nicely virtualized and context aware (note wsdl and disco aspx file for each Web service). This means  that no matter how deep you are in each site collection , these endpoints are always accessible via a call to the respective asmx file – for example http://mysite/_vti_bin/ReportServer/ReportServer2006.asmx or  http://mysite/subsite1/…../subsiteN/_vti_bin/ReportServer/ReportServer2006.asmx.

More on SSRS Proxy endpoints in  the Integrated mode can be found here.

ISAPI Files

A quick list of proxy endpoints here:

  1. ReportService2006.asmx : Proxy endpoint to support SharePoint Integrated mode. New functionalities such as Data Driven subscriptions are added to this endpoint.
  2. ReportExecution2005.asmx :  Execution endpoint. New functionalities such as On demand load (a.k.a pagination) is added to this endpoint.
  3. As you can tell , ReportService.asmx (SSRS 2000 SOAP endpoint) which was deprecated in 2005 , now is removed and no longer supported!
  4. ReportService2005.asmx: Proxy endpoint to support Native mode (not in this picture- I hope you know why 🙂 )

All right , let’s just go ahead and see what happens to non-Central admin sites :

First of all , the same feature that we just activated in the Central administration site appears on every site collection meaning that if you want to have the Reporting Services integration , you need to activate this on each site collection:
ReportServerIntegrationFeatureAtSiteCollectionLevel

Once you activate this feature , the following things will be added to your site collection:

Required content types:

ReportsContentType

Report Viewer Web part:

ReportViwerWebPart

A section in the Site Settings for managing shared schedules :

Site Settings

Obviously if you want to be able to store your reports in a Report Library and your data sources in Data Connection Library , you need to enable another web-scoped  “Office SharePoint Server Enterprise Site features” feature to get Report Library and Data Connection Library in the create page. This has nothing to do with SSRS Add-in though !

EnterpriseFeaturesAtSiteCollectionLevel

That’s all about it! I hope this blog post can help you verify your SSRS 2008 installation in integration mode.

  1. ann
    March 13th, 2009 at 18:11 | #1

    Hi Reza,
    I found this blog very useful thank you. I am currently trying to use ssrs 2008 in sp integrated mode, everything running on a standalone environment. it is just one vm, all sharing the same service account (administrator). i know this is not fit for a production environment but I am taking baby steps through the security! I’ve run into a hitch when trying to deploy a report from bids to the sharepoint reports library. It keeps prompting me for credentials and won’t accept anything I give it, not even the administrator account which has access to everything. Have you encountered and/or resolved this issue?

    thanks,
    ann

  2. Reza Alirezaei
    March 13th, 2009 at 18:41 | #2

    @ann
    Ann,

    Are you sure you are using the right URLs for the target report folder , data source and report server?

  3. mit
    March 18th, 2009 at 11:34 | #3

    Reza,

    You define this process really well. hats off.. I am installing the SSRS 2008 with Sharepoint integrated mode with multi server enviornment. this article will really going to help me while i am configure the SSRS and MOSS. do you want to refer any other documents please let me know. First time i got error in Reporting server as well as my multi server moss was down due to configuration error. but later on some one suggested your site so, i am going to follow up with your steps.. if you want me to give any advice please let me know.

    Enviorment Information:

    1 SSRS 2008 (Newly build server with sharepoint integrated mode)
    1. web front end server (MOSS 2007)
    1. App Server (MOSS 2007)
    1. Sharepoint Database server(MOSS 2007)

    Question 1.

    Do you think I need to install (Web front end) Sharepoint on the SSRS 2008 server? OR Share Point add-ins?

    Question 2.

  4. JT
    March 20th, 2009 at 10:02 | #4

    I am trying to install SQL 2008 RS in a NLB Clustered environment. I have a 2 frontend MOSS web servers, 2 MOSS app servers, 2 SQL08 AS Servers and 2 SQL08 RDMS servers. I am attempting to install RS on the AS servers and integrate with the front end and app MOSS servers.

    Have you done anything like this or know if anyone else has?

    thanks in advance, JT

  5. Scott
    March 27th, 2009 at 17:44 | #5

    Have you used SSAS through Share Point? Is the correct way to deploy SSAS on Share Point through excel?

  6. Reza Alirezaei
    March 28th, 2009 at 14:45 | #6

    @Scott
    @JT

    Sorry ,never tried to hit the AS cubes from SharePoint , but i’ve done drillthrough SSRS reports against cubes in the native me.

    Practically speaking , you should be able to use models and get the Report Builder to allow you to query them. I think that’s one options.

  7. Reza Alirezaei
    March 28th, 2009 at 14:48 | #7

    @mit

    >>Do you think I need to install (Web front end) Sharepoint on the SSRS >>2008 server? OR Share Point add-ins?

    SQL Server add-on on all of WFE servers.
    SSRS machine needs to be joint to the farm which means you need to install sharepoint bits (WFE only) on it.

    I don’t see your question 2 though!

  8. mit
    March 30th, 2009 at 12:58 | #8

    my second question is what install order i should follow? do i need to installed RS add-ins to the Application server first OR Web Front End server OR Reporting Server ( after installing WFE component this is become a WFE in a MOSS Farm). so please define install order for us…

    Thanks in advance..

  9. mit
    March 30th, 2009 at 13:00 | #9

    @Scott
    I think JT try to say AS means.. Application Server.. if i am not wrong..

  10. Reza Alirezaei
    March 30th, 2009 at 13:07 | #10

    @mit

    Add-in on ALL SharePoint WFEs
    SharePoint bits and join to existing farm on RS Server

    Order doesn’t matter , but integrations won;t happen until you are completely done doing what you need to do on both products.

  11. Reza Alirezaei
    March 30th, 2009 at 13:09 | #11

    @mit

    No he means Analysis Services Server, othewise why would he use SQL08 to prefix it? 🙂

  12. mit
    March 30th, 2009 at 14:23 | #12

    Reza,

    I am installed in following order and i can see my site is attached with the MOSS farm but when i try to install on WFE01 sever then we are getting error as well as our WFE server is down with Reporting services error(web config error).

    Installed the Reportign add-ins to the Application server
    Try to Install the WFE server 1 but not installed beause error so we try to install following way but no success.

    Run the .MSI from a command line with the following command:
    a. msiexec /I SharePointRS.msi SKIPCA=1
    2. Run the rscustomaction.exe file manually from a command line by running this
    command:
    a. %TEMP%\rscustomaction.exe -I

    So any input ….our all WFE servers are down. so we went to to each WFE webconfig file and remvoe the lines from that file.

    Following line was removed from the webconfig file and our WFE server site is working fine but RS services piece is removed from the server.

    “RSProxyHandler” lines from safecontrols section

    Please give us your valubale advise…

  13. Reza Alirezaei
    March 30th, 2009 at 14:37 | #13

    @mit

    Sorry, I have no idea what is going wrong there. BTW,the way you are describing your issue is absolutely unclear 😉

  14. mit
    March 30th, 2009 at 15:42 | #14

    My Bad Reza.. i was so exisited that your reply back with in 15 min.. so i thought you were online but hopefully this will help you out.

    1. Installed the SQL server 2008 – standalone reporting services (RS & Catalog DB)
    2. Configured the Reporting services with SharePoint Integrated mode
    3. Reporting server site is accessible – we got error that SharePoint object was not found (http://farm4.static.flickr.com/3193/2947757812_0581b83be4_o.png)
    4. Installed the web front end server on the Report server
    5. Reporting server attached with SharePoint MOSS 2007 farm.
    6. Verify that Reporting Server site is accessible.
    7. Verify that Reporting server attached to the MOSS 2007 Farm. – All sites are working
    8. Installed the Reporting services add-in (64 bit) to the Application server – Installed was successfully
    9. Verify that all sites are accessible.
    10. We try to install the Reporting services add-ins to the Web Front End Server 1 but we got the error so, we try to installed the Reporting add-ins with the following command line

    Run the .MSI from a command line with the following command:
    a. msiexec /I SharePointRS.msi SKIPCA=1
    2. Run the rscustomaction.exe file manually from a command line by running this
    command:
    a. %TEMP%\rscustomaction.exe –I

    We have tried several times to get the Reporting Services Add-In to install. It seems as if everything is going along well except when the installation gets to “Removing Backup” files it hangs and then completely uninstalls the product. Moreover, the event log simply notes “installation failed”.

    11. No success – we are not able to install the Reporting Services add-ins to the any Web Front End Servers.
    12. When we try to access to SharePoint sites then some sites were down. With the following error:

    RSProxyHandler error. – Web services error
    13. We edit the web-config file and we removed the “RSProxyHandler’ lines (2) and we found out that our site is back and working fine.

    But not the reporting servieces part…

  15. Reza Alirezaei
    March 30th, 2009 at 16:52 | #15

    It’s okay man 😉
    Like I said, without having a proper description of the error message I really can’t help you. By looking at “installation failed” , I can’t say what’s going wrong. There might be many things that needs further analysis on-site. I hope you understand.

    I’d recommend that you to open a ticket with PSS if you can, rather than looking for the answer in blogs and forums for issues of this kind , issues without having a proper description of the error.

    However, I can assure you that there is nothing wrong with the topology you are trying to implement the integration. I have deployed SharePoint and SSRS on the similar topology many times.

    Good luck and please let others know if you find an answer (if you care) . Thanks

  16. mit
    March 31st, 2009 at 12:30 | #16

    Thanks Reza,

    Finally, As per team decission, we log a case with Microsoft and we will give all infromation to them and let see what they are coming with the solution and i will post that solutions to help others…

    Once again thanks for your help and hopefully you will keep posting many blogs like that so other can get help/learn from your blogs..

    Mit

  17. Reza Alirezaei
    March 31st, 2009 at 12:42 | #17

    @mit

    Sure , no problem man! Hope to see you back here with some good news and some updates for everyone to learn.

  18. April 7th, 2009 at 10:55 | #18

    Thanks, this was a big help in trying to understand the issues we were facing getting SSRS 2008 installed properly with WSS 3.0!

  19. mit
    June 16th, 2009 at 10:13 | #19

    Reza,

    Finally we workout and now SSRS 2008 integrated SharePoint environment works ok.

    The culprit was “Application Job(Web Config edit)” which is run evrytime when we tried to installed the RS Add-ins on the any servers in the SharePoint Farm. We need to delete that job everytime when we install the RS add-ins on the servers.

    Try this solutions if you have same problem whihc i am running into for multi server environment.

    Reza have to done any integration with MS PPS server and SharePoint server. I am planning to deploy this server for SSAS services.

    Thanks for valuable info…

  20. Wei
    December 22nd, 2009 at 09:43 | #20

    Hi, Reza

    I am trying to integrate SSRS 2008 and WSS 3.0 in a environment, where one server runs WSS 3.0 and the other runs SSRS 2008. I have installed SSRS 2008 add in on the WSS server and I could see the ‘reporting services’ in application management.

    When I was in ‘Manage integration settings’, I input a valid report server url in the field. I call it a valid report server url because I could open it with IE and see the root node of the report server folder hierarchy. But I only got a 401 error when I clicked OK. It said, Server was unable to process request. —> The request failed with HTTP status 401: Unauthorized.

    I don’t know what is wrong and how to fix this. Could you or someone else help me? Thx a lot

  21. Linda
    June 2nd, 2010 at 08:03 | #21

    Hi — So glad I found this site, the Q&A is immenseful helpful. I am working a project now that has SP07 and SSRS 2008. We have successfully integrated both on a dev and test server. We were trying to generate nifty graphics using SSRS and found it woefully lacking in any pizzaz, as least that’s what the developer said and this was the best he could produce a graphical representation of data — oops, looks like I cannot insert it into this comment.

    Well, it looks about as good as my elementary school child’s art assignment. Are pricey 3rd party tools like Dundas or ComponentOne our only options?

  22. Reza Alirezaei
    June 2nd, 2010 at 09:21 | #22

    @Linda
    You can insert anything you want to your comments.

    SSRS 2008 gives you the reporting platform. for the nifty graphics , yes the 3rd parties are there to fill the gap.

  23. James
    October 21st, 2011 at 18:08 | #23

    Hello;

    I apologize for this petty request but could you move the “tweets” tab that displays on the left hand side of the page? Perhaps move it to the right where it does not interfere with the reading of the nicely presented blog?

    Thank you,
    James