Integrating SharePoint 2016 with SSRS Native Mode

Why on earth would I want to write an article on this topic? Surely, if I am using SharePoint and SQL Server Reporting Services, I should be running in in SharePoint Integrated mode, right? That’s certainly the message that I have been delivering for quite some time. However, the game has changed significantly with SSRS 2016. Last October, Microsoft outlined their reporting roadmap, and that roadmap included a significant investment in SSRS. The roadmap was very clear as to Microsoft’s intentions.

“Reporting Services is our on-premises solution for BI report delivery”

Reporting Services is the solution, not SharePoint with Reporting Services, or PerformancePoint with Reporting Services, just Reporting Services. For several years now, the path to any new features in SSRS led through SharePoint Integrated mode. Features like Power View reports were only made available in Integrated mode as an example. While that was great for those invested in SharePoint, it presented an adoption issue for those that were not. This issue has prompted Microsoft to remove the SharePoint dependency, while still providing solid integration. In short, the goal for SSRS is to run with SharePoint, not on it.

In my opinion, this is all to the good. By making SharePoint integration pluggable, the product team can focus on one codebase instead of two, and spent more energy on features. This does however have some negative impact on administrators that will need to again manage two security models, but in the ideal world, it should be transparent to end users.

The immediate impact of this refocusing is that Native mode now receives new feature priority. If we look at the current state of SSRS 2016 (RC0 at the time of this writing), only a few of the major new features will be available in SharePoint integrated mode.

SSRS New Features

Native SharePoint Integrated
HTML 5 Based Rendering Engine

Customizable Parameters Pane

New UI for Report Builder

New Web Portal

Mobile Reports

New Chart Types

PDF replaces ActiveX for printing

PowerPoint rendering and export

KPIs

Pin to Power BI Dashboard

Render Power BI Desktop files

HTML 5 Based Rendering Engine

New UI for Report Builder

New Chart Types

PDF replaces ActiveX for printing

PowerPoint rendering and export

This new disparity is likely to leave some in the SharePoint world feeling left behind. The reality is that although this may seem like the case in the short term, Microsoft stated that “We will continue to support embedding of BI content into SharePoint”. For the record, that statement is open ended enough to include both SSRS and Power BI. The improvements to integrated mode in SharePoint 2016 are a testament to this support. It would have been just as easy to leave Integration mode in its previous state (like PerformancePoint). My view is that ultimately Native mode reports will work with SharePoint in much the same manner that current Integrated mode ones do. In fact, it’s possible to do some of this today – to embed Native mode SSRS Reports into SharePoint. That’s what the remainder of this article describes.

The ability to embed Native mode SSRS reports has actually been available since SharePoint 2003. It fell by the wayside after Integrated mode was introduced in SQL Server 2008 R2, but it has continued to be there. What is needed is a Native mode SSRS Server, and the Native mode SharePoint web parts.

Installing and Configuring SSRS Native Mode

Native mode SSRS is installed from the SQL Server media. It should be installed on a NON SharePoint server. Run the SQL Server installer, and eventually you will be taken to the feature selection screen.

Native mode SSRS installs as a SQL Server instance, and it is the only option necessary to install. The SharePoint add-in is only used for Integrated mode.

Once installed, it is necessary to run the Reporting Services configuration tool. The first step in configuration is to set up the web service URL.

Once the desired options are set, click Apply and the SSRS web service will be set up. Next, click on the Database node to configure the SSRS database. If the SQL Server database is installed on the same machine, you can use it, but you can use any SQL Server at your disposal. The only restriction is that the database engine must be at least the same edition level as SSRS (ie Standard vs Enterprise).

To create a new SSRS database, click the “Change Database” button and provide the database parameters.

Two databases will actually be created, one of them a Temp database. I recommend using the word “Native” or some other identifier in the name, particularly when both Native and SharePoint Integrated mode servers may be used. Complete the database creation process, and move to the Report Manager URL node.

Click Apply to create the SSRS Report Manager. The initial URL will always be based on the machine name, but once complete, you can click on the Advanced tab to add additional URLs. This is how you can add a Fully Qualified Domain Name (FQDN) to your SSRS server, which is strongly recommended if you will be integrating SSRS with Power BI. Power BI users will need to connect directly to the SSRS server to view SSRS reports, and this requires an FQDN.

There are other steps to be performed at this point, including Power BI integration and exporting the Encryption keys, but this is all that is necessary for basic configuration. You should now be able to navigate to your Report Manager URL and create reports. The next step is therefore to integrate them with Sharepoint.

Integrating with SharePoint

Native mode ships with a pair of web parts that allow SSRS web parts to be embedded into a SharePoint page. The web parts are embedded in an installable .cab file that can be found in the folder “C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Reporting Services\SharePoint” where C: is the installation drive, and “130” is the major installation version for SQL Server (130, or 13.0 corresponds to SQL Server 2016). The name of the file is RSWebParts.cab. Copy this file to a SharePoint server in the farm, and from there, it can be installed from either PowerShell, or the gool ol’ STSADM command. With PowerShell, the command is:

Install-SPWebPartPack -LiteralPath “D:\Software\RSWebParts.cab” -GlobalInstall

Where “D:\Software” is the folder that the file was copied to. The corresponding STSADM command is:

STSADM.EXE -o addwppack -filename “D:\Software\RSWebParts.cab” -globalinstall

Unfortunately, I and many others have found that the version of the .CAB file distributed with SQL Server 2012 and above are incompatible with SharePoint 2013 and 2016 – the web parts fail to deploy. The good news (and the bad) is that the web parts are unchanged from SQL Server 2008 R2, and that version of the .CAB file will work with modern SharePoint. Of course, not everyone has a SQL Server 2008 R2 server lying around, so if you happen to need the file, I include it here:

RSWebParts.cab from SQL Server 2008 R2

Using the Web Parts

Once deployed, the two web parts, Report Viewer and Report Browser will appear under the miscellaneous section when a web part is inserted into a page. Report browser allows the browsing of reports on a server, and Report Viewer renders them. By connecting the two, it is possible to provide a highly interactive navigation of the report server right in a SharePoint server. However, editing the Report Viewer web part reveals that it is lacking some very fundamental capabilities.

Native Mode Web Part

Integrated Mode Web Part

The Native mode web part is missing all of the view control features that are available to the Integrated mode part, which means that when it comes to Native mode reports, you get what you get. However, more concerning is the fact that it is also missing parameters. There is no way to configure parameters for, or pass parameter values to Native mode reports embedded on a page.

Add to this limitation the fact that these web parts are approximately 10 years old – they were designed for SharePoint 2007. They are able to render the new chart types, but not through the new HTML renderer. These limitations make it very difficult to recommend their use, except in a few very specific scenarios.

Recommendations

So what is a Report driven SharePoint administrator to do? All of the cool new features are showing up in Native mode, but except in certain circumstances, there no really good way to embed those reports in Sharepoint pages. It seems a difficult question, but the reality is that these choices are not necessarily mutually exclusive. SSRS Integrated mode is getting many of the modernization improvements and continues to be a totally viable platform moving forward. If you want or need to take advantage of the new SSRS features like mobile reports, parameter pane customization, or Power BI integration, you can stand up a separate SSRS Native mode server, and even integrate it with SharePoint using the older web parts.

Taking this dual approach means that you’ll be well positioned to gradually move assets from Integrated mode to Native mode as the embedding story and capabilities improve.

17 thoughts on “Integrating SharePoint 2016 with SSRS Native Mode

  1. Pingback: SSRS 2016 – Integrated or Native Mode – Which one do I use? | The White Pages

  2. Carl Dalton

    been looking for the 2008 R2 RSWebParts.cab file for ages… the link to the download seems broken on the blog page. Any chance of an updated link????

  3. Thomas Bonde Ejby (@powerpappi)

    Hi John, thx for the article, do you know of someone that, has fixed the WEB part or even made a new native mode WEB part for SharePoint, that i could purchase. I really would like this to work. Or is there not a way to get the RSWEB part installed

  4. jpw@unlimitedviz.com Post author

    I’ve heard of nothing new yet, although we have gotten a glimpse of a Power BI web part in SharePoint (see my latest post) . I expect that it’ll look something like that. For now, we’ll need to get along with both modes I think.

  5. Rio

    Hi John,
    This is quite painful, my organization just launched SharePoint 2016 plus SQL Server 2016. We all thought all of the new features will be available on SharePoint once the final release is out there. We deployed in May before the final release, and now we are finding out that you cannot use Mobile Reports (biggest use case) on SharePoint.

    Do you know if there is ANY workaround for publishing Mobile reports on SharePoint 2016? You mentioned using old web parts, but this won’t work with mobile reports (which is implied I see)

  6. John White Post author

    Hey Rio – I hear you. No – you can’t use mobile reports with SSRS Integrated Mode 2016, but there’s nothing stopping you from configuring a native mode server along side of it, and using it for mobile reports. That’s what I would recommend in 2016. As the integration improves between Native Mode and SharePoint, you’ll be well positioned, and can move your reporting assets currently deployed to SharePoint over to Native mode as appropriate (or not). That’s likely the best approach for you.

  7. Rio

    Thanks so much for the quick reply, it means A LOT to me!

    Would you happen to know how difficult it is to open up Report Server to the public (internet facing)? Our SharePoint is used (almost exclusively) with external users – it’s an internet facing SharePoint solution. So if I deploy SSRS Natively, I’ll need to somehow display it to public too. I know it’s a broad question, but any tips/guidance is much appreciated.

  8. John White Post author

    No worries. The last time that I checked, SSRS Integrated Mode didn’t support anonymous access at all in SharePoint. This may have changed – are you doing this currently? In any event, your question is about Native mode, which I believe has the same restriction. As a workaround, however, you could set up a single “public” user that your external users could use when navigating to your report server. This arrangement might be better anyway, because If the restriction still exists, you wouldn’t be able to do this at all with SharePoint integrated mode, where with this setup, you could still use SharePoint anonymously, and accesses to SSRS Native will prompt for authentication with this proxy account.

  9. JC

    Any known plan or roadmap for SharePoint Online integration? New clients should know configuring and maintaining a SharePoint environment is not trivial work. Then you got to factor in security, high availability, administration, patching, clean-up, etc…
    I would imagine the obstacles with SPO integration have to do with lack of support for SAML assertion in SSRS and that all the SSRS web parts are server side. I just can’t imagine MS is not thinking about this.

  10. tom

    Is Native mode a superset of Integrated mode??
    That is, if I install SP etc. to use only Native mode, will it do everything that Integrated mode does??
    Thank you, Tom

  11. Mike Pal

    Can I keep storing reports in my SharePoint Libraries and have them opened in SSRS in Native mode? or can I only do this on Integrated mode ?

  12. Thomas Hab.

    hi john, thanks for this great article!
    I want to visualize SSRS Reports in SharePoint with native mode as described above. We have SharePoint, we have SQL licensed. I want to run SQLServerReportingServices.exe not on a SQL server, I want to run it on a separate Windows 2016 server (the needed databases for Reporting services can be stored on existing SQL) because I don’t want to install a web interface on the SQL server.
    So do I have to pay any additional licenses except for SharePoint and existing SQL?

  13. Azhar iqbal

    I am more interested to embed PBIX files from PBI Report Server into SharePoint On-Premise. According to you post “SharePoint and the New SSRS/PBIRS Native Mode Report Viewer Web Part” We can embed it through Script Editor Web Part. is Microsoft going to make a special Web part for Power BI reports?
    Please share me any link or way (Rest APIs, Embeding) we can embed PBIX files into SharePoint on-Premise.

  14. Michael

    John, I got the wepart installed, solution activated, I am able to add the webpart to the page, but I get an error where the report should be with a generic message “For more information about this error navigate to the report server on the local server machine, or enable remote errors”. I know that Kerberos delegation works on the web application, and the “Claims to Windows Token Service” I running just fine. How can I troubleshoot this error. Also, I can use a browser and go the report server, and run the report just fine, no issues there. The report sever native mode was setup originally with http url, then I added https, FQDN.
    Please help.

  15. Stephen

    We are using the PageViewer web part to integrate PBIX, SSRS, and Mobile Reports to our SharePoint 2016 Environment.

    I am working out a way to develop a web part that does this to make using the rc and rs commands easier. Essentially you append ?rc:Embed=True (that may be rs, I forget)….It will bring up the report or PowerBI report without the menu and PowerBI delegation bars stuff.

    You can get fancy and hide information like the toolbars. Passing data is a bit difficult right now but I will find a solution.

  16. Loic

    Hi,

    Great article !
    Can you refresh the link of the RSWebParts.cab from SQL Server 2008 R2 ? It doesn’t work anymore.
    THanks in advance.

    Loic

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.